How to extract out a nested key and its values from a JSON object where the key names are dynamic?

[splunk_dev]

Hi , 

I have a JSON object of following type :

{

 "time": "14040404.550055",

"Food_24ww": {

     "Grains" : {

              "status" : "OK",

             "report": {

                  "2014": {

                          "type" :"rice",

                          "prod" : "50",

                          "rate"  : "30"

                  },

               "2015": {

                       "type": "pulses",

                       "prod" : "50",

                      "rate"  : "30"

               }

      }

},

   "Beverages" : {

        "status": "Good",

       "2014": {

           "type" :"pepsi",

          "prod" : "50",

          "rate"  : "60"

        },

     "2015": {

         "type": "coke",

         "prod" : "55",

         "rate"  : "30"

      }

   }

 }

}

I want to extract all the key values inside "report" key for "Grains" and "Beverages".

Means , for Grains , I want 2014 (and key values inside it), 2015 (and key values inside it) , similarly for Beverages.

Now the challenge is none of the JSON keys until "reports" are constant. 

The first key "Food_24ww" and the next level "Grains" and "Beverages" are not constant. 

Thanks

[ITWhisperer]

What would your expected results look like?

[splunk_dev]

Hi ,

It would look as below , for either of Grains or Beverages :

Lets say for Beverages 

year	type	prod	rate
2014	pepsi	50	60
2015	coke	55	30

Similar tabular representation will be applicable for Grains( in a separate table of course). 

Hope my answer is clear. 

Please let me know else will try to explain further.

Thanks

[PickleRick]

It's not that easy because you have several independent data points in the same event.

To complicate the case further, you don't even have your "categories" and those datapoints as entries in an array, but instead as separate members of the object (Beverages and Grains and members further down the object). So you can't use the typical "spath and mvexpand" approach because you have nothing to mvexpand and you don't know what to spath by in the first place.

With just one such json, you can indeed transpose the whole event and treat each field as separate event as @ITWhisperer showed. But with more events like this it's gonna get complicated.

instead of transposing you could try to use foreach to "combine" values from separate fields into "composite fields" and then mvexpand and split them into single fields but it's very very ugly and probably not very efficient.

Can't you get your data in some decent format in the first place?

[ITWhisperer]

Assuming a single event, try something like this

| spath
| fields - _raw
| transpose 0 column_name=field
| eval group=mvindex(split(field,"."),1)
| eval year=mvindex(split(field,"."),-2)
| where year=2014 or year=2015
| eval key=mvindex(split(field,"."),-1)
| eval {key}='row 1'
| fields - "row 1" key field
| stats values(*) as * by group year