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
Career Survey
First 500 qualified respondents will receive a $20 gift card! Tell us about your professional Splunk journey.
Get Updates on the Splunk Community!

Maximizing the Value of Splunk ES 8.x

Splunk Enterprise Security (ES) continues to be a leader in the Gartner Magic Quadrant, reflecting its pivotal ...

Operationalizing TDIR: Building a More Resilient, Scalable SOC

Optimizing SOC workflows with a unified, risk-based approach to Threat Detection, Investigation, and Response ...

Introducing .conf Stories Series!

“.conf Stories” Series – First Feature: Rich Mahlerwein   Every year .conf brings together some of the most ...