PostgreSQL custom operator for equality

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
WHE

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.

StackExchange Database Administrators Q#68915, answer score: 2