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

johnhuang
Motivator

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
Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

ICYMI - Check out the latest releases of Splunk Edge Processor

Splunk is pleased to announce the latest enhancements to Splunk Edge Processor.  HEC Receiver authorization ...

Introducing the 2024 SplunkTrust!

Hello, Splunk Community! We are beyond thrilled to announce our newest group of SplunkTrust members!  The ...