Opened 18 months ago

Closed 18 months ago

Last modified 7 months ago

#31836 closed Bug (fixed)

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

Reported by: Mariusz Felisiak Owned by: Mariusz Felisiak
Component: Database layer (models, ORM) Version: 3.1
Severity: Release blocker Keywords:
Cc: sage Triage Stage: Ready for checkin
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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]]),
         ]

Attachments (1)

draft.py (1.3 KB) - added by Mariusz Felisiak 18 months ago.
Draft.

Download all attachments as: .zip

Change History (10)

comment:1 Changed 18 months ago by Carlton Gibson

Triage Stage: UnreviewedAccepted

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

Changed 18 months ago by Mariusz Felisiak

Attachment: draft.py added

Draft.

comment:2 Changed 18 months ago by 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.

comment:3 Changed 18 months ago by Mariusz Felisiak

Owner: changed from nobody to Mariusz Felisiak
Status: newassigned

comment:4 Changed 18 months ago by Mariusz Felisiak

Has patch: set

comment:5 Changed 18 months ago by Carlton Gibson

Triage Stage: AcceptedReady for checkin

comment:6 Changed 18 months ago by GitHub <noreply@…>

Resolution: fixed
Status: assignedclosed

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

comment:7 Changed 18 months ago by 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.

comment:8 Changed 7 months ago by 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';
`

comment:9 Changed 7 months ago by 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';
Note: See TracTickets for help on using tickets.
Back to Top