Aggregate JSON in PostgreSQL

Question

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).

GMB · Accepted Answer · 2020-05-27 17:45:37Z

2

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

answered May 27, 2020 at 17:45

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Will Jenkins Over a year ago

that's really close - thanks! - but I don't need to aggregate the results for all rows, I need the totals for each row as a calculated column

GMB Over a year ago

@WillJenkins: ok, I was wondering. If the primary key of your table is id, then you just need to group by id at the end of the query (you might also want to add this column to the select list).

Will Jenkins Over a year ago

boom! that's it, nice one

Collectives™ on Stack Overflow

Aggregate JSON in PostgreSQL

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related