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!

Stay Connected: Your Guide to May Tech Talks, Office Hours, and Webinars!

Take a look below to explore our upcoming Community Office Hours, Tech Talks, and Webinars this month. This ...

They're back! Join the SplunkTrust and MVP at .conf24

With our highly anticipated annual conference, .conf, comes the fez-wearers you can trust! The SplunkTrust, as ...

Enterprise Security Content Update (ESCU) | New Releases

Last month, the Splunk Threat Research Team had two releases of new security content via the Enterprise ...