Skip to content

Instantly share code, notes, and snippets.

@dmitryuk
Last active September 27, 2021 04:04
Show Gist options
  • Save dmitryuk/8bbf3d0720087c16973c8f4bc4b9917c to your computer and use it in GitHub Desktop.
Save dmitryuk/8bbf3d0720087c16973c8f4bc4b9917c to your computer and use it in GitHub Desktop.
Postgres JSONB diff recursive
CREATE OR REPLACE FUNCTION jsonb_diff_val(val1 JSONB, val2 JSONB) RETURNS JSONB AS $$
DECLARE
result JSONB;
difference jsonb;
v RECORD;
BEGIN
result = val1;
FOR v IN SELECT * FROM jsonb_each(val2) LOOP
IF result -> v.key is not null
THEN
if(jsonb_typeof(v.value) ='object') then
difference = jsonb_diff_val(result->v.key, v.value);
if (difference = '{}'::jsonb) then
result = result - v.key;
else
result = jsonb_set(result, array[v.key], difference);
end if;
else
if (result -> v.key = v.value) then
result = result - v.key;
else continue;
end if;
end if;
ELSE
result = result || jsonb_build_object(v.key, null);
END IF;
END LOOP;
RETURN result;
END;
$$ LANGUAGE plpgsql;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment