Iterating thru json array

[eckythump]

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 Reference	Description	Value
XXX	PAUL	35,700
XXX	IS GREAT	42,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",
   } ]
}

[ITWhisperer]

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

[richgalloway]

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

[eckythump · Accepted solution]

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