CODEWITHSUNDEEP
postgresqljsonb
Eric
Eric

Reputation: 24910

Merge rows into jsonb object

(This is a follow up question after Postgresql - Count freuency of array or jsonb object)

In postgresql 12+, given following input rows:

rows

The expected output is:

uid   tag_freq

1     {'a':2, 'b':1, 'c':1}
2     {'a':1, 'b':2, 'c':2, 'e':1}
...

Output column tag_freq is jsonb object, and it's merged result for a user.

Is there any way to write such a query?

Upvotes: 1

Views: 914

Answers (1)

user330315
user330315

Reputation:

You can use jsonb_object_agg() for this:

select uid, jsonb_object_agg(tag, count) as tag_freq
from the_table
group by uid;

Upvotes: 5

Related Questions