8

Is there a standard function in PostgreSQL (as of 12.x) to concatenate or merge many jsonb objects in a database column into a single jsonb object?

I know there is a the || operator since PostgreSQL 9.5 to merge two jsonb objects. But I need to merge many jsonb objects from a column. The linked documentation does not seem to have one unless I am missing something.

1

1 Answer 1

5

I had the same problem and this post solved it:

https://blog.faraday.io/how-to-aggregate-jsonb-in-postgres/

The idea is to create an aggregate:

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

See the linked article for more details.

2
  • 4
    Using the name jsonb_object_agg is dangerous, because that's also the name of a built-in aggregate function
    – user1822
    Nov 20, 2020 at 16:06
  • Your link is dead, can you update it or link to it via internet archive?
    – Mar
    Jul 27 at 15:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.