PostgreSQL 9.4 deep merge jsonb values

I am with a_horse on this: Upgrade to Postgres 9.6 to have new options at your disposal (and for other reasons).

While stuck with 9.4, it might help to simplify like this:

CREATE OR REPLACE FUNCTION jsonb_merge2(jsonb1 JSONB, jsonb2 JSONB) 
  RETURNS JSONB LANGUAGE sql IMMUTABLE AS
$func$
SELECT
CASE    
   WHEN jsonb_typeof($1) = 'object' AND jsonb_typeof($2) = 'object' THEN
     (
       SELECT jsonb_object_agg(merged.key, merged.value)
       FROM  (
         SELECT key
              , CASE WHEN p1.value <> p2.value          -- implies both are NOT NULL
                     THEN jsonb_merge2(p1.value, p2.value) 
                     ELSE COALESCE(p2.value, p1.value)  -- p2 trumps p1
                END AS value 
         FROM   jsonb_each($1) p1 
         FULL   JOIN jsonb_each($2) p2 USING (key)      -- USING helps to simplify
         ) AS merged
       WHERE  merged.value IS NOT NULL                  -- simpler, might help query planner
       AND    merged.value NOT IN ( '[]', 'null', '{}' )
     ) 
   WHEN $2 IN ( '[]', 'null', '{}' ) THEN               -- just as simple as above
     NULL
   ELSE    
     $2    
 END
$func$;

Upgrade our 9.4 server to 9.5 or 9.6. Problem: the new operator doesn't work the way we need it to, so we'd still have to use a function or heavily refactor our queries. Additionally upgrading or even restarting our production servers is something we're meant to avoid as much as possible.

I think it does.

Using PostgreSQL 9.5+

First we need to create an aggregate function that does what you want, I copied this from here

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

Now we use it with jsonb_strip_nulls

SELECT jsonb_pretty(
  jsonb_strip_nulls(jsonb_object_agg(d ORDER BY id))
)
FROM ( VALUES
 ( 1, '{ "a":null, "b":1, "c":1, "f":1, "g": { "nested": 1 } }'::jsonb   ),
 ( 2, '{ "b":2, "d":null, "e":2, "f":null, "g":{ "nested": 2 } }'        )
) AS t(id,d);

{                  
    "b": 2,        
    "c": 1,        
    "e": 2,        
    "g": {         
        "nested": 2
    }              
}

What you wanted (so you don't have to scroll).

{
  "b": 2, //overridden
  "c": 1, //kept
  "e": 2, //added
  "g": {
    "nested": 2 //overridden
  }
}

A solution:

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
$$;

Found here

Usage Example: merging two input tables into one json documents column:

Given table A, a standard sql table with columns; and table B, containing a single JSONB column named 'doc, like the following target table and its jdoc column.

create unlogged table target(jdoc jsonb);
CREATE INDEX idx_gin_target ON target USING GIN ((jdoc->'keyA'),(jdoc->'keyB'),(jdoc->'jkeyC'),(doc->'jkeyD'));
insert into target select jsonb_merge_recurse(t2.doc,to_jsonb(t1)) from A t1 join B t2 on t1."keyA"  = t2.doc->>'keyA' and t1."keyB"::TEXT = t2.doc->>'keyB';

the result table contains a json column containing json documents made of the merge of json documents fields included in table B with column-values of table A.

To use the recursive power of the jsonb_merge_recurse function, better merge two json column each containing multi-leveled json documents.