topic Re: extract all the key-value pairs from the json data in Splunk Search

extract all the key-value pairs from the json data

darrfang — Tue, 11 Feb 2025 19:41:49 GMT

Hi splunk team,

I have a question about how to extract the key-value pair from json data. Let's say for example I have two raw data like this:

# raw data1: { "key1": { "key2": { "key3": [ {"data_value": {"aaa": "12345", "bbb": "23456"}} ] } } } # raw data 2: { "key1": { "key2": { "key3": [ {"data_value": {"ccc": "34567"}} ] } } }

how can I extract the key-value results in all the data_value, to be a table as:

node value aaa 12345 bbb 23456 ccc 34567

I current have a splunk query that could do part of it:

```some search...``` | spath output=pairs path=key1.key2.key3{}.data_value | rex field=hwids "\"(?<node>[^\"]+)\":\"(?<value>[^\"]+)\"" | table node value pairs

but this only gives me the result of all the first data, result would look like below, that ignore the data of "bbb":"23456". Please give me some advice on how to grab all the results, thanks!

node value pairs aaa 12345 {"aaa": "12345", "bbb": "23456"} ccc 34567 {"ccc": "34567"}

Re: extract all the key-value pairs from the json data

livehybrid — Tue, 11 Feb 2025 20:26:27 GMT

Hey @darrfang

How about this?

| makeresults | eval _raw="{ \"key1\": { \"key2\": { \"key3\": [ {\"data_value\": {\"aaa\": \"12345\", \"bbb\": \"23456\"}} ] } } }" | spath input=_raw output=data_value path=key1.key2.key3{}.data_value | mvexpand data_value | eval key_value=split(replace(data_value, "[\{\}\"]", ""), ",") | mvexpand key_value | rex field=key_value "\s?(?<node>[^:]+):(?<value>.*)" | table node value

Please let me know how you get on and consider accepting this answer or adding karma this answer if it has helped.
Regards

Will

Re: extract all the key-value pairs from the json data

darrfang — Tue, 11 Feb 2025 22:44:51 GMT

Hi @livehybrid

this works like magic! thanks a lot for giving me the insights!

Just wondering what's the reason at here that you did mvexpand twice

just did some test seems that if I remove `| mvexpand data_value` I can still get the same results / format

Re: extract all the key-value pairs from the json data

livehybrid — Tue, 11 Feb 2025 23:04:00 GMT

@darrfang When I tried I think it listed the two pairs within the same row in the table, whereas the second mvexpand broke them into their own rows.

I guess it depends what you're going to do with the data but if you wanted to sort or filter you might want them expanded further?

Either way, Im glad it worked out for you - Thanks for letting me know 🙂