Getting Data In

How to convert JSON Keys and values as columns in splunk

sdaruna
Explorer

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
Tags (1)
0 Karma
1 Solution

javiergn
Super Champion

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):

alt text

View solution in original post

0 Karma

javiergn
Super Champion

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):

alt text

0 Karma

jkat54
SplunkTrust
SplunkTrust
0 Karma

sdaruna
Explorer

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

0 Karma
Get Updates on the Splunk Community!

Federated Search for Amazon S3 | Key Use Cases to Streamline Compliance Workflows

Modern business operations are supported by data compliance. As regulations evolve, organizations must ...

New Dates, New City: Save the Date for .conf25!

Wake up, babe! New .conf25 dates AND location just dropped!! That's right, this year, .conf25 is taking place ...

Introduction to Splunk Observability Cloud - Building a Resilient Hybrid Cloud

Introduction to Splunk Observability Cloud - Building a Resilient Hybrid Cloud  In today’s fast-paced digital ...