How to split nested json objects?

[jcw1407]

I have JSON that is really an array of values but has been encoded as objects, something like this

{ "metrics": 
  { "timers" : 
     { "foo_timer":
         {
           "count": 1,
           "max": 452603,
           "mean": 452603,
           "min": 452603
         },
       "bar_some_other_timer":
         {
            "count": 1,
            "max": 367110,
            "mean": 367110,
            "min": 367110
          }
     }
   }
} 

I can display this in a table by iterating using foreach, but what I really want to do is search for events where max > 400000, and then display it with the name of the timer - so in above that would match foo_timer.  The names of the timer can be anything and the order is not guaranteed.

I've tried all sorts today and keep coming up short.

[yuanliu · Accepted solution]

Any day JSON structure is concerned, I'll take array formulated as objects rather than objects formulated as array.  See if foreach produces something useful:

| rename metrics.timers.*.max as max_*
| fields max_* ``` just to clean view, not part of calculation ```
| foreach max_*
    [| eval maxfield = mvappend(maxfield, if(<<FIELD>> > 400000, "<<FIELD>>=" . <<FIELD>>, null()))]
| fields - max_*, _raw ``` again, just to clean view ```

Note the above is just to produce a singular field named  maxfield to carry the information you wanted in the a readable format.  For two events like

Event 1:
{ "metrics": 
  { "timers" : 
     { "foo_timer":
         {
           "count": 1,
           "max": 452603,
           "mean": 452603,
           "min": 452603
         },
       "bar_some_other_timer":
         {
            "count": 1,
            "max": 367110,
            "mean": 367110,
            "min": 367110
          }
     }
   }
}
---
Event 2:
{"metrics": 
  { "timers" : 
     { "foo_timer":
         {
           "count": 1,
           "max": 452703,
           "mean": 452603,
           "min": 452603
         },
       "bar_some_other_timer2":
         {
            "count": 1,
            "max": 467110,
            "mean": 367110,
            "min": 367110
          }
     }
   }
}

the output will be

_time	maxfield
2022-02-11 00:55:01	max_foo_timer=452603
2022-02-11 01:00:01	max_bar_some_other_timer2=467110 max_foo_timer=452703

You can operate on maxfield any way you like.  For example, you can add "| mvexpand mvfield" to produce

_time	maxfield
2022-02-11 00:55:01	max_foo_timer=452603
2022-02-11 01:00:01	max_bar_some_other_timer2=467110 max_foo_timer=452703

or even "| mvexpand maxfield | rename maxfield as _raw | kv kvdelim="=" | rename _raw as maxfield" to produce

maxfield	_time	max_bar_some_other_timer2	max_foo_timer
max_foo_timer=452603	2022-02-11 00:48:16		452603
max_bar_some_other_timer2=467110	2022-02-11 00:53:16	467110
max_foo_timer=452703	2022-02-11 00:53:16		452703