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
31835	JSONField's __contains lookup doesn't work in nested values on Oracle.	Mariusz Felisiak	Mariusz Felisiak	"Oracle doesn't provide a native way for testing containment of JSONField. The current implementation works only for basic examples without supporting nested structures and doesn't follow ''""the general principle that the contained object must match the containing object as to structure and data contents, possibly **after discarding some non-matching array elements** or **object key/value pairs** from the containing object""''.

It looks that building multi-level/recursive filters with `JSON_EXISTS()` is the only feasible way, but it's really complicated and can be clunky.

I think we should drop this lookup on Oracle and raise `NotSupportedError`.

Some examples:
{{{
diff --git a/tests/model_fields/test_jsonfield.py b/tests/model_fields/test_jsonfield.py
index 3ce7fc51a9..b659675ec8 100644
--- a/tests/model_fields/test_jsonfield.py
+++ b/tests/model_fields/test_jsonfield.py
@@ -445,8 +445,12 @@ class TestQuerying(TestCase):
         tests = [
             ({}, self.objs[2:5] + self.objs[6:8]),
             ({'baz': {'a': 'b', 'c': 'd'}}, [self.objs[7]]),
+            ({'baz': {'a': 'b'}}, [self.objs[7]]),
+            ({'baz': {'c': 'd'}}, [self.objs[7]]),
             ({'k': True, 'l': False}, [self.objs[6]]),
             ({'d': ['e', {'f': 'g'}]}, [self.objs[4]]),
+            ({'d': ['e']}, [self.objs[4]]),
+            ({'d': [{'f': 'g'}]}, [self.objs[4]]),
             ([1, [2]], [self.objs[5]]),
             ({'n': [None]}, [self.objs[4]]),
             ({'j': None}, [self.objs[4]]),
}}}"	Bug	closed	Database layer (models, ORM)	3.1	Release blocker	fixed			Accepted	1	0	0	0	0	0