5

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$;
  • Interesting. In general, index opclasses should always provide sorting operators, but I don't see why Pg needs to be able to sort in this case. – Craig Ringer Jun 24 '14 at 23:32
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.