DBError: could not identify an equality operator for type json when annotating a model with JSONField

[neara]

I'm working in Django 1.5.4 with PostgreSQL 9.3, and i get this error on a query like this:

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

Traceback:

Traceback (most recent call last):
  File "<console>", line 1, in <module>
  File "/Users/jondoe/Project/lib/python2.7/site-packages/django/db/models/query.py", line 93, in __repr__
    data = list(self[:REPR_OUTPUT_SIZE + 1])
  File "/Users/jondoe/Project/lib/python2.7/site-packages/django/db/models/query.py", line 108, in __len__
    self._result_cache.extend(self._iter)
  File "/Users/jondoe/Project/lib/python2.7/site-packages/django/db/models/query.py", line 317, in iterator
    for row in compiler.results_iter():
  File "/Users/jondoe/Project/lib/python2.7/site-packages/django/db/models/sql/compiler.py", line 775, in results_iter
    for rows in self.execute_sql(MULTI):
  File "/Users/jondoe/Project/lib/python2.7/site-packages/django/db/models/sql/compiler.py", line 840, in execute_sql
    cursor.execute(sql, params)
  File "/Users/jondoe/Project/lib/python2.7/site-packages/django/db/backends/util.py", line 41, in execute
    return self.cursor.execute(sql, params)
  File "/Users/jondoe/Project/lib/python2.7/site-packages/django/db/backends/postgresql_psycopg2/base.py", line 58, in execute
    six.reraise(utils.DatabaseError, utils.DatabaseError(*tuple(e.args)), sys.exc_info()[2])
  File "/Users/jondoe/Project/lib/python2.7/site-packages/django/db/backends/postgresql_psycopg2/base.py", line 54, in execute
    return self.cursor.execute(query, args)
DatabaseError: could not identify an equality operator for type json

Ideas? I saw another issue with similar error, going to try and find out what causes this.

[ewjoachim]

Oh, I might actually have created a duplicate in #57 . It seems to be linked to the use of SELECT DISTINCT

[dmpayton]

I'm also running into this. Here's a more explicit example.

models.py:

from django.db import models
from jsonfield.fields import JSONField


class Species(models.Model):
    common_name = models.CharField(max_length=100)
    scientific_name = models.CharField(max_length=100)
    taxonomy = JSONField()


class Reptile(models.Model):
    name = models.CharField(max_length=100)
    species = models.ForeignKey(Species, related_name='reptiles')

Here's the query that breaks, and the traceback:

>>> Species.objects.annotate(Count('reptiles'))
Traceback (most recent call last):
  File "<console>", line 1, in <module>
  File "/home/derek/.virtualenvs/snaaakes/local/lib/python2.7/site-packages/django/db/models/query.py", line 93, in __repr__
    data = list(self[:REPR_OUTPUT_SIZE + 1])
  File "/home/derek/.virtualenvs/snaaakes/local/lib/python2.7/site-packages/django/db/models/query.py", line 108, in __len__
    self._result_cache.extend(self._iter)
  File "/home/derek/.virtualenvs/snaaakes/local/lib/python2.7/site-packages/django/db/models/query.py", line 317, in iterator
    for row in compiler.results_iter():
  File "/home/derek/.virtualenvs/snaaakes/local/lib/python2.7/site-packages/django/db/models/sql/compiler.py", line 775, in results_iter
    for rows in self.execute_sql(MULTI):
  File "/home/derek/.virtualenvs/snaaakes/local/lib/python2.7/site-packages/django/db/models/sql/compiler.py", line 846, in execute_sql
    cursor.execute(sql, params)
  File "/home/derek/.virtualenvs/snaaakes/local/lib/python2.7/site-packages/django/db/backends/util.py", line 41, in execute
    return self.cursor.execute(sql, params)
  File "/home/derek/.virtualenvs/snaaakes/local/lib/python2.7/site-packages/django/db/backends/postgresql_psycopg2/base.py", line 58, in execute
    six.reraise(utils.DatabaseError, utils.DatabaseError(*tuple(e.args)), sys.exc_info()[2])
  File "/home/derek/.virtualenvs/snaaakes/local/lib/python2.7/site-packages/django/db/backends/postgresql_psycopg2/base.py", line 54, in execute
    return self.cursor.execute(query, args)
DatabaseError: could not identify an equality operator for type json
LINE 1: ...ommon_name", "animals_species"."scientific_name", "animals_s...
                                                             ^

Here's the query created for Postgres:

>>> Species.objects.annotate(Count('reptiles')).query.sql_with_params()
(u'SELECT "animals_species"."id", "animals_species"."common_name", "animals_species"."scientific_name", "animals_species"."taxonomy", COUNT("animals_reptile"."id") AS "reptiles__count" FROM "animals_species" LEFT OUTER JOIN "animals_reptile" ON ("animals_species"."id" = "animals_reptile"."species_id") GROUP BY "animals_species"."id", "animals_species"."common_name", "animals_species"."scientific_name", "animals_species"."taxonomy"', ())

If you cast the JSON field in the GROUP BY clause to text, no exception is raised.

SELECT
    "animals_species"."id",
    "animals_species"."common_name",
    "animals_species"."scientific_name",
    "animals_species"."taxonomy",
    COUNT("animals_reptile"."id") AS "reptiles__count"
FROM "animals_species"
LEFT OUTER JOIN "animals_reptile" ON ("animals_species"."id" = "animals_reptile"."species_id")
GROUP BY
    "animals_species"."id",
    "animals_species"."common_name",
    "animals_species"."scientific_name",
    "animals_species"."taxonomy"::text

Not sure what the fix is, but hopefully that provides more information.

[devangmundhra]

I am getting the same error as above and it gets fixed too by casting the JSON field to text.

[maxpeterson]

Hopefully @mjtamlyn will fix this issue with his kickstarter project

The issue seems to be that postgres does not provide a equality operator for json. I am not sure it is clear what equality should be for json. Is {"a": 1, "b":2} equal to {"b": 2, "a":1}?

Maybe the json field can be cast to ::text in the backend (django.db.backends.postgresql_psycopg2).

[mjtamlyn]

Checking whether two JSON blobs are equal is a non trivial exercise (to do it properly, it most likely involves sorting all keys in all nested objects and doing a text comparison. As a result, postgres does not implement an equality operator by design.

Our options therefore are somewhat limited. A __exact lookup does not work for JSON fields, so you need to always look them up using a different method - based on the value of a given key, based on the keys in the object or similar. These queries are generally going to be slow unless they are explicity indexed.

In short, JSON is a bad datatype in postgres to query on - it's very underpowered. It's designed for unstructured, additional metadata, not for critical querying data. In a reasonable number of cases, you're better off working with hstore than with json (which does have an equality operator).

[ewjoachim]

Yes, but then, one needs to tell django not to use this field for the kind of query that needs comparisons, and it's also not-trivial. Except if it is, but I don't think we have a simpler solution yet.

[mjtamlyn]

Yup, that'll be my problem to solve

[thekashifmalik]

Looking at the generated SQL this problem arises due to the JSON field being present in the ORDER BY clause when doing any sort of aggregation.

PostgreSQL seems to not mind if you remove JSON types from the ORDER BY clause but leave them in the SELECT.

[bradjasper]

Just pushed django-jsonfield 1.0.0 which makes the breaking change of dropping the native JSON data type from PostgreSQL. This should resolve the short-term errors, and in the long-term we can evaluate adding it back as better support emerges.