New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Closed
neara opened this Issue Oct 1, 2013 · 9 comments

Comments

Projects
None yet
8 participants
@neara

neara commented Oct 1, 2013

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

This comment has been minimized.

Show comment
Hide comment
@ewjoachim

ewjoachim Oct 23, 2013

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

ewjoachim commented Oct 23, 2013

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

@dmpayton

This comment has been minimized.

Show comment
Hide comment
@dmpayton

dmpayton Nov 17, 2013

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.

dmpayton commented Nov 17, 2013

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

This comment has been minimized.

Show comment
Hide comment
@devangmundhra

devangmundhra Mar 2, 2014

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

devangmundhra commented Mar 2, 2014

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

@maxpeterson

This comment has been minimized.

Show comment
Hide comment
@maxpeterson

maxpeterson Mar 2, 2014

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

maxpeterson commented Mar 2, 2014

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

This comment has been minimized.

Show comment
Hide comment
@mjtamlyn

mjtamlyn Mar 6, 2014

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

mjtamlyn commented Mar 6, 2014

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

This comment has been minimized.

Show comment
Hide comment
@ewjoachim

ewjoachim Mar 6, 2014

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.

ewjoachim commented Mar 6, 2014

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

This comment has been minimized.

Show comment
Hide comment
@mjtamlyn

mjtamlyn Mar 6, 2014

Yup, that'll be my problem to solve

mjtamlyn commented Mar 6, 2014

Yup, that'll be my problem to solve

@thekashifmalik

This comment has been minimized.

Show comment
Hide comment
@thekashifmalik

thekashifmalik Mar 14, 2014

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.

thekashifmalik commented Mar 14, 2014

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

This comment has been minimized.

Show comment
Hide comment
@bradjasper

bradjasper Sep 4, 2014

Collaborator

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.

Collaborator

bradjasper commented Sep 4, 2014

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.

@bradjasper bradjasper closed this Sep 4, 2014

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment