JSON comparison operations in SQL for PostgreSQL

sql_json_comparison_ops.sql

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);

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

what does "bttextcmp" do here? lol

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