Opened 9 years ago
Closed 7 years ago
#25718 closed Bug (fixed)
Can’t use queries with JSON ’null’ values with JSONField
Reported by: | Dmitry Dygalo | Owned by: | Dmitry Dygalo |
---|---|---|---|
Component: | contrib.postgres | Version: | dev |
Severity: | Normal | Keywords: | |
Cc: | Simon Charette | Triage Stage: | Accepted |
Has patch: | yes | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | yes |
Easy pickings: | no | UI/UX: | no |
Description
For regular fields it can be done with is_null
lookup, but with JSON field it is not an option.
Example setup.
from django.tests.postgres_tests.models import JSONModel JSONModel.objects.create(field={‘key’: None}) JSONModel.objects.create(field={‘key’: 1})
Now lets get all objects where key is None:
>>> JSONModel.objects.filter(field__key__isnull=True)
Will fail with:
ProgrammingError: operator does not exist: jsonb -> boolean LINE 1: ...onmodel" WHERE "postgres_tests_jsonmodel"."field" -> ‘key' IS ... ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
Generated SQL:
SELECT "postgres_tests_jsonmodel"."id", "postgres_tests_jsonmodel"."field" FROM "postgres_tests_jsonmodel" WHERE "postgres_tests_jsonmodel"."field" -> ‘key' IS NULL;
Which is obviously incorrect.
Next try:
>>> JSONModel.objects.filter(field__key=None)
Will fail with:
ValueError: Cannot use None as a query value at django.db.models.sql.query, line 985, in prepare_lookup_value
Will also fail:
JSONModel.objects.filter(field__key='null’)
Correct query should be:
SELECT "postgres_tests_jsonmodel"."id", "postgres_tests_jsonmodel"."field" FROM "postgres_tests_jsonmodel" WHERE "postgres_tests_jsonmodel"."field" -> ‘key' = 'null';
In my patch JSONField uses slightly modified lookups. Any thoughts? Is it a good approach to solve this problem?
Attachments (1)
Change History (16)
by , 9 years ago
Attachment: | json_null.patch added |
---|
comment:2 by , 9 years ago
Cc: | added |
---|---|
Triage Stage: | Unreviewed → Accepted |
Hi Stranger6667, thanks for the report and patch!
At a first glance you proposed changes make sense.
Please create a PR out of this branch and link back to it here as it will give it more visibility and allows running CI against the iterations of your patch.
Once this is done, detailing why this requires changing code outside the django.contrib.postgres
package would also help future reviewers.
Accepting based on the detailed report and the fact that we need an API to query null
values.
comment:3 by , 9 years ago
Owner: | set to |
---|---|
Status: | new → assigned |
comment:5 by , 9 years ago
Version: | 1.8 → master |
---|
comment:6 by , 9 years ago
Patch needs improvement: | set |
---|
Marking "Patch needs improvement" given Marc's feedback on the pull request.
comment:8 by , 7 years ago
Patch needs improvement: | set |
---|
comment:9 by , 7 years ago
Patch needs improvement: | unset |
---|
comment:10 by , 7 years ago
Patch needs improvement: | set |
---|
comment:11 by , 7 years ago
Patch needs improvement: | unset |
---|---|
Triage Stage: | Accepted → Ready for checkin |
For me, I think this is now good to go. (Not 100% sure about versionchanged
vs versionadded
here...)
comment:12 by , 7 years ago
Patch needs improvement: | set |
---|---|
Triage Stage: | Ready for checkin → Accepted |
comment:13 by , 7 years ago
Patch needs improvement: | unset |
---|---|
Triage Stage: | Accepted → Ready for checkin |
Release notes and docs have been updated to reflect changes. (Including BC change if you were using =None
to query for __isnull
.) Should be good to go.
comment:14 by , 7 years ago
Patch needs improvement: | set |
---|---|
Triage Stage: | Ready for checkin → Accepted |
Looking good, but I think HStoreField
should have the same behavior.
Changes are here: https://github.com/django/django/compare/master...Stranger6667:fix-json-isnull-lookups?expand=1