Splunk Search

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

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

1 Solution

mrobichaud_splu
Splunk Employee
Splunk Employee

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

Lowell
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

mrobichaud_splu
Splunk Employee
Splunk Employee

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"

mrobichaud_splu
Splunk Employee
Splunk Employee

Updated with sundareshr's spath command instead of rex.

0 Karma

suarezry
Builder

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

0 Karma

mrobichaud_splu
Splunk Employee
Splunk Employee

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

Sweet dreams.

0 Karma

sundareshr
Legend

How about

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

mrobichaud_splu
Splunk Employee
Splunk Employee

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
Get Updates on the Splunk Community!

Splunk Observability Cloud’s AI Assistant in Action Series: Analyzing and ...

This is the second post in our Splunk Observability Cloud’s AI Assistant in Action series, in which we look at ...

Elevate Your Organization with Splunk’s Next Platform Evolution

 Thursday, July 10, 2025  |  11AM PDT / 2PM EDT Whether you're managing complex deployments or looking to ...

Splunk Answers Content Calendar, June Edition

Get ready for this week’s post dedicated to Splunk Dashboards! We're celebrating the power of community by ...