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!

Birch

(not enough points to comment) for https://mcmap.net/q/441081/-postgresql-compare-two-jsonb-objects, it worked great, however the jsonb_typeof(val1) = 'null' check just works for 'null' strings/jsonb values. if you pass an actual null into val1, it will return null. changing IF val1 IS NULL OR jsonb_typeof(val1) = 'null' let me return whole of val2, for case where val1 was null (this scenario comes up when doing lag functions, for the first row) – Graph

Answer 1

UPDATED

CREATE OR REPLACE FUNCTION jsonb_diff_val(val1 JSONB,val2 JSONB)
RETURNS JSONB AS $$
DECLARE
  result JSONB;
  v RECORD;
BEGIN
   result = val1;
   FOR v IN SELECT * FROM jsonb_each(val2) LOOP
     IF result @> jsonb_build_object(v.key,v.value)
        THEN result = result - v.key;
     ELSIF result ? v.key THEN CONTINUE;
     ELSE
        result = result || jsonb_build_object(v.key,'null');
     END IF;
   END LOOP;
   RETURN result;
END;
$$ LANGUAGE plpgsql;

Query:

SELECT jsonb_diff_val(
    '{"a":"aaa", "b":"bbb", "c":"ccc"}'::jsonb,
    '{"a":"aaa", "b":"jjj", "d":"ddd"}'::jsonb
);
            jsonb_diff_val             
---------------------------------------
 {"b": "bbb", "c": "ccc", "d": "null"}
(1 row)

Stealer

Thanks for your answer: creating a function seems like a nice thing. However; '{"b":"bbb", "c":"ccc", "d":null}' is not a typo since "d" is not in row 1 and therefore the function should return "d":null – Norvan

I changed "json array" into "json objects" in my question; I was not aware of these definitions – Norvan

How can we use this with postgres 9.4 – Venenose

This is very elegant, Dmitry. I've used this for a simple json auditing/log trigger at github.com/rorycl/pg_json_logger -- hope that is ok. – Capri

Answer 2

I have created similar function that would scan the object recursively and will return the difference between new object and old object. I was not able to find a 'nicer' way to determine if jsonb object 'is empty' - so would be grateful for any suggestion how to simplify that. I plan to use it to keep track of updates made to the jsonb objects, so I store only what have changed.

Here is the function:

CREATE OR REPLACE FUNCTION jsonb_diff_val(val1 JSONB,val2 JSONB)
RETURNS JSONB AS $$
DECLARE
    result JSONB;
    object_result JSONB;
    i int;
    v RECORD;
BEGIN
    IF jsonb_typeof(val1) = 'null'
    THEN 
        RETURN val2;
    END IF;

    result = val1;
    FOR v IN SELECT * FROM jsonb_each(val1) LOOP
        result = result || jsonb_build_object(v.key, null);
    END LOOP;

    FOR v IN SELECT * FROM jsonb_each(val2) LOOP
        IF jsonb_typeof(val1->v.key) = 'object' AND jsonb_typeof(val2->v.key) = 'object'
        THEN
            object_result = jsonb_diff_val(val1->v.key, val2->v.key);
            -- check if result is not empty 
            i := (SELECT count(*) FROM jsonb_each(object_result));
            IF i = 0
            THEN 
                result = result - v.key; --if empty remove
            ELSE 
                result = result || jsonb_build_object(v.key,object_result);
            END IF;
        ELSIF val1->v.key = val2->v.key THEN 
            result = result - v.key;
        ELSE
            result = result || jsonb_build_object(v.key,v.value);
        END IF;
    END LOOP;

    RETURN result;

END;
$$ LANGUAGE plpgsql;

Then simple query looks like this:

SELECT jsonb_diff_val(                                                                                                                                                                                                                                                           
    '{"a":"aaa", "b":{"b1":"b","b2":"bb","b3":{"b3a":"aaa","b3c":"ccc"}}, "c":"ccc"}'::jsonb,
    '{"a":"aaa", "b":{"b1":"b1","b3":{"b3a":"aaa","b3c":"cccc"}}, "d":"ddd"}'::jsonb
);
                                jsonb_diff_val                                 
-------------------------------------------------------------------------------
 {"b": {"b1": "b1", "b2": null, "b3": {"b3c": "cccc"}}, "c": null, "d": "ddd"}
(1 row)

Sacaton

you can test for empty json object with IF object_result = '{}'::jsonb THEN – Cheer

Just as a note, because of jsonb_build_object and the use of || to concatenate jsonb objects, this is PG 9.5+. I think you could use json_build_object(...)::jsonb to get around the first problem but I dont think there was a way to concatenate two jsonb fields prior to 9.5. – Trireme

Answer 3

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.

Sculpsit

Answer 4

My solution is not recursive but you can use it for detecting common key/values:

-- Diff two jsonb objects
CREATE TYPE jsonb_object_diff_result AS (
  old jsonb,
  new jsonb,
  same jsonb
);
CREATE OR REPLACE FUNCTION jsonb_object_diff(in_old jsonb, in_new jsonb)
RETURNS jsonb_object_diff_result AS
$jsonb_object_diff$
DECLARE
  _key text;
  _value jsonb;
  _old jsonb;
  _new jsonb;
  _same jsonb;
BEGIN
  _old := in_old;
  _new := in_new;

  FOR _key, _value IN SELECT * FROM jsonb_each(_old) LOOP
    IF (_new -> _key) = _value THEN
      _old := _old - _key;
      _new := _new - _key;
      IF _same IS NULL THEN
        _same := jsonb_build_object(_key, _value);
      ELSE
        _same := _same || jsonb_build_object(_key, _value);
      END IF;
    END IF;
  END LOOP;

  RETURN (_old, _new, _same);
END;
$jsonb_object_diff$
LANGUAGE plpgsql;

Result can look like this:

SELECT * FROM jsonb_object_diff(
  '{"a": 1, "b": 5, "extra1": "woo", "old_null": null, "just_null": null}'::jsonb,
  '{"a": 1, "b": 4, "extra2": "ahoj", "new_null": null, "just_null": null}'::jsonb);

-[ RECORD 1 ]--------------------------------------
old  | {"b": 5, "extra1": "woo", "old_null": null}
new  | {"b": 4, "extra2": "ahoj", "new_null": null}
same | {"a": 1, "just_null": null}

Decane

Wow, that one is great! – Pyroclastic

Answer 5

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
;

Achaemenid

Answer 6

I achieved this by using the hstore extension.

CREATE EXTENSION hstore;

CREATE OR REPLACE FUNCTION log_history() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
  hs_new hstore;
  hs_old hstore;
BEGIN
  IF (TG_OP = 'DELETE') THEN
    INSERT INTO history(item_id, old_values, new_values)
     VALUES(OLD.id, row_to_json(OLD)::jsonb, NULL);
  ELSIF (TG_OP = 'INSERT') THEN
    INSERT INTO history(item_id, old_values, new_values)
     VALUES(NEW.id, NULL, row_to_json(NEW)::jsonb);
  ELSIF (TG_OP = 'UPDATE' AND NEW.* IS DISTINCT FROM OLD.*) THEN
    hs_new := hstore(NEW);
    hs_old := hstore(OLD);
    INSERT INTO history(item_id, old_values, new_values)
     VALUES(NEW.id, (hs_old - hs_new - 'updated_at'::text)::jsonb, (hs_new - hs_old - 'updated_at'::text)::jsonb);
  END IF;
  RETURN NULL;
END;
$$;

Notice that I used it to log history of any change happens on a specific table, and also I'm removing updated_at from the diff object.

Kemme

Answer 7

Improving upon @dmitry-s answer and added null check for val1 and val2. Please do note that, this is not recursive hence works for top-level JSON only.

CREATE OR REPLACE FUNCTION find_jsonb_diff(val1 JSONB, val2 JSONB)
RETURNS JSONB AS $$
DECLARE
  result JSONB;
  v RECORD;
BEGIN
   IF val1 IS NULL THEN
      RETURN val2;
   END IF;
   IF val2 IS NULL THEN
      RETURN val1;
   END IF;
   result = val1;
   FOR v IN SELECT * FROM jsonb_each(val2) LOOP
     IF result @> jsonb_build_object(v.key,v.value)
        THEN result = result - v.key;
     ELSIF result ? v.key THEN CONTINUE;
     ELSE
        result = result || jsonb_build_object(v.key,'null');
     END IF;
   END LOOP;
   RETURN result;
END;
$$ LANGUAGE plpgsql;

Freak