Greetings,
I have this sample json data indexed in Splunk:
{"billId":3598,"bodyLines":
[{"bodyLineId":24246,"value":116281.200000,"caption":"Unadjusted Consumption"},{"bodyLineId":24247,"value":120653.370000,"caption":"Adjusted Consumption"}]}
I want to extract bodyLines{}.value
(ie. 120653.370000
) where bodyLines{}.caption=="Adjusted Consumption"
. Can someone please provide the right (eval?) syntax to extract the value?
Here's a solution, assuming there is only one billId per event
| spath output=value bodyLines{}.value
| spath output=caption bodyLines{}.caption
| eval zipped=mvzip(value,caption)
| mvexpand zipped
You'll now have a separate event for each value. You can read caption and value as a pair:
| makemv delim="," zipped
| eval adjustedConsumption=if(mvindex(zipped, 1) = "Adjusted Consumption", mvindex(zipped, 0), '')
Or create new fields for them and filter out the other results:
| makemv delim="," zipped
| eval caption=mvindex(zipped, 1)
| eval value=mvindex(zipped, 0)
| search caption = "Adjusted Consumption"
I realize this question is old, but apparently lots of people reference it. Handling JSON arrays in Splunk can be difficult and require many SPL commands. And in a simple case like this, it's not too bad, but if you have to unwrap a few JSON arrays simultaneously the mvzip()
and mvexpand
approach become super tedious.
If you deal with complex JSON on a regular basis, be sure to check out the JMESPath app for Splunk. It makes this type of extraction super easy to do in a single command. Take a look:
| jmespath output=adjustConsumption_value "bodyLines[?caption=='Adjusted Consumption'].value"
Here's a run-anywhere example for those following along at home:
| makeresults | eval _raw="{\"billId\":3598,\"bodyLines\":[{\"bodyLineId\":24246,\"value\":116281.2,\"caption\":\"Unadjusted Consumption\"},{\"bodyLineId\":24247,\"value\":120653.37,\"caption\":\"Adjusted Consumption\"}]}"
| jmespath output=adjustConsumption_value "bodyLines[?caption=='Adjusted Consumption'].value"
Full disclosure. I'm currently the maintainer of the JMESPath for Splunk. I took over because the original author ran out of time, and because I think this app is awesome!
Here's a solution, assuming there is only one billId per event
| spath output=value bodyLines{}.value
| spath output=caption bodyLines{}.caption
| eval zipped=mvzip(value,caption)
| mvexpand zipped
You'll now have a separate event for each value. You can read caption and value as a pair:
| makemv delim="," zipped
| eval adjustedConsumption=if(mvindex(zipped, 1) = "Adjusted Consumption", mvindex(zipped, 0), '')
Or create new fields for them and filter out the other results:
| makemv delim="," zipped
| eval caption=mvindex(zipped, 1)
| eval value=mvindex(zipped, 0)
| search caption = "Adjusted Consumption"
Updated with sundareshr's spath command instead of rex.
It works! ...but I'm gonna have nightmares about this search...
Glad it worked! Working with multivalue fields is often unintuitive.
Sweet dreams.
How about
.. | spath output=blid bodyLines{}.value | spath output=blcaption bodyLines{}.caption | search blcaption="Adjusted*"
That's a better way to create the fields than what I did, but you still need to use mvzip() and mvexpand to get the correct value.