Operator does not exist: json = json

Question

when I try to select some record from a table

    SELECT * FROM movie_test WHERE tags = ('["dramatic","women", "political"]'::json)

The sql code cast a error

LINE 1: SELECT * FROM movie_test WHERE tags = ('["dramatic","women",...
                                        ^
HINT:  No operator matches the given name and argument type(s). You might      need to add explicit type casts.

********** 错误 **********

ERROR: operator does not exist: json = json
SQL 状态: 42883
指导建议:No operator matches the given name and argument type(s). You might need to add explicit type casts.
字符:37

Did I miss something or where I can learn something about this error.

Solution

In short - use JSONB instead of JSON or cast JSON to JSONB.

You cannot compare json values. You can compare text values instead:

SELECT * 
FROM movie_test 
WHERE tags::text = '["dramatic","women","political"]'

Note however that values of type JSON are stored as text in a format in which they are given. Thus the result of comparison depends on whether you consistently apply the same format:

SELECT 
    '["dramatic" ,"women", "political"]'::json::text =  
    '["dramatic","women","political"]'::json::text      -- yields false!
    

In Postgres 9.4+ you can solve this problem using type JSONB, which is stored in a decomposed binary format. Values of this type can be compared:

SELECT 
    '["dramatic" ,"women", "political"]'::jsonb =  
    '["dramatic","women","political"]'::jsonb           -- yields true

so this query is much more reliable:

SELECT * 
FROM movie_test 
WHERE tags::jsonb = '["dramatic","women","political"]'::jsonb

Read more about JSON Types.

Comments

[Craig Ringer]

It's strongly preferable to use jsonb rather than text comparisons, as a text comparison will report unequal results due to minor differences in formatting etc.

[Leo Folsom]

Thank you! In case it is helpful to anyone else, List.where("nutrition_constraints::jsonb -> 'vegetarian' ? '1'").first.

[Hartmut Pfarr]

Thanks. Would be happy if somebody knows the background/reason for this: "You cannot compare json values. You can compare text values instead". What is the issue here?

[klin]

@HartmutPfarr - Values of the Postgres JSON type (as opposed to the JSONB) are stored as text. The developers were aware that comparing text representations is not the same as comparing JSON objects. For this reason, they did not define the equality operator for this type. Anyway, you can consider the JSON type obsolete and use JSONB instead.

[poshest]

->> returns a string instead of the JSON, so when I get complex nested JSON documents, do I really need to keep casting the text result of ->> back to jsonb at every stage? Eg (fullresult is already jsonb) ((fullresult ->> 'country')::jsonb -> 0 ->> 'city')::jsonb ->> 'street'

[klin]

@poshest - fullresult -> 'country' -> 0 -> 'city' ->> 'street'