Postgres Create JSON Count of Row Value

Question

I'm trying to create a view with aggregated data displayed as a json object:

 thing_named_not_unique | emergency_code 
------------------------+----------------
foo                     |  1
foo                     |  2
foo                     |  1
bar                     |  1
bar                     |  2

To have the results be

 thing_named_not_unique | emergency_code_counts 
------------------------+----------------
foo                     |  {'1': 2, '2': 1}
bar                     |  {'1': 1, '2': 1}

I'm pretty new to Postgres JSON functions and can't figure out how to build an aggregate for this.

user330315 · Accepted Answer · 2021-03-18 20:22:34Z

3

You need to aggregate twice. The first aggregation is to calculate the count of emergency codes per "thing". The second aggregation then aggregates the counts for each thing:

select thing_named_not_unique, jsonb_object_agg(emergency_code, cnt) as emergency_code_counts
from (
  select thing_named_not_unique, emergency_code, count(*) as cnt
  from the_table
  group by thing_named_not_unique, emergency_code
) t
group by thing_named_not_unique
order by thing_named_not_unique

answered Mar 18, 2021 at 20:22

user330315

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

Collectives™ on Stack Overflow

Postgres Create JSON Count of Row Value

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related