Can’t use queries with JSON ’null’ values with JSONField

[Dmitry Dygalo]

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?

[Dmitry Dygalo]

Changes is here: ​https://github.com/django/django/compare/master...Stranger6667:fix-json-isnull-lookups?expand=1

[Simon Charette]

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.

[Dmitry Dygalo]

PR: ​https://github.com/django/django/pull/5617

[Tim Graham]

Marking "Patch needs improvement" given Marc's feedback on the pull request.

[Tim Graham]

​PR

[Carlton Gibson]

For me, I think this is now good to go. (Not 100% sure about versionchanged vs versionadded here...)

[Carlton Gibson]

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.

[Tim Graham]

Looking good, but I think HStoreField should have the same behavior.

[Tim Graham <timograham@…>]

In c979c0a:

Fixed #25718 -- Made a JSONField lookup value of None match keys that have a null value.