运算子不存在:json = json [英] Operator does not exist: json = json
问题描述
当我尝试从表中选择一些记录时
when I try to select some record from a table
SELECT * FROM movie_test WHERE tags = ('["dramatic","women", "political"]'::json)
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.
推荐答案
您无法比较json值.您可以改为比较文本值:
You cannot compare json values. You can compare text values instead:
SELECT *
FROM movie_test
WHERE tags::text = '["dramatic","women","political"]'
但是请注意,类型json
的值以给出它们的格式存储为文本.因此,比较的结果取决于您是否始终采用相同的格式:
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!
在Postgres 9.4+中,您可以使用jsonb
类型解决此问题,该类型以分解的二进制格式存储.可以比较此类型的值:
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
因此该查询更加可靠:
SELECT *
FROM movie_test
WHERE tags::jsonb = '["dramatic","women","political"]'::jsonb
详细了解 JSON类型.
这篇关于运算子不存在:json = json的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!