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!

AppDynamics Summer Webinars

This summer, our mighty AppDynamics team is cooking up some delicious content on YouTube Live to satiate your ...

SOCin’ it to you at Splunk University

Splunk University is expanding its instructor-led learning portfolio with dedicated Security tracks at .conf25 ...

Credit Card Data Protection & PCI Compliance with Splunk Edge Processor

Organizations handling credit card transactions know that PCI DSS compliance is both critical and complex. The ...