Dashboards & Visualizations

Iterating thru json array

eckythump
Engager

Hi sorry if this has been asked before, spent lot of time researching but can't find quite the answer.

I have this json logged below, I want to do analysis on the order lines, so need a search to return two lines from my json example.

Order ReferenceDescriptionValue
XXXPAUL35,700
XXXIS GREAT42,000

I've tried a million things, can't get it to work 😞

Thanks in advance

Paul (who isn't actually that great!)

THE JSON

{
  "orderReference": "xxx",
  "orderLine": [
  { 
     "orderLineUserItemDescription": "PAUL",  
     "orderLineUnitPrice": "35700.0",
   }, 
   { 
     "orderLineUserItemDescription": "IS GREAT",
     "orderLineUnitPrice": "42000.0",
   } ]
}

Labels (1)
0 Karma
1 Solution

eckythump
Engager

You  are a genius!  Thank you so much for answering so quickly, works amazingly and learned a lot in this post.

Drinks on me if you ever pass thru Atlanta!

Paul

View solution in original post

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Alternatively:

 

| makeresults | eval _raw="{
  \"orderReference\": \"xxx\",
  \"orderLine\": [
  { 
     \"orderLineUserItemDescription\": \"PAUL\",  
     \"orderLineUnitPrice\": \"35700.0\"
   }, 
   { 
     \"orderLineUserItemDescription\": \"IS GREAT\",
     \"orderLineUnitPrice\": \"42000.0\"
   }
   ]
}" 


| spath orderLine{} output=orderLine
| mvexpand orderLine
| spath input=orderLine
| spath orderReference
| rename orderReference as "Order Reference", orderLineUnitPrice as Value, orderLineUserItemDescription as Description
| table "Order Reference" Description Value

 

0 Karma

richgalloway
SplunkTrust
SplunkTrust

One doesn't iterate through the array.  The array is treated as a single entity so we have to split it into separate events.  Something like this:

| makeresults | eval _raw="{
  \"orderReference\": \"xxx\",
  \"orderLine\": [
  { 
     \"orderLineUserItemDescription\": \"PAUL\",  
     \"orderLineUnitPrice\": \"35700.0\"
   }, 
   { 
     \"orderLineUserItemDescription\": \"IS GREAT\",
     \"orderLineUnitPrice\": \"42000.0\"
   }
   ]
}" | spath
```Everything above just sets up test data```
```Rename the fields for convenience```
| rename orderLine{}.orderLineUserItemDescription as Description, orderLine{}.orderLineUnitPrice as Value
```Match up Description and Value pairs```
| eval DV=mvzip(Description,Value, ";")
```Split the Description/Value pairs into separate events```
| mvexpand DV
```Break the Description/Value pairs apart```
| eval DV=split(DV,";")
| eval Description = mvindex(DV,0), Value=mvindex(DV, 1)
```Display the results```
| table orderReference Description Value
---
If this reply helps you, Karma would be appreciated.

eckythump
Engager

You  are a genius!  Thank you so much for answering so quickly, works amazingly and learned a lot in this post.

Drinks on me if you ever pass thru Atlanta!

Paul

0 Karma
Get Updates on the Splunk Community!

Detecting Remote Code Executions With the Splunk Threat Research Team

WATCH NOWRemote code execution (RCE) vulnerabilities pose a significant risk to organizations. If exploited, ...

Enter the Splunk Community Dashboard Challenge for Your Chance to Win!

The Splunk Community Dashboard Challenge is underway! This is your chance to showcase your skills in creating ...

.conf24 | Session Scheduler is Live!!

.conf24 is happening June 11 - 14 in Las Vegas, and we are thrilled to announce that the conference catalog ...