PostgreSQL custom operator for equality

Question

I want to build a custom equality operator in PostgreSQL, which can be used in GROUP BY, UNION and DISTINCT [ON] for the json type (just for the sake of curiosity, not for real-world implementation -- that's why the upcoming jsonb type is not what I'm looking for).

I could write a function, to test for json equality:

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

The custom equality operator could look like this:

DROP OPERATOR IF EXISTS = (json, json) CASCADE;

CREATE OPERATOR = (
  PROCEDURE   = json_equals,
  LEFTARG     = json,
  RIGHTARG    = json,
  COMMUTATOR  = =,
  RESTRICT    = eqsel,
  JOIN        = eqjoinsel,
  HASHES,
  MERGES
);

But I'm having trouble to create an operator class. I tried:

DROP OPERATOR CLASS IF EXISTS json_ops USING hash CASCADE;

CREATE OPERATOR CLASS json_ops
  DEFAULT
  FOR TYPE json
  USING hash AS
  OPERATOR 1 =;

Also:

DROP OPERATOR CLASS IF EXISTS json_ops USING btree CASCADE;

CREATE OPERATOR CLASS json_ops
  DEFAULT
  FOR TYPE json
  USING btree AS
  OPERATOR 3 =;

But still, a sample query cannot run:

SELECT *
FROM (VALUES (json '{"a":1,"a":2}'), ('{"a":2}')) v(a)
GROUP BY a;

-- ERROR: could not implement GROUP BY
-- SQL state: 0A000
-- DETAIL: Some of the datatypes only support hashing, while others only support sorting.

EDIT:

As it turned out, the problem was that the DDL statements and the sample query ran at the same transaction (or, the daily restart helped, which I doubt).

So, the new error message was:

ERROR: could not find hash function for hash operator 33794
SQL state: XX000

That could be easily solved with adding a json_hash(json) function to the class:

CREATE OPERATOR CLASS json_ops
  DEFAULT
  FOR TYPE json
  USING hash AS
  OPERATOR 1 =,
  FUNCTION 1 json_hash(json);

There were no need to create a btree opclass thankfully, because sorting json is not so trivial. This is my json hashing function, if someone interested:

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

Answer 1

Your answer/edit worked for me. The only thing I needed to change was:

...
WHEN '[' THEN COALESCE((SELECT ...), 3)
WHEN '{' THEN COALESCE((SELECT ...), 4)

to be able to handle empty arrays and empty objects.