0

I have a jsonb object in Postgres column named metrics with following structure:

{"http": {"GET": {"statusCode": {"200": {"count": 100}, "500": {"count": 322}}}}}

I want to get total count by different httpmethod, statusCode so that I can plot it using Grafana. Can someone point me how can I use group by. Expected result should be:

httpMethod statusCode sum(count)
GET        200        100
GET        500        322
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
sandeep singh
  • 13
  • 5

1 Answers1

0

Assuming the structure is constant, and counts are integer values:

SELECT h.key AS http_method
     , s.key AS status_code
     , sum((s.value->>'count')::int) AS sum_count
FROM   tbl t
     , jsonb_each(t.js->'http') h
     , jsonb_each(h.value->'statusCode') s
GROUP  BY h.key, s.key;

db<>fiddle here

jsonb_each() in an implicit CROSS JOIN LATERAL join is the key ingredient.

About LATERAL:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • @Erwin.Thanks very much. It works like a charm although now I am struggling a bit to get this into grafana but that's different battle to fight. – sandeep singh Mar 08 '21 at 15:42