How to group/select JSON type column (PG::UndefinedFunction: ERROR: could not identify an equality operator for type json)

Question

I wanna do

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

Here's the table scheme ,

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

But I got the errors,

How can I do, Thanks

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...

Answer 1

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

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 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)

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

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.

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

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.

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

Or you could just install 9.4 beta1 and use jsonb.

Answer 2

.group('brief_content:text') should do the trick. Anyway it can be not accurate, when fields have different order in serialized json. In rails this should not be a problem.