Splunk Search

JSON nested field extraction

wilcomply13
Explorer

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.

Labels (1)
0 Karma

johnhua
Builder

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=":"

 

yuanliu
SplunkTrust
SplunkTrust

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.

Tags (1)
0 Karma