Getting Data In
Highlighted

Dynamically select json object in json array

I'm looking to extract a JSON object from a JSON array using a dynamic index number ( based on data in another part of the JSON )

Simplified JSON example........

{
"header":
{"index_number": 2},
"rows": [
{"carrot": 108, "apple": 29},
{"carrot": 12, "apple": 44},
{"carrot": 54, "apple": 23},
{"carrot": 67, "apple":9}
]
}

In this example the value from the field indexnumber is used to select the correct JSON object from the JSON array. The indexnumber is not a static number so the JSON object returned from the JSON array will change. Using the spath command I can statically extract a JSON object but not do it dynamically using the value in indexnumber as path=rows{indexnumbertouse} does not work.

Example SPL.......

| makeresults
| eval json_field = "{\"header\": {\"index_number\": 1}, \"rows\": [{\"carrot\": 108, \"apple\": 29}, {\"carrot\": 12, \"apple\": 44}, {\"carrot\": 54, \"apple\": 23}, {\"carrot\": 67, \"apple\":9}]}" 
| spath input=json_field output=index_number_to_use path=header.index_number
| spath input=json_field output=row_found path=rows{1}

Any ideas on how this could be achieved?

0 Karma
Highlighted

Re: Dynamically select json object in json array

Ultra Champion
| makeresults 
| eval json_field = "{\"header\": {\"index_number\": 1}, \"rows\": [{\"carrot\": 108, \"apple\": 29}, {\"carrot\": 12, \"apple\": 44}, {\"carrot\": 54, \"apple\": 23}, {\"carrot\": 67, \"apple\":9}]}" 
| spath input=json_field path=header{}.index_number output=index_number 
| spath input=json_field path=rows{} output=rows 
| eval _raw=mvindex(rows,index_number - 1) 
| kv 
| table apple carrot

View solution in original post

Highlighted

Re: Dynamically select json object in json array

Thank you @to4kawa I was able to create a variation based on your answer that worked.

0 Karma
Highlighted

Re: Dynamically select json object in json array

Ultra Champion

you're welcome. happy splunking,too.

0 Karma
Speak Up for Splunk Careers!

We want to better understand the impact Splunk experience and expertise has has on individuals' careers, and help highlight the growing demand for Splunk skills.