Splunk Search

How to parse json with SPL?

Strangertinz
Path Finder

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"

Labels (5)
0 Karma
1 Solution

danspav
SplunkTrust
SplunkTrust

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:

danspav_0-1687216826043.png

danspav_1-1687216929276.png

 


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

 

View solution in original post

0 Karma

Strangertinz
Path Finder
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"
 
0 Karma

danspav
SplunkTrust
SplunkTrust

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:

danspav_0-1687499153157.png

Is that what you were looking for?

 

Cheers
Daniel

0 Karma

danspav
SplunkTrust
SplunkTrust

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:

danspav_0-1687216826043.png

danspav_1-1687216929276.png

 


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

 

0 Karma
Get Updates on the Splunk Community!

What's New in Splunk Cloud Platform 9.2.2403?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.2.2403! Analysts can ...

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...

Edge Processor Scaling, Energy & Manufacturing Use Cases, and More New Articles on ...

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...