如何分组/选择JSON类型列（PG :: UndefinedFunction：错误：无法识别json类型的相等运算符） [英] How to group/select JSON type column (PG::UndefinedFunction: ERROR: could not identify an equality operator for type json)

问题描述

我想做

<MODEL>.select("brief_content").group("brief_content")

这里是餐桌模式，

                :id => :integer,
           :content => :json,
     :brief_content => :json

但是我得到了错误，

我该怎么办，谢谢

companyAlarmLog Load (0.9ms)  SELECT company_alarm_logs.id, company_alarm_logs.name, company_alarm_logs.utc_time, company_alarm_logs.company_alarm_test_id, company_alarm_logs.brief_content, brief_content FROM "company_alarm_logs" GROUP BY brief_content ORDER BY utc_time
E, [2014-06-24T09:40:39.069988 #954] ERROR -- : PG::UndefinedFunction: ERROR:  could not identify an equality operator for type json
LINE 1: ...t, brief_content FROM "company_alarm_logs"  GROUP BY brief_cont...
                                                             ^
: SELECT company_alarm_logs.id, company_alarm_logs.name, company_alarm_logs.utc_time, company_alarm_logs.company_alarm_test_id, company_alarm_logs.brief_content, brief_content FROM "company_alarm_logs"  GROUP BY brief_content  ORDER BY utc_time
Hirb Error: PG::UndefinedFunction: ERROR:  could not identify an equality operator for type json
LINE 1: ...t, brief_content FROM "company_alarm_logs"  GROUP BY brief_cont...

推荐答案

不幸的是，没有简单的方法可以在9.3中直接进行 json 相等性测试。

Unfortunately there's no simple way to do direct json equality tests in 9.3.

9.3的 json 类型没有相等运算符，因为它接受具有重复键的json（许多实现都期望如此）。尚不清楚 { a：1， a：2} 是否等于 { a：1}

9.3's json type has no equality operator, because it accepts json with duplicate keys (as many implementations expect). It isn't clear if {"a":1, "a":2} is "equal" to {"a":1} or not.

9.4添加 jsonb 会将重复键折叠到最后一个键上-wins基础，使平等明确。

9.4 adds jsonb which collapses duplicate keys on a last-key-wins basis, making equality unambiguous.

regress=# SELECT '{"a":1, "a":2}'::json = '{"a":1}'::json;
ERROR:  operator does not exist: json = json
LINE 1: SELECT '{"a":1, "a":2}'::json = '{"a":1}'::json;
                                      ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

regress=# SELECT '{"a":1, "a":2}'::jsonb = '{"a":1}'::jsonb;
 ?column? 
----------
 f
(1 row)

不幸的是，这意味着您无法简单地在9.3版中完成所需的操作。

Unfortunately, this means you can't do what you want simply in 9.3.

您可以为编写自定义的相等运算符json -也许只是将它们都转换为文本并进行比较，但这会处理 { a：1， b：2} 和 { b：2， a：1} 不相等。

You could write a custom equality operator for json - perhaps just casting both to text and comparing that way, but that'd treat {"a":1, "b":2} and {"b":2, "a":1} as unequal.

一个更好的选择是安装PL / V8并使用V8 JavaScript引擎的json操作执行相等比较。

A better option would be to install PL/V8 and use the V8 JavaScript engine's json operations to perform the equality comparison.

为 json 定义一个相等运算符，然后使用该运算符定义一个简单的b树opclass。两者在SQL级别上都很简单-请参见 CREATE OPERATOR 和 CREATE OPERATOR CLASS 。

Define an equality operator for json, then define a simple b-tree opclass using that operator. Both are simple to do at the SQL level - see CREATE OPERATOR and CREATE OPERATOR CLASS.

完成后，您将能够在9.3版中 GROUP BY json值。

Once you've done that you'll be able to GROUP BY json values in 9.3.

或者您可以只安装9.4 beta1并使用 jsonb 。

Or you could just install 9.4 beta1 and use jsonb.

这篇关于如何分组/选择JSON类型列（PG :: UndefinedFunction：错误：无法识别json类型的相等运算符）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！