Splunk Search

How to filter search by value from a json list?

mottig
Path Finder

Hi 

Consider this event structure :

 

 

{"result" : {"dogs" : [{"name" : "dog-a", "food":["pizza", "burger"] },
{"name" : "dog-b", "food":["pasta"] }] }}

 

 

Now want to filter the dogs by name and present them relevant food.

When I try this search(with the relevant index):

 

 

result.dogs{}.name = dog_a| table result.dogs{}.food{}

 

 

I Am getting this result:

pizza

burger

pasta 

 

I Am expecting to get only dog-a foods(pizza and burger)  

Labels (2)
0 Karma
1 Solution

yuanliu
SplunkTrust
SplunkTrust

You want to access structured result.dogs{}, instead of operating on result.dogs{}.name directly, because you want to apply mvexpand to the structure.  Internal structure of JSON can be accessed with path option in spath.  After mvexpand, you then extract inner fields using spath. (Yes, again.)  Try this

 

| spath path=result.dogs{}
| mvexpand result.dogs{}
| spath input=result.dogs{}
| where name == "dog-a"

 

 Output from your sample data is

food{}
nameresult.dogs{}
pizza
burger
dog-a{"name" : "dog-a", "food":["pizza", "burger"] }

View solution in original post

Tags (2)

yuanliu
SplunkTrust
SplunkTrust

You want to access structured result.dogs{}, instead of operating on result.dogs{}.name directly, because you want to apply mvexpand to the structure.  Internal structure of JSON can be accessed with path option in spath.  After mvexpand, you then extract inner fields using spath. (Yes, again.)  Try this

 

| spath path=result.dogs{}
| mvexpand result.dogs{}
| spath input=result.dogs{}
| where name == "dog-a"

 

 Output from your sample data is

food{}
nameresult.dogs{}
pizza
burger
dog-a{"name" : "dog-a", "food":["pizza", "burger"] }
Tags (2)

mottig
Path Finder

Hi

Thank you for your answer.

It worked like a magic 

0 Karma

mottig
Path Finder

Hi thank you for your answer.

When I Am running the search I Am getting a warning that -  Field 'new_dogs' does not exist in the data.

0 Karma

andrew_nelson
Communicator

The quick and dirty method going on the exact event format in your query is to run regex and create new lines per dog.

 

| rex field=_raw "dogs\" : \[(?<dogs_raw>.+)\] " 
| eval new_dogs=split(replace(dogs_raw, "},{", "}##{"), "##")
| mvexpand new_dogs
| spath input=new_dogs
| search name="dog-a" 
| table food{}

 

Lines 1&2 extracts everything from "dogs" and splits them out into a multivalue field called new_dogs.
Lines 3&4 expands them out to one row per dog and extracts the fields.

If this is a datasource you'll be using a lot and other users will be looking at it, it might be worth tweaking your input to split each dog into its own event which would make lines 1-4 redundant. 

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 ...