Skip to content

Instantly share code, notes, and snippets.

@adunstan
Last active April 18, 2023 10:26
Show Gist options
  • Save adunstan/32ad224d7499d2603708 to your computer and use it in GitHub Desktop.
Save adunstan/32ad224d7499d2603708 to your computer and use it in GitHub Desktop.
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
Copy link

thmb commented Jun 17, 2020

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

@joshxyzhimself
Copy link

what does "bttextcmp" do here? lol

@adunstan
Copy link
Author

bttextcmp is the Postgres builtin C level function for comparing text values.

It's probably better to use the extension I built for this: See https://bitbucket.org/adunstan/jsoncmp

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