How to extract sum totals for data in a nested JSON object?

[stephenmelrose]

Hi there,

I have the following log line format (slightly edited for anonymity),

2013-08-14T08:54:10.098+0100 [INFO] {process-0.1735} spin-processor - [TIMER] some.request.complete {"id":"a1b2c3d4e5","sequenceNo":2,"fooId":"f6g7h8i9j0","duration":2.981,"totalActions":170,"actions":{"Foo.UPDATED":1,"Bar.UPDATED":169}}

The bit my question relates to is the actions key in the JSON object. Ultimately what I want is to get a table containing the sum total for each action type over a given time period, e.g.

+----------------------+
| Action       | Total |
+----------------------+
| Foo.INSERTED | 2     |
| Foo.UPDATED  | 56    |
| Bar.INSERTED | 169   |
| Bar.UPDATED  | 5678  |
+----------------------+

Any idea how I would do this?

The closest I've got so far is to manually do a sum() on each type (as I'm already SPATHing the JSON), but the table it gives me is the wrong way round to what I want, e.g.

+-----------------------------------------------------+
| sum(actions.Foo.UPDATED) | sum(actions.Bar.UPDATED) |
+-----------------------------------------------------+
| 56                       | 5678                     |
+-----------------------------------------------------+

Thanks.

[russellliss]

I was sort of able to do this myself. You may want to try

Based on your example above:

| spath input=some.request.complete actions{} | mvexpand actions{} | spath input=actions{} | stats sum("Bar.UPDATED") by "Foo.UPDATED"