topic Re: parsing json with SPL in Splunk Search

How to parse json with SPL?

Strangertinz — Tue, 20 Jun 2023 02:21:20 GMT

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"

Re: parsing json with SPL

danspav — Mon, 19 Jun 2023 23:28:23 GMT

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:

Hopefully that points you in the right direction.

Cheers,
Daniel

Re: How to parse json with SPL?

Strangertinz — Wed, 21 Jun 2023 18:45:09 GMT

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"

Re: How to parse json with SPL?

danspav — Fri, 23 Jun 2023 05:48:09 GMT

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