PostgreSQL compare two jsonb objects

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)

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)

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.