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!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

(view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...