Django db error: could not identify equality operator for json type when trying to annotate model using jsonfield

Question

I work in Django 1.5.4 and PostgreSQL 9.3 using django-jsonfield for JSONField.

The following query causes a db error (could not identify the equality operator for the json type):

ModelWithJsonField.objects.annotate(count=Count('field_to_count_by')) 

field_to_count_by not a JSONField, a normal int field.

Any ideas how I can solve the problem and still use annotation?

What does the annotation behind the hood do?

Answer 1

I ran into the same problem and finally (today) implemented a fake statement by doing this as admin in the psql console:

 -- This creates a function named hashjson that transforms the -- json to texts and generates a hash CREATE OR REPLACE FUNCTION hashjson( json ) RETURNS INTEGER LANGUAGE SQL STRICT IMMUTABLE AS $$ SELECT hashtext($1::text); $$; -- This creates a function named json_eq that checks equality (as text) CREATE OR REPLACE FUNCTION json_eq( json, json ) RETURNS BOOLEAN LANGUAGE SQL STRICT IMMUTABLE AS $$ SELECT bttextcmp($1::text, $2::text) = 0; $$; -- This creates an operator from the equality function CREATE OPERATOR = ( LEFTARG = json, RIGHTARG = json, PROCEDURE = json_eq ); -- Finaly, this defines a new default JSON operator family with the -- operators and functions we just defined. CREATE OPERATOR CLASS json_ops DEFAULT FOR TYPE json USING hash AS OPERATOR 1 =, FUNCTION 1 hashjson(json); 

(strongly inspired by this stream)

I also referred to your question in the django-jsonfield GitHub issue .

Note:

• I have a very limited idea of ​​the impact this will have. Maybe this is not a good idea. Implementations are naive, but there should be enough. Or maybe not.
• In particular, the equality operator checks for text equality, not semantic json equality. But as for django-jsonField, I think we have little chance that we really need the right results (maybe SELECT FALSE will even do the trick).

Answer 2

I went through the same problem and then I tried the Joachim Jablon code, and although this seemed to work well, it still had problems. I get to the point, the longest version is on my blog .

• SELECT '{"a":1,"b":2}'::json = '{"b":2,"a":1}'::json returned false because it is based on a string representation.
• Sorting fields is not allowed because the hash operator class is instead of btree .

Then I created the json_cmp() function in PL / V8, which can be used to power the operators needed for btree.

Here is the full SQL script

 CREATE OR REPLACE FUNCTION json_cmp(left json, right json) RETURNS integer AS $$ function cleverType(obj) { var type = typeof obj; if (type === 'object') { if (obj === null) { type = 'null'; } else if (obj instanceof Array) { type = 'array'; } } return type; } function cmp(left, right) { var leftType = cleverType(left), rightType = cleverType(right), i, buf, leftKeys, rightKeys, output = 0; if (leftType !== rightType) { output = leftType.localeCompare(rightType); } else if (leftType === 'number' || leftType === 'boolean' || leftType === 'string') { if (left < right) { output = -1; } else if (left > right) { output = 1; } else { output = 0; } } else if (leftType === 'array') { if (left.length !== right.length) { output = cmp(left.length, right.length); } else { for (i = 0; i < left.length; i += 1) { buf = cmp(left[i], right[i]); if (buf !== 0) { output = buf; break; } } } } else if (leftType === 'object') { leftKeys = Object.keys(left); rightKeys = Object.keys(right); if (leftKeys.length !== rightKeys.length) { leftKeys.sort(); rightKeys.sort(); buf = cmp(leftKeys, rightKeys); } else { buf = cmp(leftKeys.length, rightKeys.length); } if (buf !== 0) { output = buf; } else { for (i = 0; i < leftKeys.length; i += 1) { buf = cmp(left[leftKeys[i]], right[leftKeys[i]]); if (buf !== 0) { output = buf; break; } } } } return output; } return cmp(left, right); $$ LANGUAGE plv8 IMMUTABLE STRICT; CREATE OR REPLACE FUNCTION json_eq(json, json) RETURNS BOOLEAN LANGUAGE SQL STRICT IMMUTABLE AS $$ SELECT json_cmp($1, $2) = 0; $$; CREATE OR REPLACE FUNCTION json_lt(json, json) RETURNS BOOLEAN LANGUAGE SQL STRICT IMMUTABLE AS $$ SELECT json_cmp($1, $2) < 0; $$; CREATE OR REPLACE FUNCTION json_lte(json, json) RETURNS BOOLEAN LANGUAGE SQL STRICT IMMUTABLE AS $$ SELECT json_cmp($1, $2) <= 0; $$; CREATE OR REPLACE FUNCTION json_gt(json, json) RETURNS BOOLEAN LANGUAGE SQL STRICT IMMUTABLE AS $$ SELECT json_cmp($1, $2) > 0; $$; CREATE OR REPLACE FUNCTION json_gte(json, json) RETURNS BOOLEAN LANGUAGE SQL STRICT IMMUTABLE AS $$ SELECT json_cmp($1, $2) >= 0; $$; CREATE OPERATOR = (LEFTARG = json, RIGHTARG = json, PROCEDURE = json_eq); CREATE OPERATOR < (LEFTARG = json, RIGHTARG = json, PROCEDURE = json_lt); CREATE OPERATOR <= (LEFTARG = json, RIGHTARG = json, PROCEDURE = json_lte); CREATE OPERATOR > (LEFTARG = json, RIGHTARG = json, PROCEDURE = json_gt); CREATE OPERATOR >= (LEFTARG = json, RIGHTARG = json, PROCEDURE = json_gte); CREATE OPERATOR CLASS json_ops DEFAULT FOR TYPE json USING btree AS OPERATOR 1 <, OPERATOR 2 <=, OPERATOR 3 =, OPERATOR 4 >=, OPERATOR 5 >, FUNCTION 1 json_cmp(json, json); 

This is usually much slower than a simple string comparison, of course, but has the advantage of creating more reliable results.

Note that if you use the South for your migrations, you can create an empty migration and execute SQL from the forwards() method. This will automatically install features when porting your application.

Answer 3

My solution uses PL / Python, it parses and deletes json, sorting the keys, and then gives the hash result FNV1a of the result: https://github.com/ifad/chronomodel/blob/master/sql/json_ops.sql .

I do not use hashtext () because it is intended for internal use only: http://www.postgresql.org/message-id/ [e-mail] .

This is not a silver bullet, just a rough handle. The real solution is to wait for full support in Postgres.