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

to4kawa
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

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
Ultra Champion

you're welcome. happy splunking,too.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...