Nested json array with missing fields into Splunk Table

[dheeru487]

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:

index	apple	carrot
2	29
2		12
2	54	23
2	67	9

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

[to4kawa · Accepted solution]

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