Getting Data In

Dynamically select json object in json array

msivill_splunk
Splunk Employee
Splunk Employee

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 index_number is used to select the correct JSON object from the JSON array. The index_number 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 index_number as path=rows{index_number_to_use} 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
1 Solution

to4kawa
SplunkTrust
SplunkTrust
| 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

to4kawa
SplunkTrust
SplunkTrust
| 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

msivill_splunk
Splunk Employee
Splunk Employee

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

0 Karma

to4kawa
SplunkTrust
SplunkTrust

you're welcome. happy splunking,too.

0 Karma
.conf21 Now Fully Virtual!
Register for FREE Today!

We've made .conf21 totally virtual and totally FREE! Our completely online experience will run from 10/19 through 10/20 with some additional events, too!