topic How to extract a value from a JSON multivalue field based on a value from another multivalue field? in Splunk Search

How to extract a value from a JSON multivalue field based on a value from another multivalue field?

suarezry — Wed, 18 Nov 2015 15:52:00 GMT

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?

Re: How to extract a value from a JSON multivalue field based on a value from another multivalue field?

sundareshr — Wed, 18 Nov 2015 16:51:59 GMT

How about

    .. | spath output=blid bodyLines{}.value | spath output=blcaption bodyLines{}.caption | search blcaption="Adjusted*"

Re: How to extract a value from a JSON multivalue field based on a value from another multivalue field?

mrobichaud_splu — Wed, 18 Nov 2015 16:59:24 GMT

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"

Re: How to extract a value from a JSON multivalue field based on a value from another multivalue field?

suarezry — Wed, 18 Nov 2015 18:32:12 GMT

It works! ...but I'm gonna have nightmares about this search...

Re: How to extract a value from a JSON multivalue field based on a value from another multivalue field?

mrobichaud_splu — Wed, 18 Nov 2015 18:43:20 GMT

Glad it worked! Working with multivalue fields is often unintuitive.

Sweet dreams.

Re: How to extract a value from a JSON multivalue field based on a value from another multivalue field?

mrobichaud_splu — Wed, 18 Nov 2015 18:47:55 GMT

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.

Re: How to extract a value from a JSON multivalue field based on a value from another multivalue field?

mrobichaud_splu — Wed, 18 Nov 2015 18:50:33 GMT

Updated with sundareshr's spath command instead of rex.

Re: How to extract a value from a JSON multivalue field based on a value from another multivalue field?

Lowell — Sat, 17 Nov 2018 01:18:01 GMT

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!