Getting Data In

Nested json array with missing fields into Splunk Table

dheeru487
Engager

 

I have the below JSON event with nested array in splunk -:

 

{
   "index": 2,
   "rows": [
      {
         "apple": 29
      },
      {
         "carrot": 12
      },
      {
         "carrot": 54,
         "apple": 23
      },
      {
         "carrot": 67,
         "apple": 9
      }
   ]
}

 

Important thing to consider is that few entries in json array can have one or more missing fields. 

I want to write splunk query which would create table like following:

indexapplecarrot
229 
2 12
25423
2679



I could write a splunk query like following:

| makeresults
| eval _raw="{
\"index\":2,
\"rows\": [
{\"apple\": 29},
{\"carrot\": 12},
{\"carrot\": 54, \"apple\": 23},
{\"carrot\": 67, \"apple\":9}
]
}" 
| spath
| spath input=rows
| table index,rows{}.apple,rows{}.carrot

 

But it has two problems, 1- i need separate rows, 2 -  i need to maintain one-to-one mapping of individual columns

Labels (1)
0 Karma
1 Solution

to4kawa
SplunkTrust
SplunkTrust
index=_internal | head 1 | fields _raw 
| eval _raw="{
\"index\":2,
\"rows\": [
{\"apple\": 29},
{\"carrot\": 12},
{\"carrot\": 54, \"apple\": 23},
{\"carrot\": 67, \"apple\":9}
]
}"
| spath index 
| spath rows{} output=rows
| stats count by rows index
| spath input=rows
| fields - count rows

View solution in original post

to4kawa
SplunkTrust
SplunkTrust
index=_internal | head 1 | fields _raw 
| eval _raw="{
\"index\":2,
\"rows\": [
{\"apple\": 29},
{\"carrot\": 12},
{\"carrot\": 54, \"apple\": 23},
{\"carrot\": 67, \"apple\":9}
]
}"
| spath index 
| spath rows{} output=rows
| stats count by rows index
| spath input=rows
| fields - count rows

View solution in original post

.conf21 Now Fully Virtual!
Register for FREE Today!

We've made .conf21 totally virtual and totally FREE! Our completely online experience will run from 10/19 through 10/20 with some additional events, too!