Opened 3 years ago

Closed 7 months 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: 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 3 years ago.

Download all attachments as: .zip

Change History (16)

Changed 3 years ago by Dmitry Dygalo

Attachment: json_null.patch added

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

Owner: set to Dmitry Dygalo
Status: newassigned

comment:5 Changed 3 years ago by Dmitry Dygalo

Version: 1.8master

comment:6 Changed 3 years ago by Tim Graham

Patch needs improvement: set

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

comment:7 Changed 19 months ago by Tim Graham

Patch needs improvement: unset

comment:8 Changed 16 months ago by Tim Graham

Patch needs improvement: set

comment:9 Changed 9 months ago by Tim Graham

Patch needs improvement: unset

comment:10 Changed 8 months ago by Carlton Gibson

Patch needs improvement: set

comment:11 Changed 8 months ago by Carlton Gibson

Patch needs improvement: unset
Triage Stage: AcceptedReady for checkin

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

comment:12 Changed 8 months ago by Carlton Gibson

Patch needs improvement: set
Triage Stage: Ready for checkinAccepted

comment:13 Changed 8 months ago by Carlton Gibson

Patch needs improvement: unset
Triage Stage: AcceptedReady 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 Changed 8 months ago by Tim Graham

Patch needs improvement: set
Triage Stage: Ready for checkinAccepted

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

comment:15 Changed 7 months ago by Tim Graham <timograham@…>

Resolution: fixed
Status: assignedclosed

In c979c0a:

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

Note: See TracTickets for help on using tickets.
Back to Top