Fixed #36689 -- Fixed top-level JSONField __in lookup failures on MySQL and Oracle. #20009

cliffordgama · 2025-10-27T20:42:46Z

Added a JSONIn lookup to handle correct serialization and extraction for JSONField __in queries on backends without native JSON support. KeyTransformIn now subclasses JSONIn.

Thanks @jacobtylerwalls for the report.

Trac ticket number

ticket-36689

Not sure if a release note is required.

Checklist

This PR targets the main branch.
The commit message is written in past tense, mentions the ticket number, and ends with a period.
I have checked the "Has patch" ticket flag in the Trac system.
I have added or updated relevant tests.
I have added or updated relevant docs, including release notes if applicable.
I have attached screenshots in both light and dark modes for any UI changes.

jacobtylerwalls · 2025-10-30T12:15:28Z

buildbot, test on oracle.

jacobtylerwalls

Thanks @cliffordgama for the push 👍

django/db/models/fields/json.py

jacobtylerwalls · 2025-10-30T12:46:14Z

django/db/models/fields/json.py

+                template = (
+                    "COALESCE("
+                    "JSON_VALUE(%s, '$'),"
+                    "JSON_QUERY(%s, '$' DISALLOW SCALARS)"
+                    ")"


This looks subtly different from the logic in KeyTransform.as_oracle. Can you share more about how you derived this code, or why we wouldn't factor out the duplication?

I factored out the duplication. LMKWYT!

(The code was derived taking inspiration from KeyTransform.as_{vendor} methods, replacing JSON paths with the root path for JSON Cols.)

cliffordgama · 2025-10-30T19:53:12Z

django/db/backends/oracle/features.py

+            "Oracle doesn't support JSON null scalar extraction for IN queries": {
+                "model_fields.test_jsonfield.JSONNullTests.test_filter_in"
+            },


@jacobtylerwalls you were right about there being no way to extract a JSON scalar null value in Oracle. Oracle's JSON/SQL functions will convert it to SQL NULL. The way key transform exact handles this is by changing __key__exact=JSONNull() to "does the JSON value have the key and is the key NULL. https://github.com/django/django/blob/main/django/db/models/fields/json.py#L594-L597

Perhaps a potential hack in this case would be to rewrite the IN clause as an OR chain, for example replacing
IN (v1, v2, …) with
JSON_EQUAL(..., v1) OR JSON_EQUAL(..., v2) OR ... although I'm not sure if it's worth it or not.

Cool. I'll sit with this, but at a glance yeah a skip looks better than a feature flag for this.

rewrite the IN clause as an OR chain

That seems akin to what we did with composite pk's but I agree would be pretty overkill for a niche use case. Happy to skip as you suggest here.

So I sat with this and managed to get something basically working:

diff --git a/django/db/backends/oracle/features.py b/django/db/backends/oracle/features.py index a1bbe24fe5..c07d9f1ed0 100644 --- a/django/db/backends/oracle/features.py +++ b/django/db/backends/oracle/features.py @@ -127,9 +127,6 @@ class DatabaseFeatures(BaseDatabaseFeatures): "Oracle doesn't support casting filters to NUMBER.": { "lookup.tests.LookupQueryingTests.test_aggregate_combined_lookup", }, - "Oracle doesn't support JSON null scalar extraction.": { - "model_fields.test_jsonfield.JSONNullTests.test_filter_in", - }, } if self.connection.oracle_version < (23,): skips.update( diff --git a/django/db/models/fields/json.py b/django/db/models/fields/json.py index 5f0abb3f2e..b901cc4086 100644 --- a/django/db/models/fields/json.py +++ b/django/db/models/fields/json.py @@ -6,6 +6,7 @@ from django.core import checks, exceptions from django.db import NotSupportedError, connections, router from django.db.models import expressions, lookups from django.db.models.constants import LOOKUP_SEP +from django.db.models.expressions import ExpressionList from django.db.models.fields import TextField from django.db.models.lookups import ( FieldGetDbPrepValueMixin, @@ -459,6 +460,27 @@ class JSONIn(ProcessJSONLHSMixin, lookups.In): return self._process_as_sqlite(sql, params, connection) return sql, params + def as_oracle(self, compiler, connection): + if ( + connection.features.supports_primitives_in_json_field + and isinstance(self.rhs, ExpressionList) + and JSONNull() in self.rhs.get_source_expressions() + ): + self.rhs.set_source_expressions( + [val for val in self.rhs.get_source_expressions() if val != JSONNull()] + ) + lhs_sql, lhs_params = self.process_lhs(compiler, connection) + is_null_sql = f"JSON_VALUE({lhs_sql}, '$') IS NULL" + try: + sql, params = super().as_sql(compiler, connection) + except exceptions.EmptyResultSet: + sql, params = is_null_sql, lhs_params + else: + sql += f" OR {is_null_sql}" + params = (*lhs_params, *params) + return sql, params + return self.as_sql(compiler, connection) + JSONField.register_lookup(DataContains) JSONField.register_lookup(ContainedBy) diff --git a/tests/model_fields/test_jsonfield.py b/tests/model_fields/test_jsonfield.py index bb69955e56..0d7eae0716 100644 --- a/tests/model_fields/test_jsonfield.py +++ b/tests/model_fields/test_jsonfield.py @@ -1309,6 +1309,11 @@ class JSONNullTests(TestCase): NullableJSONModel.objects.filter(value__in=[JSONNull()]), [obj], ) + obj2 = NullableJSONModel.objects.create() + self.assertCountEqual( + NullableJSONModel.objects.filter(value__in=[JSONNull(), None]), + [obj, obj2], + ) def test_bulk_update(self): obj1 = NullableJSONModel.objects.create(value={"k": "1st"})

Would you be able to have a look and let me know what you think? I know you mentioned not being able to pull the docker image, so if it's too much trouble just say so. 👍

Thanks @jacobtylerwalls! This is better than an OR chain.

There is, however, currently one issue with this. Since all direct values are wrapped in expressions, e.g. Value({}, output_field()), for them to be source expressions of ExpressionList, it means that these values won't be serialized by JSONIn.resolve_expression_parameter. As such, I was expecting the following (note the the extra filter value for obj3) to fail with

django.db.utils.DatabaseError: ORA-22848: cannot use NCLOB type as comparison key
Help: https://docs.oracle.com/error-help/db/ora-22848/

def test_filter_in(self): obj = NullableJSONModel.objects.create(value=JSONNull()) self.assertSequenceEqual( NullableJSONModel.objects.filter(value__in=[JSONNull()]), [obj], ) obj2 = NullableJSONModel.objects.create() obj3 = NullableJSONModel.objects.create(value={"k": "v"}) self.assertCountEqual( NullableJSONModel.objects.filter(value__in=[JSONNull(), None, {"k": "v"}]), [obj, obj2, obj3], )

However the error I am getting is not expected.

ERROR: test_filter_in (model_fields.test_jsonfield.JSONNullTests.test_filter_in) ---------------------------------------------------------------------- Traceback (most recent call last): File "/django/source/tests/model_fields/test_jsonfield.py", line 1314, in test_filter_in self.assertCountEqual( File "/usr/local/lib/python3.12/unittest/case.py", line 1198, in assertCountEqual first_seq, second_seq = list(first), list(second) ^^^^^^^^^^^ File "/django/source/django/db/models/query.py", line 406, in __iter__ self._fetch_all() File "/django/source/django/db/models/query.py", line 2088, in _fetch_all self._result_cache = list(self._iterable_class(self)) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/django/source/django/db/models/query.py", line 96, in __iter__ results = compiler.execute_sql( ^^^^^^^^^^^^^^^^^^^^^ File "/django/source/django/db/models/sql/compiler.py", line 1623, in execute_sql cursor.execute(sql, params) File "/django/source/django/db/backends/utils.py", line 79, in execute return self._execute_with_wrappers( ^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/django/source/django/db/backends/utils.py", line 92, in _execute_with_wrappers return executor(sql, params, many, context) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/django/source/django/db/backends/utils.py", line 105, in _execute return self.cursor.execute(sql, params) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/django/source/django/db/backends/oracle/base.py", line 625, in execute query, params = self._fix_for_params(query, params, unify_by_values=True) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File "/django/source/django/db/backends/oracle/base.py", line 611, in _fix_for_params for i, param_type in enumerate(dict.fromkeys(param_types)) ^^^^^^^^^^^^^^^^^^^^^^^^^^ TypeError: unhashable type: 'dict'
Ran 1 test in 0.206s

The same unexpected error happens on main on __key__in if a Value instance with value=list|dict is included in the list of RHS values:

diff --git a/tests/model_fields/test_jsonfield.py b/tests/model_fields/test_jsonfield.py index b16499d198..698a238d1c 100644 --- a/tests/model_fields/test_jsonfield.py +++ b/tests/model_fields/test_jsonfield.py @@ -1016,6 +1016,11 @@ class TestQuerying(TestCase): ("value__bar__in", [["foo", "bar"]], [self.objs[7]]), ("value__bar__in", [["foo", "bar"], ["a"]], [self.objs[7]]), ("value__bax__in", [{"foo": "bar"}, {"a": "b"}], [self.objs[7]]), + ( + "value__bax__in", + [Value({"foo": "bar"}, JSONField()), {"a": "b"}], + [self.objs[7]], + ), ("value__h__in", [True, "foo"], [self.objs[4]]), ("value__i__in", [False, "foo"], [self.objs[4]]), ]

Maybe we have a separate bug? 🤔

It also fails on sqlite3

ERROR: test_key_in (model_fields.test_jsonfield.TestQuerying.test_key_in) [] (lookup='value__bax__in', value=[Value({'foo': 'bar'}), {'a': 'b'}]) ---------------------------------------------------------------------- Traceback (most recent call last): File "/home/clifford/code/django/django/db/backends/utils.py", line 105, in _execute return self.cursor.execute(sql, params) ^^^^^^^ File "/home/clifford/code/django/django/db/backends/sqlite3/base.py", line 359, in execute return super().execute(query, params) ^^^^^^^^^^^^^^^ sqlite3.ProgrammingError: Error binding parameter 5: type 'dict' is not supported The above exception was the direct cause of the following exception: Traceback (most recent call last): File "/home/clifford/.pyenv/versions/3.14.0/lib/python3.14/unittest/case.py", line 58, in testPartExecutor yield File "/home/clifford/.pyenv/versions/3.14.0/lib/python3.14/unittest/case.py", line 565, in subTest yield File "/home/clifford/code/django/tests/model_fields/test_jsonfield.py", line 1029, in test_key_in self.assertCountEqual( ^^^^^^^ File "/home/clifford/.pyenv/versions/3.14.0/lib/python3.14/unittest/case.py", line 1238, in assertCountEqual first_seq, second_seq = list(first), list(second) ^^^^^^^^^^^^^^^ File "/home/clifford/code/django/django/db/models/query.py", line 406, in iter self._fetch_all() ^^^^^^^^^^^^^^^ File "/home/clifford/code/django/django/db/models/query.py", line 2086, in _fetch_all self._result_cache = list(self._iterable_class(self)) ^^^^^^^^^^^ File "/home/clifford/code/django/django/db/models/query.py", line 96, in iter results = compiler.execute_sql( ^^^^^^^^^^^^^^^ File "/home/clifford/code/django/django/db/models/sql/compiler.py", line 1623, in execute_sql cursor.execute(sql, params) ^^^^^^^^^^^ File "/home/clifford/code/django/django/db/backends/utils.py", line 79, in execute return self._execute_with_wrappers( ^^^^^^^^^^^^^^^ File "/home/clifford/code/django/django/db/backends/utils.py", line 92, in _execute_with_wrappers return executor(sql, params, many, context) ^^^^^^^^^^^^^^^ File "/home/clifford/code/django/django/db/backends/utils.py", line 100, in _execute with self.db.wrap_database_errors: ^^^^^^^^^^^^^^^ File "/home/clifford/code/django/django/db/utils.py", line 94, in exit raise dj_exc_value.with_traceback(traceback) from exc_value ^^^^^^^ File "/home/clifford/code/django/django/db/backends/utils.py", line 105, in _execute return self.cursor.execute(sql, params) ^^^^^^^ File "/home/clifford/code/django/django/db/backends/sqlite3/base.py", line 359, in execute return super().execute(query, params) ^^^^^^^^^^^^^^^ django.db.utils.ProgrammingError: Error binding parameter 5: type 'dict' is not supported Ran 102 tests in 2.353s

I also struggled with that, and that explains why I played with __in=[JSONNull(), None] -- all I wanted was a second parameter, and None was the easiest thing to provide. (But now I see an issue with providing None there is that the behavior isn't the same on all backends, so let's remove it.) I think it would be better to use JSONObject(k=Value("v") for now.

If you think there is a separate issue here where we can improve handling of list/dict params we should probably get a new ticket triaged for it and evaluate it on each backend.

I also think I'm missing a clone = self.rhs.copy() before mutating the source expressions.

I do see this now with JSONObject:

django.db.utils.DatabaseError: ORA-22848: cannot use NCLOB type as comparison key
Help: https://docs.oracle.com/error-help/db/ora-22848/

Do you have an idea about this?

It also fails on postgres

ERROR: test_key_in (model_fields.test_jsonfield.TestQuerying.test_key_in) [] (lookup='value__bax__in', value=[Value({'foo': 'bar'}), {'a': 'b'}]) ---------------------------------------------------------------------- Traceback (most recent call last): File "/home/clifford/code/django/django/db/backends/utils.py", line 105, in _execute return self.cursor.execute(sql, params) ^^^^^^^ File "/home/clifford/.pyenv/versions/dj/lib/python3.14/site-packages/psycopg/cursor.py", line 97, in execute raise ex.with_traceback(None) ^^^^^^^^^^^ psycopg.ProgrammingError: cannot adapt type 'dict' using placeholder '%t' (format: TEXT) The above exception was the direct cause of the following exception: Traceback (most recent call last): File "/home/clifford/.pyenv/versions/3.14.0/lib/python3.14/unittest/case.py", line 58, in testPartExecutor yield File "/home/clifford/.pyenv/versions/3.14.0/lib/python3.14/unittest/case.py", line 565, in subTest yield File "/home/clifford/code/django/tests/model_fields/test_jsonfield.py", line 1029, in test_key_in self.assertCountEqual( ^^^^^^^ File "/home/clifford/.pyenv/versions/3.14.0/lib/python3.14/unittest/case.py", line 1238, in assertCountEqual first_seq, second_seq = list(first), list(second) ^^^^^^^^^^^^^^^ File "/home/clifford/code/django/django/db/models/query.py", line 406, in iter self._fetch_all() ^^^^^^^^^^^^^^^ File "/home/clifford/code/django/django/db/models/query.py", line 2086, in _fetch_all self._result_cache = list(self._iterable_class(self)) ^^^^^^^^^^^ File "/home/clifford/code/django/django/db/models/query.py", line 96, in iter results = compiler.execute_sql( ^^^^^^^^^^^^^^^ File "/home/clifford/code/django/django/db/models/sql/compiler.py", line 1623, in execute_sql cursor.execute(sql, params) ^^^^^^^^^^^ File "/home/clifford/code/django/django/db/backends/utils.py", line 79, in execute return self._execute_with_wrappers( ^^^^^^^^^^^^^^^ File "/home/clifford/code/django/django/db/backends/utils.py", line 92, in _execute_with_wrappers return executor(sql, params, many, context) ^^^^^^^^^^^^^^^ File "/home/clifford/code/django/django/db/backends/utils.py", line 100, in _execute with self.db.wrap_database_errors: ^^^^^^^^^^^^^^^ File "/home/clifford/code/django/django/db/utils.py", line 94, in exit raise dj_exc_value.with_traceback(traceback) from exc_value ^^^^^^^ File "/home/clifford/code/django/django/db/backends/utils.py", line 105, in _execute return self.cursor.execute(sql, params) ^^^^^^^ File "/home/clifford/.pyenv/versions/dj/lib/python3.14/site-packages/psycopg/cursor.py", line 97, in execute raise ex.with_traceback(None) ^^^^^^^^^^^ django.db.utils.ProgrammingError: cannot adapt type 'dict' using placeholder '%t' (format: TEXT) Ran 102 tests in 3.359s

Do you have an idea about this?

I will look into it 👍🏾

django/db/models/fields/json.py

cliffordgama · 2025-10-31T11:17:36Z

buildbot, test on oracle.

jacobtylerwalls

Thanks @cliffordgama 👍

tests/model_fields/test_jsonfield.py

…QL and Oracle. Added a JSONIn lookup to handle correct serialization and extraction for JSONField top-level __in queries on backends without native JSON support. KeyTransformIn now subclasses JSONIn. Thanks Jacob Walls for the report and review.

cliffordgama force-pushed the ticket-36689 branch from 32009e1 to 1dfe85d Compare October 27, 2025 20:43

cliffordgama marked this pull request as draft October 27, 2025 20:52

cliffordgama force-pushed the ticket-36689 branch from 1dfe85d to 628dba5 Compare October 27, 2025 21:40

cliffordgama marked this pull request as ready for review October 27, 2025 21:40

This comment was marked as outdated.

Sign in to view

cliffordgama force-pushed the ticket-36689 branch 2 times, most recently from 9a70f9f to 7fc097f Compare October 30, 2025 11:35

jacobtylerwalls reviewed Oct 30, 2025

View reviewed changes

cliffordgama force-pushed the ticket-36689 branch from 7fc097f to fa8f10c Compare October 30, 2025 19:00

cliffordgama commented Oct 30, 2025

View reviewed changes

cliffordgama commented Oct 31, 2025

View reviewed changes

django/db/models/fields/json.py Outdated Show resolved Hide resolved

cliffordgama force-pushed the ticket-36689 branch from fa8f10c to 2541f1f Compare October 31, 2025 11:02

jacobtylerwalls reviewed Oct 31, 2025

View reviewed changes

tests/model_fields/test_jsonfield.py Outdated Show resolved Hide resolved

cliffordgama force-pushed the ticket-36689 branch from 2541f1f to d656535 Compare November 4, 2025 08:49

Uh oh!

Fixed #36689 -- Fixed top-level JSONField __in lookup failures on MySQL and Oracle. #20009

Are you sure you want to change the base?

Fixed #36689 -- Fixed top-level JSONField __in lookup failures on MySQL and Oracle. #20009

Conversation

cliffordgama commented Oct 27, 2025

Trac ticket number

Checklist

Uh oh!

This comment was marked as outdated.

jacobtylerwalls commented Oct 30, 2025

Uh oh!

jacobtylerwalls left a comment

Choose a reason for hiding this comment

Uh oh!

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

jacobtylerwalls Nov 5, 2025 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Choose a reason for hiding this comment

Uh oh!

cliffordgama Nov 5, 2025 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Uh oh!

cliffordgama commented Oct 31, 2025

Uh oh!

jacobtylerwalls left a comment

Choose a reason for hiding this comment

Uh oh!

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

2 participants

jacobtylerwalls Nov 5, 2025 •

edited

Loading

cliffordgama Nov 5, 2025 •

edited

Loading