Merging JSONB values in PostgreSQL?

Question

Using the || operator yields the following result:

select '{"a":{"b":2}}'::jsonb || '{"a":{"c":3}}'::jsonb ;
    ?column?     
-----------------
 {"a": {"c": 3}}
(1 row)

I would like to be able to do achieve the following result (?? just a placeholder for the operator):

select '{"a":{"b":2}}'::jsonb ?? '{"a":{"c":3}}'::jsonb ;
    ?column?     
-----------------
 {"a": {"b": 2, "c": 3}}
(1 row)

So, you can see the top-level a key has its child values "merged" such that the result contains both b and c.

How do you "deep" merge two JSONB values in Postgres?

Is this possible, if so how?

---

A more complex test case:

select '{"a":{"b":{"c":3},"z":true}}'::jsonb ?? '{"a":{"b":{"d":4},"z":false}}'::jsonb ;
    ?column?     
-----------------
 {"a": {"b": {"c": 3, "d": 4}, "z": false}}
(1 row)

Another test case where a primitive "merges over" and object:

select '{"a":{"b":{"c":3},"z":true}}'::jsonb ?? '{"a":{"b":false,"z":false}}'::jsonb ;
        ?column?         
-----------------
 {"a": {"b": false, "z": false}}
(1 row)

Answer 1

You should merge unnested elements using jsonb_each() for both values. Doing this in a non-trivial query may be uncomfortable, so I would prefer a custom function like this one:

create or replace function jsonb_my_merge(a jsonb, b jsonb)
returns jsonb language sql as $$
    select 
        jsonb_object_agg(
            coalesce(ka, kb), 
            case 
                when va isnull then vb 
                when vb isnull then va 
                else va || vb 
            end
        )
    from jsonb_each(a) e1(ka, va)
    full join jsonb_each(b) e2(kb, vb) on ka = kb
$$;

Use:

select jsonb_my_merge(
    '{"a":{"b":2}, "d": {"e": 10}, "x": 1}'::jsonb, 
    '{"a":{"c":3}, "d": {"f": 11}, "y": 2}'::jsonb
)

                          jsonb_my_merge                          
------------------------------------------------------------------
 {"a": {"b": 2, "c": 3}, "d": {"e": 10, "f": 11}, "x": 1, "y": 2}
(1 row)

---

You can slightly modify the function using recursion to get a solution working on any level of nesting:

create or replace function jsonb_recursive_merge(a jsonb, b jsonb)
returns jsonb language sql as $$
    select 
        jsonb_object_agg(
            coalesce(ka, kb), 
            case 
                when va isnull then vb 
                when vb isnull then va 
                when jsonb_typeof(va) <> 'object' then va || vb
                else jsonb_recursive_merge(va, vb)
            end
        )
    from jsonb_each(a) e1(ka, va)
    full join jsonb_each(b) e2(kb, vb) on ka = kb
$$;

Examples:

select jsonb_recursive_merge( 
    '{"a":{"b":{"c":3},"x":5}}'::jsonb, 
    '{"a":{"b":{"d":4},"y":6}}'::jsonb);

             jsonb_recursive_merge              
------------------------------------------------
 {"a": {"b": {"c": 3, "d": 4}, "x": 5, "y": 6}}
(1 row)

select jsonb_recursive_merge(
    '{"a":{"b":{"c":{"d":{"e":1}}}}}'::jsonb, 
    '{"a":{"b":{"c":{"d":{"f":2}}}}}'::jsonb)

            jsonb_recursive_merge             
----------------------------------------------
 {"a": {"b": {"c": {"d": {"e": 1, "f": 2}}}}}
(1 row)

---

Finally, the variant of the function with changes proposed by OP (see the comments below):

create or replace function jsonb_recursive_merge(a jsonb, b jsonb) 
returns jsonb language sql as $$ 
select 
    jsonb_object_agg(
        coalesce(ka, kb), 
        case 
            when va isnull then vb 
            when vb isnull then va 
            when va = vb then vb
            when jsonb_typeof(va) <> 'object' or jsonb_typeof(vb) <> 'object' then vb 
            else jsonb_recursive_merge(va, vb) end 
        ) 
    from jsonb_each(a) e1(ka, va) 
    full join jsonb_each(b) e2(kb, vb) on ka = kb 
$$;

Answer 2

Combining (merging :-D) answers from @klin, @pozs and comment from @Arman Khubezhov while also actually merging arrays instead of concatenating (which resulted in duplicates otherwise), came up with the following function:

