PostgreSQL compare two jsonb objects

Question

With PostgreSQL(v9.5), the JSONB formats give awesome opportunities. But now I'm stuck with what seems like a relatively simple operation;

compare two jsonb objects; see what is different or missing in one document compared to the other.

What I have so far

WITH reports(id,DATA) AS (
          VALUES (1,'{"a":"aaa", "b":"bbb", "c":"ccc"}'::jsonb),
                 (2,'{"a":"aaa", "b":"jjj", "d":"ddd"}'::jsonb) )
SELECT jsonb_object_agg(anon_1.key, anon_1.value)
FROM
  (SELECT anon_2.key AS KEY,
      reports.data -> anon_2.KEY AS value
   FROM reports,
     (SELECT DISTINCT jsonb_object_keys(reports.data) AS KEY
      FROM reports) AS anon_2
   ORDER BY reports.id DESC) AS anon_1

Should return the difference of row 1 compared to row 2:

'{"b":"bbb", "c":"ccc", "d":null}'

Instead it returns also duplicates ({"a": "aaa"}). Also; there might be a more elegant approach in general!

user_3557676 · Answer

Here is a solution without creating a new function;

SELECT
    json_object_agg(COALESCE(old.key, new.key), old.value)
  FROM json_each_text('{"a":"aaa", "b":"bbb", "c":"ccc"}') old
  FULL OUTER JOIN json_each_text('{"a":"aaa", "b":"jjj", "d":"ddd"}') new ON new.key = old.key 
WHERE 
  new.value IS DISTINCT FROM old.value

The result is;

{"b" : "bbb", "c" : "ccc", "d" : null}

This method only compares first level of json. It does NOT traverse the whole object tree.

user_9819342 · Answer

This does basically the same as what other folks have done, but reports changes in a right/left "delta" format.

Example:

SELECT jsonb_delta(
    '{"a":"aaa", "b":"bbb", "c":"ccc"}'::jsonb,
    '{"a":"aaa", "b":"jjj", "d":"ddd"}'::jsonb
);

Resolves to:

{"b": {"left": "bbb", "right": "jjj"}}

code:

CREATE OR REPLACE FUNCTION jsonb_delta(
    IN json_left JSONB
,   IN json_right JSONB
,   OUT json_out JSONB
) AS
$$
BEGIN

IF json_left IS NULL OR json_right IS NULL THEN
    RAISE EXCEPTION 'Non-null inputs required';
END IF
;

WITH
    base as
(
SELECT
    key
,   CASE
        WHEN a.value IS DISTINCT FROM b.value THEN jsonb_build_object('left', a.value, 'right', b.value)
        ELSE NULL
    END as changes
FROM jsonb_each_text(json_left) a
FULL OUTER JOIN jsonb_each_text(json_right) b using (key)
)
SELECT
    jsonb_object_agg(key,changes)
INTO json_out
FROM base
WHERE
    changes IS NOT NULL
;

json_out := coalesce(json_out, '{}');

END;
$$
LANGUAGE PLPGSQL
IMMUTABLE
PARALLEL SAFE
;

PostgreSQL compare two jsonb objects

5 Answers