Postgresql – merging many jsonb objects using aggregate functions in PostgreSQL

aggregatejsonpostgresql-12

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.

Best Answer

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.

Related Solutions

Postgresql – Longest matching suffix while using aggregate functions

What is wrong with the (maybe too obvious?):

select * from noun n, noun_inflection ni
where
  n.label = 'handlebar' and
  n.label ilike '%'||ni.label_singular
order by
  char_length(ni.label_singular) DESC
limit 1;

Postgresql – How to combine concatenate operator on JSON values with AS

Not exactly sure what you are asking, but in Postgres 9.3 or later you can use json_agg() to translate a whole table into a JSON array of records. Prepare the rows in a single subquery and feed whole rows to json_agg() using the table alias:

Multiple layers of nested subselects like you display seem uncalled for.

Simple example:

CREATE TABLE tbl (
  tbl_id serial PRIMARY KEY,
  foo    text
);

INSERT INTO tbl VALUES
  (1, 'First' )
, (2, 'Second');

Query:

SELECT json_agg(sub)
FROM  (
   SELECT tbl_id        AS some_id
        , foo           AS bar
        , tbl_id || foo AS some_derived_column
   FROM  tbl
   ) sub;

Result:

[{"some_id":1,"bar":"First","some_derived_column":"1First"}, 
 {"some_id":2,"bar":"Second","some_derived_column":"2Second"}]

SQL Fiddle.

Best Answer

Related Solutions

Postgresql – Longest matching suffix while using aggregate functions

Postgresql – How to combine concatenate operator on JSON values with AS

Related Question