How to merge JSON fields in PostgreSQL

How to merge JSON fields in PostgreSQL

This is part of our series on PostgreSQL and things that are obvious once you see them. Nice!

Deprecated! Please see our new article How to aggregate JSONB in PostgreSQL 9.5+.

How do you combine (merge) JSON fields in Postgres? You define a new function json_collect()

# select * from greetings;
(3 rows)

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

json_collect is not built into Postgres, though. You have to create it yourself:

-- needed by json_collect
CREATE FUNCTION json_merge(data json, merge_data json) RETURNS json LANGUAGE sql IMMUTABLE
AS $$
  SELECT json_object_agg(key, value)
  FROM (
    WITH to_merge AS (
      SELECT * FROM json_each(merge_data)
    SELECT *
    FROM json_each(data)
    WHERE key NOT IN (SELECT key FROM to_merge)
    SELECT * FROM to_merge
  ) t;

CREATE AGGREGATE json_collect(json) (
  SFUNC = json_merge,
  STYPE = json,
  INITCOND = '{}'

Thanks to @matheusoliveira!