Merging Concatenating JSON(B) columns in query

In Postgres 9.5+ you can merge JSONB like this:

select json1 || json2;

Or, if it's JSON, coerce to JSONB if necessary:

select json1::jsonb || json2::jsonb;

Or:

select COALESCE(json1::jsonb||json2::jsonb, json1::jsonb, json2::jsonb);

(Otherwise, any null value in json1 or json2 returns an empty row)

For example:

select data || '{"foo":"bar"}'::jsonb from photos limit 1;
                               ?column?
----------------------------------------------------------------------
 {"foo": "bar", "preview_url": "https://unsplash.it/500/720/123"}

Kudos to @MattZukowski for pointing this out in a comment.

Looks like nobody proposed this kind of solution yet, so here's my take, using custom aggregate functions:

create or replace aggregate jsonb_merge_agg(jsonb)
(
    sfunc = jsonb_concat,
    stype = jsonb,
    initcond = '{}'
);

create or replace function jsonb_concat(a jsonb, b jsonb) returns jsonb
    as 'select $1 || $2'
    language sql
    immutable
    parallel safe
;

Note: this is using || which replaces existing values at same path instead of deeply merging them. For a take on deep merge, see EDIT

Now jsonb_merge_agg is accessible like so:

select jsonb_merge_agg(some_col) from some_table [group by something];

EDIT: here is a take on recursive merge without stack explosion, as it uses with recursive:

create or replace function jsonb_deep_merge(a inout jsonb, b jsonb)
returns jsonb
strict
immutable
parallel safe
leakproof
language plpgsql as $$
declare
    op record;
begin
    for op in with recursive entry (path, value) as (
        select array[key], value from jsonb_each(b)
        union all
        select path || r.key, r.value
        from entry, jsonb_each(entry.value) r
        where entry.value is json object
    )
    select * from entry
    loop
        -- raise notice '%', op;
        a = jsonb_set(a, op.path, op.value);
    end loop;
end;
$$
;

Basically extracts the operations to perform on a by walking all the paths of b and jsonb_set for each vertex.

This function would merge nested json objects

create or replace function jsonb_merge(CurrentData jsonb,newData jsonb)
 returns jsonb
 language sql
 immutable
as $jsonb_merge_func$
 select case jsonb_typeof(CurrentData)
   when 'object' then case jsonb_typeof(newData)
     when 'object' then (
       select    jsonb_object_agg(k, case
                   when e2.v is null then e1.v
                   when e1.v is null then e2.v
                   when e1.v = e2.v then e1.v 
                   else jsonb_merge(e1.v, e2.v)
                 end)
       from      jsonb_each(CurrentData) e1(k, v)
       full join jsonb_each(newData) e2(k, v) using (k)
     )
     else newData
   end
   when 'array' then CurrentData || newData
   else newData
 end
$jsonb_merge_func$;

Also you can tranform json into text, concatenate, replace and convert back to json. Using the same data from Clément you can do:

SELECT replace(
    (json1::text || json2::text), 
    '}{', 
    ', ')::json 
FROM test

You could also concatenate all json1 into single json with:

SELECT regexp_replace(
    array_agg((json1))::text,
    '}"(,)"{|\\| |^{"|"}$', 
    '\1', 
    'g'
)::json
FROM test

This is a very old solution, since 9.4 you should use json_object_agg and simple || concatenate operator. Keeping here just for reference.

FYI, if someone's using jsonb in >= 9.5 and they only care about top-level elements being merged without duplicate keys, then it's as easy as using the || operator:

select '{"a1": "b2"}'::jsonb || '{"f":{"g" : "h"}}'::jsonb;
      ?column?           
-----------------------------
 {"a1": "b2", "f": {"g": "h"}}
(1 row)

However this question is answered already some time ago; the fact that when json1 and json2 contain the same key; the key appears twice in the document, does not seem to be best practice.

Therefore u can use this jsonb_merge function with PostgreSQL 9.5:

CREATE OR REPLACE FUNCTION jsonb_merge(jsonb1 JSONB, jsonb2 JSONB)
    RETURNS JSONB AS $$
    DECLARE
      result JSONB;
      v RECORD;
    BEGIN
       result = (
    SELECT json_object_agg(KEY,value)
    FROM
      (SELECT jsonb_object_keys(jsonb1) AS KEY,
              1::int AS jsb,
              jsonb1 -> jsonb_object_keys(jsonb1) AS value
       UNION SELECT jsonb_object_keys(jsonb2) AS KEY,
                    2::int AS jsb,
                    jsonb2 -> jsonb_object_keys(jsonb2) AS value ) AS t1
           );
       RETURN result;
    END;
    $$ LANGUAGE plpgsql;

The following query returns the concatenated jsonb columns, where the keys in json2 are dominant over the keys in json1:

select id, jsonb_merge(json1, json2) from test

Try this, if anyone having an issue for merging two JSON object

select table.attributes::jsonb || json_build_object('foo',1,'bar',2)::jsonb FROM table where table.x='y';

CREATE OR REPLACE FUNCTION jsonb_merge(pCurrentData jsonb, pMergeData jsonb, pExcludeKeys text[])
RETURNS jsonb IMMUTABLE LANGUAGE sql
AS $$
    SELECT json_object_agg(key,value)::jsonb
    FROM (
        WITH to_merge AS (
            SELECT * FROM jsonb_each(pMergeData) 
        )
        SELECT *
        FROM jsonb_each(pCurrentData)
        WHERE key NOT IN (SELECT key FROM to_merge)
     AND ( pExcludeKeys ISNULL OR key <> ALL(pExcludeKeys))
        UNION ALL
        SELECT * FROM to_merge
    ) t;
$$;

SELECT jsonb_merge('{"a": 1, "b": 9, "c": 3, "e":5}'::jsonb, '{"b": 2, "d": 4}'::jsonb, '{"c","e"}'::text[]) as jsonb

works well as an alternative to || when recursive deep merge is required (found here) :

create or replace function jsonb_merge_recurse(orig jsonb, delta jsonb)
returns jsonb language sql as $$
    select
        jsonb_object_agg(
            coalesce(keyOrig, keyDelta),
            case
                when valOrig isnull then valDelta
                when valDelta isnull then valOrig
                when (jsonb_typeof(valOrig) <> 'object' or jsonb_typeof(valDelta) <> 'object') then valDelta
                else jsonb_merge_recurse(valOrig, valDelta)
            end
        )
    from jsonb_each(orig) e1(keyOrig, valOrig)
    full join jsonb_each(delta) e2(keyDelta, valDelta) on keyOrig = keyDelta
$$;