UNION ALL on JSON data type

Question

I need to UNION ALL a JSON column in Postgres 9.2. But Postgres replies with this error:

ERROR: could not identify an equality operator for type json SQL  
state: 42883  
Character: 9

To the query:

(select cast('{"billingcode" : "' || billingcode || '"}' as JSON)
 from billing_2012_08 limit 10)
union
(select cast('{"charged" : "' || charged || '"}' as JSON)
 from sending_response_2012_08 limit 10)

What's wrong here?

It seems that Postgres doesn't have an equality operator for the json data type.
If this is correct, why?

As an example trying to figure out the problem, this works fine:

(select cast('{"billingcode" : "' || billingcode || '"}' as JSON)
 from billing_2012_08 limit 10)
union all
(select cast('{"charged" : "' || charged || '"}' as JSON)
 from sending_response_2012_08 limit 10)

Note, UNION ALL just "adds" results, as opposed to just UNION which eliminates duplicate values.

Answer 1

It is not trivial to test whether JSON values are "equal". Among other things, attributes can be sorted in any order, or there can be any amount of insignificant white space. So the binary or text representation can be completely different while the value still qualifies as equal according to JSON specifications. That's why there is no equality operator for the data type json in PostgreSQL.

If you are satisfied with the text representations being equal (as it seems from the example) you could UNION ALL with a text column and cast to json later:

SELECT json_col::json
FROM (
   (SELECT '{"billingcode" : "' || billingcode || '"}'::text AS json_col
    FROM   billing_2012_08 LIMIT 10)
   UNION ALL
   (SELECT '{"charged" : "' || charged || '"}'::text
    FROM   sending_response_2012_08 LIMIT 10)
   ) sub

Or you can use jsonb in Postgres 9.4 or later, which comes with the previously missing equality operator (among other things). See:

• How to remove known elements from a JSON[] array in PostgreSQL?
• SELECT UNION from two views of the same table

Then consider this alternative query:

SELECT to_jsonb(t) AS jsonb_col
FROM  (SELECT billingcode FROM billing_2012_08 LIMIT 10) t

UNION
SELECT to_jsonb(t)   -- also preserves possible numeric type!
FROM  (SELECT charged FROM sending_response_2012_08 LIMIT 10) t

ORDER  BY 1;  -- possible with jsonb

In addition to UNION, ORDER BY is also possible now.

Note the use of to_jsonb(). By feeding it a row, column name(s) are used as key names automatically. This is cleaner and faster and (among other things) preserves possible numeric types, which can affect equality and sort order. (to_json() is also available.)

db<>fiddle here
_{Old sqlfiddle}