topic How to filter search by value from a json list? in Splunk Search

How to filter search by value from a json list?

mottig — Mon, 19 Sep 2022 15:39:15 GMT

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)

Re: Filter search by value from a json list

andrew_nelson — Mon, 19 Sep 2022 10:44:55 GMT

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.

Re: Filter search by value from a json list

mottig — Mon, 19 Sep 2022 11:22:20 GMT

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.

Re: How to filter search by value from a json list?

yuanliu — Tue, 20 Sep 2022 05:19:27 GMT

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"] }

Re: How to filter search by value from a json list?

mottig — Tue, 20 Sep 2022 06:56:41 GMT

Thank you for your answer.

It worked like a magic