How to parse json with SPL?

[Strangertinz]

Hi Splunk Community,


I am looking to create a search that can help me extract a specific key/value pair within a nested json data.

The tricky part is that the nested json data is within an array of dictionaries with same keys. I want to extract a particular key/value within a dictionary only when a particular key is equal to a specific value. 

Sample JSON below..
------------------------------------------------------------------------------------------------------
{Key1: "Value1",
Key2: {
subKey2_1: "sub value1 for key2",
subKey2_2: [
{subkey2_2_key1: "value1_sub22",
subkey2_2_key2: "value2_sub22"
},
{subkey2_2_key1: "value1_sub22_2",
subkey2_2_key2: "value2_sub22_2 ---- value interested in "
},
{subkey2_2_key1: "value1_sub22_3",
subkey2_2_key2: "value2_sub22_3"
}
],
subKey2_3: "sub value3 for key2"
},
Key3: "Value3",
Key4: "Value4"
}


------------------------------------------------------------------------------------------------------



I am looking to extract the value for  -->"subkey2_2_key2"

when -- > subkey2_2_key1: "value1_sub22_2"

[Strangertinz]

Hi Danspav! Thanks the response certainly solved the hurdle I was on.. However, I am now stuck again on a different problem,

Is there a way for me to create a table output of the output of the json keys (possibly filter the keys to be a different name as well to represent their uniqueness).  Example below..

Json data:

{

     Key1: "Value1",

     Key2: {

          subKey2_1: "sub value1 for key2",

     Manifest: [

        {

            flight: "start",

            City: "Los Angeles"

            code: 7870

            Inventory: {

                snacks: 300

                status: "full"

                }

        },

       {

             flight: "end",

            City: "Las Vegas"

            code: 7470

            Inventory: {

                 snacks: 56

                 status: "near empty"

                 }

        }

        ],

subKey2_3: "sub value3 for key2"

},

    Key3: "Value3",

    Key4: "Value4"

}

I am looking to create a table with a single row of the fields and values below 

Field  = Value 

city_origin = "Los Angeles"

code_origin = 7870

inventory_snacks_origin=300

inventory_status_origin="full"

city_end = "Las Vegas"

code_end = 7470

inventory_snacks_end=56

inventory_status_end="near empty"

[danspav]

HI @Strangertinz,

Try this search:

| makeresults | eval _raw="{ \"Key1\": \"Value1\", \"Key2\": { \"subKey2_1\": \"sub value1 for key2\", \"Manifest\": [{ \"flight\": \"start\", \"City\": \"Los Angeles\", \"code\": 7870, \"Inventory\": { \"snacks\": 300, \"status\": \"full\" } }, { \"flight\": \"end\", \"City\": \"Las Vegas\", \"code\": 7470, \"Inventory\": { \"snacks\": 56, \"status\": \"near empty\" } } ], \"subKey2_3\": \"sub value3 for key2\" }, \"Key3\": \"Value3\", \"Key4\": \"Value4\"}"
| fromjson _raw
``` Above is just to format the data into a JSON event```

``` Get the JSON data from Key2```
| fromjson Key2
``` Split out the origin and destination fields```
| eval Origin = mvindex(Manifest,0)
| eval Destination = mvindex(Manifest,1)
| fields - Manifest, _raw, _time, Key2
``` Update the field names so we know which was Origin and which was Destination```
| rex mode=sed field=Origin "s/\"([^\"]+)\":/\"\\1_Origin\":/g"
| rex mode=sed field=Destination "s/\"([^\"]+)\":/\"\\1_Destination\":/g"


``` Extract all the fields from the JSON array ```
| fromjson Destination
| fromjson Origin
| fromjson Inventory_Destination
| fromjson Inventory_Origin
| fields - Inventory_End, Inventory_Origin

``` Table everything out on one row ```
| table City_Origin, code_Origin, snacks_Origin, status_Origin, City_Destination, code_Destination, snacks_Destination, status_Destination

I'm using rex mode=sed to update the JSON with more descriptive names for the duplicated fields.

The output is:

Is that what you were looking for?

Cheers
Daniel

[danspav · Accepted solution]

Hi @Strangertinz,

Depending on your exact data format, something like this may work:

| makeresults | eval _raw="{\"Key1\": \"Value1\", \"Key2\": { \"subKey2_1\": \"sub value1 for key2\", \"subKey2_2\": [ {\"subkey2_2_key1\": \"value1_sub22\", \"subkey2_2_key2\": \"value2_sub22\" }, {\"subkey2_2_key1\": \"value1_sub22_2\", \"subkey2_2_key2\": \"value2_sub22_2 ---- value interested in \" }, {\"subkey2_2_key1\": \"value1_sub22_3\", \"subkey2_2_key2\": \"value2_sub22_3\" } ], \"subKey2_3\": \"sub value3 for key2\" }, \"Key3\": \"Value3\", \"Key4\": \"Value4\"}"
``` Above is just generating the data ```

| spath path="Key2{}.subKey2_2{}" output="json"
| fromjson json
| mvexpand json
| spath input=json
| search subkey2_2_key1="value1_sub22_2"
| table subkey2_2_key1, subkey2_2_key2

That gives you:

If you don't want to figure out the spath path (Key2{}.subKey2_2{}) then you can just use the fromjson command:

``` As above... then: ```
| fromjson _raw
| fromjson Key2
| mvexpand subKey2_2
| fromjson subKey2_2
| search subkey2_2_key1="value1_sub22_2"
| table subkey2_2_key1, subKey2_2_key2, *

Hopefully that points you in the right direction.

Cheers,
Daniel