Startup hacks and engineering miracles from your exhausted friends at Faraday

How to aggregate JSONB in PostgreSQL 9.5+

Seamus Abshere on

This is part of our series on PostgreSQL and things that are obvious once you see them. It's a 2017 update to our 2016 article How to merge JSON fields in Postgres Nice!

How do you aggregate (aka merge aka combine) JSONB fields in Postgres 9.5+? You define a new aggregate jsonb_collect(), used here:

# select * from greetings;
         data
-----------------------
 {"es":"Saludos"}
 {"en":"Hello"}
 {"ru":"Здравствуйте"}
(3 rows)

# select jsonb_collect(data) from greetings;
                        json_collect
-------------------------------------------------------------
 { "es" : "Saludos", "en" : "Hello", "ru" : "Здравствуйте" }
(1 row)

jsonb_collect is not built into Postgres. You have to create it yourself:

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

Note that jsonb_concat(jsonb, jsonb) is the function behind || that was introduced in Postgres 9.5.

It's just that simple!