Last active
December 18, 2020 15:29
-
-
Save pozs/cd9649293d7cfe068f45 to your computer and use it in GitHub Desktop.
PostgreSQL function to test if two json values are equal
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- SQL function to test if two json values are equal | |
-- requires PostgreSQL 9.3+ | |
CREATE OR REPLACE FUNCTION json_equals(json, json) | |
RETURNS BOOLEAN | |
LANGUAGE sql | |
IMMUTABLE | |
STRICT | |
AS $function$ | |
SELECT CASE f1 | |
WHEN '{' THEN -- object | |
CASE f2 | |
WHEN '{' THEN ( | |
SELECT COALESCE(bool_and(k1 IS NOT NULL AND k2 IS NOT NULL AND json_equals(v1, v2)), TRUE) | |
FROM (SELECT DISTINCT ON (k1) * FROM json_each($1) AS j1(k1, v1) ORDER BY k1, row_number() OVER () DESC) AS j1 | |
FULL JOIN (SELECT DISTINCT ON (k2) * FROM json_each($2) AS j2(k2, v2) ORDER BY k2, row_number() OVER () DESC) AS j2 ON j1.k1 = j2.k2 | |
) | |
ELSE FALSE | |
END | |
WHEN '[' THEN -- array | |
CASE f2 | |
WHEN '[' THEN ( | |
SELECT COALESCE(bool_and(r1 IS NOT NULL AND r2 IS NOT NULL AND json_equals(e1, e2)), TRUE) | |
FROM (SELECT e1, row_number() OVER () AS r1 FROM json_array_elements($1) AS e1) AS e1 | |
FULL JOIN (SELECT e2, row_number() OVER () AS r2 FROM json_array_elements($2) AS e2) AS e2 ON e1.r1 = e2.r2 | |
) | |
ELSE FALSE | |
END | |
WHEN 'n' THEN -- null | |
CASE f2 | |
WHEN 'n' THEN TRUE | |
ELSE FALSE | |
END | |
WHEN 't' THEN -- true | |
CASE f2 | |
WHEN 't' THEN TRUE | |
ELSE FALSE | |
END | |
WHEN 'f' THEN -- false | |
CASE f2 | |
WHEN 'f' THEN TRUE | |
ELSE FALSE | |
END | |
WHEN '"' THEN -- string | |
CASE f2 | |
WHEN '"' THEN (CAST('[' || j1 || ']' AS json) ->> 0) | |
= (CAST('[' || j2 || ']' AS json) ->> 0) | |
ELSE FALSE | |
END | |
ELSE -- number | |
CASE f2 | |
WHEN '{' THEN FALSE | |
WHEN '[' THEN FALSE | |
WHEN 'n' THEN FALSE | |
WHEN 't' THEN FALSE | |
WHEN 'f' THEN FALSE | |
WHEN '"' THEN FALSE | |
ELSE CAST(CAST('[' || j1 || ']' AS json) ->> 0 AS NUMERIC) | |
= CAST(CAST('[' || j2 || ']' AS json) ->> 0 AS NUMERIC) | |
END | |
END | |
FROM ( | |
SELECT TRIM(LEADING E'\x20\x09\x0A\x0D' FROM CAST($1 AS text)) AS j1, | |
TRIM(LEADING E'\x20\x09\x0A\x0D' FROM CAST($2 AS text)) AS j2 | |
) AS jsons, | |
LATERAL ( | |
SELECT SUBSTRING(j1 FROM 1 FOR 1) AS f1, | |
SUBSTRING(j2 FROM 1 FOR 1) AS f2 | |
) AS firsts | |
$function$; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- Equality operator & hash operator class for json | |
-- requires PostgreSQL 9.3+ | |
DROP OPERATOR CLASS IF EXISTS json_ops USING hash CASCADE; | |
DROP OPERATOR IF EXISTS = (json, json) CASCADE; | |
CREATE OPERATOR = ( | |
PROCEDURE = json_equals, | |
LEFTARG = json, | |
RIGHTARG = json, | |
COMMUTATOR = =, | |
RESTRICT = eqsel, | |
JOIN = eqjoinsel, | |
HASHES, | |
MERGES | |
); | |
CREATE OR REPLACE FUNCTION json_hash(json) | |
RETURNS INTEGER | |
LANGUAGE sql | |
IMMUTABLE | |
STRICT | |
AS $function$ | |
SELECT CASE f | |
WHEN 'n' THEN 0 | |
WHEN 't' THEN 1 | |
WHEN 'f' THEN 2 | |
WHEN '"' THEN hashtext(CAST('[' || j || ']' AS json) ->> 0) | |
WHEN '[' THEN (SELECT bit_or(json_hash(e)) FROM json_array_elements($1) AS e) | |
WHEN '{' THEN (SELECT bit_and(hashtext(k) | json_hash(v)) FROM (SELECT DISTINCT ON (k) * FROM json_each($1) AS j(k, v) ORDER BY k, row_number() OVER () DESC) AS pairs) | |
ELSE hash_numeric(CAST(CAST('[' || j || ']' AS json) ->> 0 AS NUMERIC)) | |
END | |
FROM ( | |
SELECT TRIM(LEADING E'\x20\x09\x0A\x0D' FROM CAST($1 AS text)) AS j | |
) AS jsons, | |
LATERAL ( | |
SELECT SUBSTRING(j FROM 1 FOR 1) AS f | |
) AS firsts | |
$function$; | |
CREATE OPERATOR CLASS json_ops | |
DEFAULT | |
FOR TYPE json | |
USING hash AS | |
OPERATOR 1 =, | |
FUNCTION 1 json_hash(json); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Thx man!