topic Re: parsing a JSON list in Splunk Search

parsing a JSON list

rberman — Tue, 14 Dec 2021 02:16:12 GMT

Hi, I have a field called "catgories" whose value is in the format of a JSON array. The array is a list of one or more category paths. The paths are in the form of a comma separated list of one or more (category_name:category_id) pairs.

Three example events have the following category data:

"categories":"[{"categ_name_a":"categ_id_a","categ_name_b":"categ_id_b","categ_name_c":"categ_id_c"},{"categ_name_m":"categ_id_m","categ_name_n":"categ_id_n"},{"categ_name_z":"categ_id_z"}]"

"categories":"[{"categ_name_d":"categ_id_d","categ_name_e":"categ_id_e"}]"

"categories":"[{"categ_name_f":"categ_id_f"}]"

For each event, I am trying to extract the list of " >> " separated category_ids in each path into a multivalued field. So using the examples above I want to get a list of category paths:

event #	category_paths
1	"categ_id_a >> categ_id_b >> categ_id_c" "categ_id_m >> categ_id_n" "categ_id_z"
2	"categ_id_d >> categ_id_e"
3	"categ_id_f"

I have no way of knowing what the category names or ids will be or how many (category_name:category_id) pairs there will be in each category path. I also won't know how many category paths are in the categories JSON array.

I have tried a bunch of ways to get at the data (spath, json_extract, regex) but I am new to this type of nested extraction.

Can anyone help me?

Thanks!!!

Re: parsing a JSON list

ITWhisperer — Tue, 14 Dec 2021 09:49:16 GMT

| makeresults | eval categories="[{\"categ_name_a\":\"categ_id_a\",\"categ_name_b\":\"categ_id_b\",\"categ_name_c\":\"categ_id_c\"},{\"categ_name_m\":\"categ_id_m\",\"categ_name_n\":\"categ_id_n\"},{\"categ_name_z\":\"categ_id_z\"}]" | spath input=categories output=category_paths path={} | streamstats count as event | mvexpand category_paths | rex field=category_paths max_match=0 "\":\"(?<segment>[^\"]+)\"" | eval path=mvjoin(segment," >> ") | fields - segment category_paths | stats list(path) as category_paths by event

Re: parsing a JSON list

rberman — Tue, 14 Dec 2021 14:07:42 GMT

Thank you so much!!! I really appreciate the help!

Re: parsing a JSON list

rberman — Tue, 14 Dec 2021 20:13:20 GMT

@ITWhisperer is there a way to use rex to get the multivalued path data straight out of the _raw string? I wanted to check if there were other approaches besides using spath and JSON extraction.

Re: parsing a JSON list

ITWhisperer — Tue, 14 Dec 2021 22:13:46 GMT

You could try this way without spath and mvexpand

| makeresults | eval categories="[{\"categ_name_a\":\"categ_id_a\",\"categ_name_b\":\"categ_id_b\",\"categ_name_c\":\"categ_id_c\"},{\"categ_name_m\":\"categ_id_m\",\"categ_name_n\":\"categ_id_n\"},{\"categ_name_z\":\"categ_id_z\"}]" | rex field=categories max_match=0 "(?<category_paths>\{[^\}]+\})" | rex field=category_paths mode=sed "s/}/,\"x\":\"|\"}/" | rex field=category_paths max_match=0 "\":\"(?<segment>[^\"]+)\"" | eval category_paths=mvjoin(segment," >> ") | fields - segment | rex field=category_paths mode=sed "s/ >> \| >> / /g" | rex field=category_paths mode=sed "s/ >> \|//g"