id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
25718	Can’t use queries with JSON ’null’ values with JSONField	Dmitry Dygalo	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?"	Bug	closed	contrib.postgres	dev	Normal	fixed		Simon Charette	Accepted	1	0	0	1	0	0