#25718: Can’t use queries with JSON ’null’ values with JSONField
----------------------------------+-----------------
Reporter: Stranger6667 | Owner:
Type: Bug | Status: new
Component: contrib.postgres | Version: 1.8
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 1
Easy pickings: 0 | UI/UX: 0
----------------------------------+-----------------
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?
--
Ticket URL: <https://code.djangoproject.com/ticket/25718>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/055.4c16886dbd11d9593ec022c6d8a08f2b%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
