extract all the key-value pairs from the json data

[darrfang]

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"}

[livehybrid · Accepted solution]

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

[darrfang]

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

[livehybrid]

@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 🙂