运算子不存在:json = json

本文介绍了运算子不存在: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类型.