Opened 2 years ago

Last modified 7 months ago

#25718 assigned Bug

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

Reported by: Dmitry Dygalo Owned by: Dmitry Dygalo
Component: contrib.postgres Version: master
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)

json_null.patch (4.9 KB) - added by Dmitry Dygalo 2 years ago.

Download all attachments as: .zip

Change History (9)

Changed 2 years ago by Dmitry Dygalo

Attachment: json_null.patch added

comment:2 Changed 2 years ago by Simon Charette

Cc: Simon Charette added
Triage Stage: UnreviewedAccepted

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 Changed 2 years ago by Dmitry Dygalo

Owner: set to Dmitry Dygalo
Status: newassigned

comment:5 Changed 2 years ago by Dmitry Dygalo

Version: 1.8master

comment:6 Changed 2 years ago by Tim Graham

Patch needs improvement: set

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

comment:7 Changed 10 months ago by Tim Graham

Patch needs improvement: unset

comment:8 Changed 7 months ago by Tim Graham

Patch needs improvement: set
Note: See TracTickets for help on using tickets.
Back to Top