Aggregate JSON in PostgreSQL

user_93812 0
Viewed 67

I have a json column with entries that look like this:

{
  "pages": "64",
  "stats": {
    "1": { "200": "55", "400": "4" },
    "2": { "200": "1" },
    "3": { "200": "1", "404": "13" },
  }
}

The 'stats' are collections (of various sizes) containing http status codes versus counts.

I would like to aggregate the stats into two calculated columns - one for the total number of 200 responses and the other for the total number of responses (including 200s).

pivot group-by sum postgresql json
1 Answers

You can use two lateral joins to unnest the inner objects, then do conditional aggregation:

select 
    sum(z.cnt::int) no_responses,
    sum(z.cnt::int) filter(where z.code::int = 200) no_200_responses
from mytable t
cross join lateral jsonb_each(t.data -> 'stats') as x(kx, obj)
cross join lateral jsonb_each_text(x.obj) as z(code, cnt)

Demo on DB Fiddle:

no_responses | no_200_responses
-----------: | ---------------:
          74 |               57
user_106767160
Related