topic Iterating thru json array in Dashboards & Visualizations

Iterating thru json array

eckythump — Tue, 14 Dec 2021 14:00:50 GMT

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",
} ]
}

Re: Iterating thru json array

richgalloway — Tue, 14 Dec 2021 14:06:56 GMT

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

Re: Iterating thru json array

ITWhisperer — Tue, 14 Dec 2021 14:59:44 GMT

Alternatively:

Re: Iterating thru json array

eckythump — Tue, 14 Dec 2021 18:28:44 GMT

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