topic JSON nested field extraction in Splunk Search

JSON nested field extraction

wilcomply13 — Mon, 24 Jan 2022 19:43:34 GMT

I have the following JSON:

{ "kind": "report", "id": { "time": "2021-12-24T15:45:01.331Z", }, "events": [ { "parameters": [ { "name": "field1", "boolValue": true }, { "name": "field2", "boolValue": true }, { "name": "field3", "value": "value3" }, { "name": "field4", "value": "value4" }, { "name": "field5", "boolValue": false }, { "name": "field6", "value": "value6" }, { "name": "field7", "value": "value7" }, { "name": "field8", "boolValue": false }, { "name": "field9", "value": "value9" }, { "name": "field10", "boolValue": false }, { "name": "field11", "boolValue": false } ] } ] }

I'd like to marry the key/value pairing of name/value and name/boolValue with their corresponding values i.e.

field1: true
field4: value4

I've attempted to use spath to extract values, but keep coming up short.

Re: JSON nested field extraction

johnhuang — Wed, 26 Jan 2022 07:55:18 GMT

Try this:

| rex field=_raw max_match=0 "\"name\"\:\s?\"?(?<field_name>[^\"^,]*)\"?\,\s+\"\w+\"\:\s?\"?(?<field_value>[^\"^\n]*)" | eval fields_list=mvzip(field_name, field_value, ":") | eval _raw=mvjoin(mvzip(field_name, field_value, ":"),"|") | extract pairdelim="=|",kvdelim=":"

Re: JSON nested field extraction

yuanliu — Thu, 27 Jan 2022 10:26:04 GMT

For structured data like JSON, I still prefer buildin SPL commands to custom regex so even badly formatted, but syntactically correct inputs do not ruin extraction. The same idea can be implemented with spath, for example:

| rex mode=sed "s/boolValue/value/g" ``` treat boolValue just like string value ``` | rex mode=sed "s/\"\"/\"()\"/g" ``` compensate for spath's lack of zero-length standin ``` | spath | rename events{}.parameters{}.* as field_* ``` johnhua's original code below ``` | eval _raw=mvjoin(mvzip(field_name, field_value, ":"),"|") | extract pairdelim="=|",kvdelim=":"

A caveat with spath is that it doesn't have an option to provide a standin for zero-length string values, so I have to force a non-zero standin.