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
Ultra Champion
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
Ultra Champion
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
Get Updates on the Splunk Community!

Stay Connected: Your Guide to November Tech Talks, Office Hours, and Webinars!

🍂 Fall into November with a fresh lineup of Community Office Hours, Tech Talks, and Webinars we’ve ...

Transform your security operations with Splunk Enterprise Security

Hi Splunk Community, Splunk Platform has set a great foundation for your security operations. With the ...

Splunk Admins and App Developers | Earn a $35 gift card!

Splunk, in collaboration with ESG (Enterprise Strategy Group) by TechTarget, is excited to announce a ...