Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
JSON comparison operations in SQL for PostgreSQL
CREATE OR REPLACE FUNCTION json_cmp(left json, right json)
RETURNS integer AS $$
select bttextcmp($1::text, $2::text)
$$ LANGUAGE sql IMMUTABLE STRICT;
CREATE OR REPLACE FUNCTION json_eq(json, json)
RETURNS BOOLEAN LANGUAGE SQL STRICT IMMUTABLE AS $$
SELECT json_cmp($1, $2) = 0;
$$;
CREATE OR REPLACE FUNCTION json_lt(json, json)
RETURNS BOOLEAN LANGUAGE SQL STRICT IMMUTABLE AS $$
SELECT json_cmp($1, $2) < 0;
$$;
CREATE OR REPLACE FUNCTION json_lte(json, json)
RETURNS BOOLEAN LANGUAGE SQL STRICT IMMUTABLE AS $$
SELECT json_cmp($1, $2) <= 0;
$$;
CREATE OR REPLACE FUNCTION json_gt(json, json)
RETURNS BOOLEAN LANGUAGE SQL STRICT IMMUTABLE AS $$
SELECT json_cmp($1, $2) > 0;
$$;
CREATE OR REPLACE FUNCTION json_gte(json, json)
RETURNS BOOLEAN LANGUAGE SQL STRICT IMMUTABLE AS $$
SELECT json_cmp($1, $2) >= 0;
$$;
CREATE OPERATOR = (LEFTARG = json, RIGHTARG = json, PROCEDURE = json_eq);
CREATE OPERATOR < (LEFTARG = json, RIGHTARG = json, PROCEDURE = json_lt);
CREATE OPERATOR <= (LEFTARG = json, RIGHTARG = json, PROCEDURE = json_lte);
CREATE OPERATOR > (LEFTARG = json, RIGHTARG = json, PROCEDURE = json_gt);
CREATE OPERATOR >= (LEFTARG = json, RIGHTARG = json, PROCEDURE = json_gte);
CREATE OPERATOR CLASS json_ops
DEFAULT FOR TYPE json USING btree AS
OPERATOR 1 <,
OPERATOR 2 <=,
OPERATOR 3 =,
OPERATOR 4 >=,
OPERATOR 5 >,
FUNCTION 1 json_cmp(json, json);
@thmb

This comment has been minimized.

Copy link

@thmb thmb commented Jun 17, 2020

Awesome! Your code rocks my database! Thank you very much!!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment