How to aggregate JSONB PostgreSQL 10

Question

I have table with ids and jsonb colums

1, "{"a" : {"per" : 0.45, "number" : 1}}"
1, "{"b" : {"per" : 3.13, "number" : 7}}"
2, "{"a" : {"per" : 14.73, "number" : 33}}"
2, "{"b" : {"per" : 85.27, "number" : 191}}"
3, "{"a" : {"per" : 40.63, "number" : 91}}"
4, "{"a" : {"per" : 1.79, "number" : 4}}"
4, "{"b" : {"per" : 2.68, "number" : 6}}"

Need to group values by ids and get this result

1, "{"a" : {"per" : 0.45, "number" : 1}, "b" : {"per" : 3.13, "number" : 7}}"
2, "{"a" : {"per" : 14.73, "number" : 33}, "b" : {"per" : 85.27, "number" : 191}}"
3, "{"a" : {"per" : 40.63, "number" : 91}}"
4, "{"a" : {"per" : 1.79, "number" : 4}, "b" : {"per" : 2.68, "number" : 6}}"

Is there any way to achieve that?

user330315 · Accepted Answer · 2018-05-29 15:04:56Z

2

There is no built-in aggregate for that, but you can easily create your own:

CREATE AGGREGATE jsonb_merge(jsonb) 
(
    SFUNC = jsonb_concat(jsonb, jsonb),
    STYPE = jsonb
);

Then you can do:

select id, jsonb_merge(the_jsonb_column) 
from your_table
group by id

answered May 29, 2018 at 15:04

user330315

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

1 Comment

Ivan Piskunov Over a year ago

Works pretty well. Thank you.

Collectives™ on Stack Overflow

How to aggregate JSONB PostgreSQL 10

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related