PostgreSQL 9.4 deep merge jsonb values

Requirements and situation

We're currently using a JSONB column to speed up somewhat arbitrary searches in our database and this is working fine so far. When updating the data the requirement is as follows:

• Any new value will overwrite any existing value. This includes nulls and arrays.

• Any (nested) objects will be merged.

• Null values as well as empty arrays and objects will be removed (should they exist at all).

To illustrate that, consider this example:

Existing (contains a null value for illustration purposes):

{
  "a":null, 
  "b":1, 
  "c":1,
  "f":1, 
  "g": { 
     "nested": 1 
  }
}

This should be merged into the existing object:

{
  "b":2, 
  "d":null, 
  "e":2, 
  "f":null, 
  "g":{
    "nested": 2
  }
}

As you can see we're overriding a few fields and remove f. So the expected output would be:

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

To achieve this we're using the following function:

```
CREATE OR REPLACE FUNCTION jsonb_merge(jsonb1 JSONB, jsonb2 JSONB)
RETURNS JSONB LANGUAGE sql IMMUTABLE
AS $$
SELECT
CASE
WHEN jsonb_typeof($1) = 'object' AND jsonb_typeof($2) = 'object' THEN
(
SELECT jsonb_object_agg(merged.key, merged.value) FROM
(
SELECT
COALESCE( p1.key, p2.key ) as key,
CASE
WHEN p1.key IS NULL then p2.value
WHEN p2.key IS NULL THEN p1.value
ELSE jsonb_merge( p1.value, p2.value )
END AS value
FROM jsonb_each($1) p1
FULL OUTER JOIN jsonb_each($2) p2 ON p1.key = p2.key
) AS merged
-- Removing this condition reduces runtime by 70%
WHERE NOT (merged.value IS NULL OR merged.value in ( '[]', 'null', '{}') )
)
WHEN jsonb_typeof($2) = 'null' OR (jsonb_typeof($2) = 'array' AND jsonb_array_length($2) < 1)

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

StackExchange Database Administrators Q#166092, answer score: 3