Startup hacks and engineering miracles from your exhausted friends at Faraday

How to merge JSON fields in PostgreSQL

Seamus Abshere on

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;
         data
-----------------------
 {"es":"Saludos"}
 {"en":"Hello"}
 {"ru":"Здравствуйте"}
(3 rows)

# select json_collect(data) from greetings;
                        json_collect
-------------------------------------------------------------
 { "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)
    UNION ALL
    SELECT * FROM to_merge
  ) t;
$$;

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

Thanks to @matheusoliveira!