Skip to content

Instantly share code, notes, and snippets.

@jordaaash
Created September 11, 2017 04:31
Show Gist options
  • Save jordaaash/d314c585e73e26cb2d00678316f71d8b to your computer and use it in GitHub Desktop.
Save jordaaash/d314c585e73e26cb2d00678316f71d8b to your computer and use it in GitHub Desktop.
Find the symmetric difference between two jsonb objects
CREATE OR REPLACE FUNCTION jsonb_diff(a_ jsonb, b_ jsonb)
RETURNS jsonb AS $$
SELECT jsonb_object_agg("keys"."key", "a"."value")
FROM (
SELECT jsonb_object_keys(a_) AS "key"
UNION
SELECT jsonb_object_keys(b_) AS "key"
) AS "keys"
LEFT JOIN (SELECT "key", "value" FROM jsonb_each(a_)) AS "a"
ON "a"."key" = "keys"."key"
LEFT JOIN (SELECT "key", "value" FROM jsonb_each(b_)) AS "b"
ON "b"."key" = "keys"."key"
WHERE "a"."value" != "b"."value"
OR "a"."key" || "b"."key" IS NULL;
$$ LANGUAGE sql IMMUTABLE;
SELECT jsonb_diff(
to_jsonb('{ "a": 1, "b": 2, "c": 3, "d": 4 }'::json),
to_jsonb('{ "a": 1, "b": 5, "c": 3, "x": 7 }'::json)
);
jsonb_diff
-----------------------------
{"b": 2, "d": 4, "x": null}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment