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 badges38 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

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related