parsing a JSON list

[rberman]

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.

| 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={}

Can anyone help me?

Thanks!!!

[ITWhisperer · Accepted solution]

| 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

[rberman]

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

[ITWhisperer]

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"

[rberman]

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