Postgres: concatenate JSONB values across rows?

Question

I'm getting grips with the JSONB functionality in Postgres >= 9.5 (and loving it) but have hit a stumbling block. I've read about the ability to concatenate JSON fields, so '{"a":1}' || '{"b":2}' creates {"a":1,"b":2}, but I'd like to do this across the same field in multiple rows. e.g.:

select row_concat_??(data) from table where field = 'value'

I've discovered the jsonb_object_agg function which sounds like it would be what I want, except that the docs show it taking multiple arguments, and I only have one.

Any ideas how I would do this? jsonb_agg creates an array successfully so it feels like I'm really close.

Alastair · Accepted Answer · 2016-09-16 14:31:59Z

9

After some digging around with custom aggregates in Postgres, I have the following:

DROP AGGREGATE IF EXISTS jsonb_merge(jsonb);

CREATE AGGREGATE jsonb_merge(jsonb) (
    SFUNC = jsonb_concat(jsonb, jsonb),
    STYPE = jsonb,
    INITCOND = '{}'
)

Which is then usable as:

SELECT group_id, jsonb_merge(data) FROM table GROUP BY group_id

edited Sep 16, 2016 at 14:31

answered Sep 16, 2016 at 14:11

Alastair

6,1847 gold badges38 silver badges69 bronze badges

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

2 Comments

klin Over a year ago

You do not need to create the function, use jsonb_concat(jsonb, jsonb).

Alastair Over a year ago

Oh wow, you're right. So much easier. Edited to reflect that, thanks @klin!

klin · Accepted Answer · 2016-09-15 13:32:34Z

2

Use jsonb_each():

with data(js) as (
    values
    ('{"a": 1}'::jsonb),
    ('{"b": 2}')
)
select jsonb_object_agg(key, value)
from data
cross join lateral jsonb_each(js);

 jsonb_object_agg 
------------------
 {"a": 1, "b": 2}
(1 row)

answered Sep 15, 2016 at 13:32

klin

123k15 gold badges240 silver badges262 bronze badges

Collectives™ on Stack Overflow

Postgres: concatenate JSONB values across rows?

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related