aggregate rows as jsonb in postgres

Question

key | value | col ---------------------------------------- color | red | g1 color | blue | g2 contrast | {'l':123456} | g1 setting | srnew | g2 contrast | {'s':123456} | g1 key | value | col --------------------------------------------- color | ['red'] | g1 color | ['blue'] | g2 contrast | [{'l':123456}, {'s':456}]| g1 setting | ['srnew'] | g2

How would I aggregate the first table into the second table?

Column value is of type jsonb. I want the result column value to be jsonb too.

D-Shih · Accepted Answer · 2018-09-04 06:58:45Z

2

You can try to use array_agg do group by concat then array_to_json to json array.

SELECT array_to_json(array_agg(value)) FROM t group by key,col

answered Sep 4, 2018 at 6:58

D-Shih

46.5k6 gold badges37 silver badges56 bronze badges

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

2 Comments

D-Shih Over a year ago

No problem glad to help

Bharath Over a year ago

i replaced the array_to_json(array_agg(_)) with jsonb_agg. because i wanted a jsonb as the column type.

Collectives™ on Stack Overflow

aggregate rows as jsonb in postgres

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related