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!

Dashboards: Hiding charts while search is being executed and other uses for tokens

There are a couple of features of SimpleXML / Classic dashboards that can be used to enhance the user ...

Splunk Observability Cloud's AI Assistant in Action Series: Explaining Metrics and ...

This is the fourth post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how ...

Brains, Bytes, and Boston: Learn from the Best at .conf25

When you think of Boston, you might picture colonial charm, world-class universities, or even the crack of a ...