topic Re: How to convert JSON Keys and values as columns in splunk in Getting Data In

How to convert JSON Keys and values as columns in splunk

sdaruna — Tue, 09 Aug 2016 01:02:00 GMT

Hi,

I want to flatten json data to columns for my report purpose. I might not be explaining my requirement properly, here is what my data and result has to be.

Input:

{
"name" : "srini",
"value" {
"1": "val1",
"2" : "val2",
"3" : "val3"
}
}

Output:

name, name.key, name.value
------------------------------------------
srini      1         val1
srini      2         val2
srini      3         val3

Re: How to convert JSON Keys and values as columns in splunk

jkat54 — Tue, 09 Aug 2016 01:07:32 GMT

Try this spath example:

https://docs.splunk.com/Documentation/Splunk/6.4.2/SearchReference/Spath#Example_3:_Extract_and_expand_JSON_events_with_multi-valued_fields

Re: How to convert JSON Keys and values as columns in splunk

javiergn — Tue, 09 Aug 2016 08:27:38 GMT

I think your JSON is wrong and there's a colon missing after value.
In any case, see if the below helps:

| makeresults
| eval json = "
{
   \"name\" : \"srini\",
   \"value\": {
      \"1\": \"val1\",
      \"2\" : \"val2\",
      \"3\" : \"val3\"
   }
}
"
| spath input=json
| fields - json
| untable name key value
| rex field=key "(?<key>\d+)"
| rename key AS name.key, value AS name.value

Output (see picture below):

Re: How to convert JSON Keys and values as columns in splunk

sdaruna — Tue, 09 Aug 2016 20:39:15 GMT

I have so many key value pairs under "value" field. So it is not possible to specify each and every key of "value" field.