0

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.
2
  • 3
    Hi. Welcome to Stack Exchange. Please read the help pages before posting. This seems to just be a copy and paste of an error message and some SQL. While it's good to show the exact errors, you should also explain what you're trying to do, where you got stuck, and what you tried to do before posting. It might be a good idea to edit your question. Commented Jul 8, 2015 at 5:44
  • May be helpful: dba.stackexchange.com/a/64765/3684 Commented Jul 8, 2015 at 6:20

1 Answer 1

5
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.

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