create or replace function jsonb_merge_deep(jsonb, jsonb)
  returns jsonb
  language sql
  immutable
as $func$
  select case jsonb_typeof($1)
    when 'object' then
      case jsonb_typeof($2)
        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
               else jsonb_merge_deep(e1.v, e2.v)
             end
          )
          from jsonb_each($1) e1(k, v)
          full join jsonb_each($2) e2(k, v) using (k)
          )
        else COALESCE($2, $1)
      end
    when 'array' then
      (
        SELECT jsonb_agg(items.val)
        FROM (
          SELECT jsonb_array_elements($1) AS val
          UNION
          SELECT jsonb_array_elements($2) AS val
        ) AS items
      )
    else $2
  end
$func$;

Based on comment from @Arman Khubezhov, enhanced the case when any of $1 or $2 is null with:

    else COALESCE($2, $1)

And added real merge (no duplicate) of the 2 arrays values with:

when 'array' then
  (
    SELECT jsonb_agg(items.val)
    FROM (
      SELECT jsonb_array_elements($1) AS val
      UNION
      SELECT jsonb_array_elements($2) AS val
    ) AS items
  )

Glad if one can come up with a enhanced code for this one - like an existing PostreSQL function I am not aware of?

---

Pros: no data loss when combining 2 JSONB values or updating a JSONB field in an UPDATE query like.

UPDATE my_table
SET my_jsonb_field = jsonb_merge_deep(my_jsonb_field, '{ "a": { "aa" : { "aaa" : [6, 4, 7] } } }'::jsonb)

Cons: removing a key/value or array value requires a dedicated query.

Answer 3

As @lightSouls say, after PostgreSQL 9.5 you can use jsonb_set() function... But you must to learn how to use it!

jsonb_set can merge or destroy...

Supposing j:='{"a":{"x":1},"b":2}'::jsonb.

• jsonb_set(j, '{a,y}', '1'::jsonb); will merge object {"y":1} with object {"x":1}.
  Result: {"a": {"x": 1, "y": 1}, "b": 2}

• jsonb_set(j, '{a}', '{"x":1}'::jsonb); will destroy! replacing full old object by the new one.
  Result: {"a": {"x": 1}, "b": 2}

Answer 4

This kind of "deep merge" can be interpreted quite differently, depending on your use case. For completeness, my intuition usually dictates the following rules:

• object + object: Every property survives from each object, which is not in the other object (JSON's null value is considered to be in the object, if it's explicitly mentioned). When a property is in both objects, the merge continues recursively with the same rules (this point is usually agreed on).
• array + array: The result is the concatenation of the two arrays.
• array + primitive/object: the result is the first array, with the second JSON value appended to it.
• any other cases: The result is the second JSON value (so f.ex. primitives or incompatible types override each other).

---

create or replace function jsonb_merge_deep(jsonb, jsonb)
  returns jsonb
  language sql
  immutable
as $func$
  select case jsonb_typeof($1)
    when 'object' then case jsonb_typeof($2)
      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
                    else jsonb_merge_deep(e1.v, e2.v)
                  end)
        from      jsonb_each($1) e1(k, v)
        full join jsonb_each($2) e2(k, v) using (k)
      )
      else $2
    end
    when 'array' then $1 || $2
    else $2
  end
$func$;

This function's added bonus is that it can be called with literally any type of JSON values: always produces a result & never complains about JSON value types.

http://rextester.com/FAC95623

Answer 5

After PostgreSQL 9.5 you can use jsonb_set function:

1. '{a,c}' looking into path if it is not there, it will created.
2. '{"a":{"c":3}}'::jsonb#>'{a,c}' this will get the value of c

new_value added if create_missing is true ( default is true)

Hier is document jsonb -functions

select jsonb_set('{"a":{"b":2}}', '{a,c}','{"a":{"c":3}}'::jsonb#>'{a,c}' )

Result:  {"a":{"c":3,"b":2}}

Merge more attribute at once:

with jsonb_paths(main_part,missing_part) as (
values ('{"a":{"b":2}}','{"a":{"c":3,"d":4}}')
)
select jsonb_object_agg(t.k,t.v||t2.v)
from jsonb_paths,
jsonb_each(main_part::jsonb) t(k,v),
jsonb_each(missing_part::jsonb) t2(k,v);

result: {"a":{"c":3,"b":2,"d":4}}