How to filter search by value from a json list?

[mottig]

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)  

[yuanliu · Accepted solution]

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{}	name	result.dogs{}
pizza burger	dog-a	{"name" : "dog-a", "food":["pizza", "burger"] }

[mottig]

Hi

Thank you for your answer.

It worked like a magic 

[mottig]

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.

[andrew_nelson]

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.