Opened 8 years ago

Closed 6 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)

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

Download all attachments as: .zip

Change History (16)

by Dmitry Dygalo, 8 years ago

Attachment: json_null.patch added

comment:2 by Simon Charette, 8 years ago

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 by Dmitry Dygalo, 8 years ago

Owner: set to Dmitry Dygalo
Status: newassigned

comment:5 by Dmitry Dygalo, 8 years ago

Version: 1.8master

comment:6 by Tim Graham, 8 years ago

Patch needs improvement: set

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

comment:7 by Tim Graham, 7 years ago

Patch needs improvement: unset

comment:8 by Tim Graham, 7 years ago

Patch needs improvement: set

comment:9 by Tim Graham, 6 years ago

Patch needs improvement: unset

comment:10 by Carlton Gibson, 6 years ago

Patch needs improvement: set

comment:11 by Carlton Gibson, 6 years ago

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 by Carlton Gibson, 6 years ago

Patch needs improvement: set
Triage Stage: Ready for checkinAccepted

comment:13 by Carlton Gibson, 6 years ago

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 by Tim Graham, 6 years ago

Patch needs improvement: set
Triage Stage: Ready for checkinAccepted

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

comment:15 by Tim Graham <timograham@…>, 6 years ago

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