操作符不存在:json = json

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

sql代码抛出一个错误。

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.

我是否漏掉了什么，或者在哪里可以学到一些关于这个错误的东西。

1 个解决方案

#1

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

您不能比较json值。你可以比较文本值:

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:

但是请注意，json类型的值是作为文本存储在给定格式中的。因此，比较的结果取决于你是否一贯采用相同的格式:

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:

在Postgres 9.4+中，您可以使用jsonb类型来解决这个问题，jsonb存储在分解的二进制格式中。这种类型的值可以进行比较:

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

#1