Splunk Search
Highlighted

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

Builder

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?

Highlighted

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

Legend

How about

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

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

Path Finder

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.

0 Karma
Highlighted

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

Path Finder

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"

View solution in original post

Highlighted

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

Builder

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

0 Karma
Highlighted

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

Path Finder

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

Sweet dreams.

0 Karma
Highlighted

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

Path Finder

Updated with sundareshr's spath command instead of rex.

0 Karma
Highlighted

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

Super Champion

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!

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.