JSONField's __contains and __contained_by lookups don't work with nested values on SQLite.

[Mariusz Felisiak]

SQLite 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".

I'm not sure if it's feasible to emulate it in Python.

Some (not really complicated) examples that don't work:

diff --git a/tests/model_fields/test_jsonfield.py b/tests/model_fields/test_jsonfield.py
index 9a9e1a1286..1acc5af73e 100644
--- a/tests/model_fields/test_jsonfield.py
+++ b/tests/model_fields/test_jsonfield.py
@@ -449,9 +449,14 @@ 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]]),
             ([1, [2]], [self.objs[5]]),
+            ([1], [self.objs[5]]),
+            ([[2]], [self.objs[5]]),
             ({'n': [None]}, [self.objs[4]]),
             ({'j': None}, [self.objs[4]]),
         ]

[Carlton Gibson]

OK, grrrr... — Just a case of doc-ing the limitations. 🤨
(Thanks)

[Mariusz Felisiak]

Draft.

[Mariusz Felisiak]

I've attached a draft solution but it's really hot and it doesn't handle list with dicts (...deep rabbit hole). IMO we should drop support for these lookups on SQLite, at least for now. Testing containment of JSONField is really complicated, I hope SQLite and Oracle will prepare native solutions in future versions.

[Mariusz Felisiak]

​PR

[GitHub <noreply@…>]

In ba69193:

Fixed #31836 -- Dropped support for JSONField contains and contained_by lookups on SQLite.

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".

[Mariusz Felisiak <felisiak.mariusz@…>]

In 247bcef6:

[3.1.x] Fixed #31836 -- Dropped support for JSONField contains and contained_by lookups on SQLite.

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".

Backport of ba691933cee375195c9c50f333dd4b2a3abbb726 from master.

[Tobias Bengfort]

I hope SQLite and Oracle will prepare native solutions in future versions.

AFAIU there is a native solution for sqlite by using a join:

`
Foo.objects.filter(jsonsomepathcontains='bar')
SELECT … FROM foo, json_each(foo.json, '$.some.path') WHERE json_each.value = 'bar';
`

[Tobias Bengfort]

Sorry for the messed up format. I will try again:

Foo.objects.filter(json__some__path__contains='bar')
SELECT … FROM foo, json_each(foo.json, '$.some.path') WHERE json_each.value = 'bar';