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.

Answer 1

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.