search

djangonauts / django-pgjson

PostgreSQL json field support for Django
http://djangonauts.github.io/django-pgjson/
Other
131 stars 39 forks source link

isnull filtering on JSON values doesn't work #12

Closed lanzz closed 9 years ago

lanzz commented 9 years ago 
>>> Model.objects.filter(jsonfield__at_key__isnull=False)
<repr(<djorm_pgfulltext.models.SearchQuerySet at 0x1119368d0>) failed: django.db.utils.ProgrammingError: operator does not exist: json ->> boolean
LINE 1: ... "app_model" WHERE ("app_model"."jsonfield"->>'key' ...

I'm expecting this filter to return records that have a non-null value in the jsonfield->>'key' field. The generated SQL is however wrong:

SELECT ... FROM "app_model" WHERE ("app_model"."jsonfield"->>'key' IS NOT NULL)

According to the PostgreSQL operator precedence table, the ->> operator has a lower precedence than the IS operator, so the above condition is actually parsed as "jsonfield"->>('key' IS NOT NULL), while it should be ("jsonfield"->>'key') IS NOT NULL.

lanzz commented 9 years ago

Apparently other lookup types are also subtly broken:

Model.objects.filter(jsonfield__at_key__in=['foo', 'bar'])

generates this SQL:

SELECT ... FROM "app_model" WHERE "app_model"."jsonfield"->>'key' IN ('"foo"', '"bar"')

As you can see, the values being matched are JSON-encoded (there are extra quotes added within the strings), so that condition will match if jsonfield->>'key' is "foo" (a string of five characters, including the quotes), but will not match if it is just foo (a string of three characters).

lanzz commented 9 years ago

Two pull requests for both issues described here: https://github.com/djangonauts/django-pgjson/pull/13 https://github.com/djangonauts/django-pgjson/pull/14

niwinz commented 9 years ago

Merged and released 0.2.3 ;) thanks!