PostgreSQL compare two jsonb objects
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)
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)
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.