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.

Improve this question
1

2 Answers 2

Reset to default
5

I had the same problem and this post solved it:

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.

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

Did not have high enough reputation to comment above - I think this is the updated link to the linked article from blog.faraday.io:

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

Whether this link is the same blog post or not, the content of this blog post gave me the solution to the problem.

Improve this answer
4
  • 1
    You should have suggested an edit to that post to update the link. Commented Mar 4 at 22:20
  • I didn't think suggesting an edit was necessary since someone else made that exact request above, nearly 2 years ago. At this point, I thought providing the useful information was more important than repeating the request to edit the link. Commented Mar 5 at 1:34
  • Perhaps the words "suggesting an edit" were misunderstood; on this site, if you want to improve a post, you click the "edit" link underneath and make the desired changes; other people will review the change and approve it, when appropriate, and the post will be modified accordingly. Commented Mar 5 at 1:51
  • Thank you for the clarification. I can't go back in time and see, but I believe because my reputation points are low I didn't have access to the edit link, but that may be because I was focused on replying to the comment initially, not the post. In the future, I will try to keep this in mind. Commented Mar 5 at 13:12

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.