this is an example of what I'm trying to do,
SELECT *
FROM ( VALUES ('{"a":1}'::json) )
AS t(data)
WHERE data = '{"a":1}'::json;
But it's giving me this error,
ERROR: operator does not exist: json = json
LINE 4: WHERE data = '{"a":1}'::json;
^
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
test=> SELECT '{"x":1}'::json = '{"y":1}'::json;
ERROR: operator does not exist: json = json
LINE 1: SELECT '{"x":1}'::json = '{"y":1}'::json;
^
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
vs
test=> SELECT '{"x":1}'::jsonb = '{"y":1}'::jsonb;
?column?
----------
f
(1 row)
There is no = operator for json. That's because it just stores the raw json text, and it's not clear what exactly equality means in that case: is the json text the same, or is the json content logically equivalent? What about if there are duplicate keys in an object, and one of the duplicates is equal but one isn't?
The new binary-storage jsonb type in PostgreSQL 9.4 stores only the logical structure of the json data, and has equality operators.
