[Django] #31836: JSONField's __contains and __contained_by lookups don't work with nested values on SQLite.
Django
values on SQLite.
-------------------------------------+-------------------------------------
Reporter: felixxm | Owner: nobody
Type: Bug | Status: new
Component: Database | Version: 3.1
layer (models, ORM) |
Severity: Release | Keywords:
blocker |
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
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]]),
]
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/31836>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
values on SQLite.
-------------------------------------+-------------------------------------
Reporter: felixxm | Owner: nobody
Type: Bug | Status: new
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* stage: Unreviewed => Accepted
Comment:
OK, grrrr... — Just a case of doc-ing the limitations. 🤨
(Thanks)
--
Ticket URL: <https://code.djangoproject.com/ticket/31836#comment:1>
Django
values on SQLite.
-------------------------------------+-------------------------------------
Reporter: felixxm | Owner: nobody
Type: Bug | Status: new
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* Attachment "draft.py" added.
Draft.
Django
values on SQLite.
-------------------------------------+-------------------------------------
Reporter: felixxm | Owner: nobody
Type: Bug | Status: new
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by felixxm):
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/31836#comment:2>
Django
values on SQLite.
-------------------------------------+-------------------------------------
Type: Bug | Status: assigned
Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* owner: nobody => felixxm
* status: new => assigned
--
Ticket URL: <https://code.djangoproject.com/ticket/31836#comment:3>
Django
values on SQLite.
-------------------------------------+-------------------------------------
Type: Bug | Status: assigned
Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Release blocker | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* has_patch: 0 => 1
Comment:
[https://github.com/django/django/pull/13250 PR]
--
Ticket URL: <https://code.djangoproject.com/ticket/31836#comment:4>
Django
values on SQLite.
-------------------------------------+-------------------------------------
Type: Bug | Status: assigned
Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Release blocker | Resolution:
| checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* stage: Accepted => Ready for checkin
--
Ticket URL: <https://code.djangoproject.com/ticket/31836#comment:5>
Django
values on SQLite.
-------------------------------------+-------------------------------------
Type: Bug | Status: closed
Component: Database layer | Version: 3.1
(models, ORM) |
Keywords: | Triage Stage: Ready for
| checkin
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: assigned => closed
* resolution: => fixed
Comment:
In [changeset:"ba691933cee375195c9c50f333dd4b2a3abbb726" ba69193]:
{{{
#!CommitTicketReference repository=""
revision="ba691933cee375195c9c50f333dd4b2a3abbb726"
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".
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/31836#comment:6>
Django
values on SQLite.
-------------------------------------+-------------------------------------
Type: Bug | Status: closed
Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Release blocker | Resolution: fixed
Keywords: | Triage Stage: Ready for
| checkin
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Mariusz Felisiak <felisiak.mariusz@…>):
In [changeset:"247bcef6b4f8625e80b6f07e264b7bbdf330194d" 247bcef6]:
{{{
#!CommitTicketReference repository=""
revision="247bcef6b4f8625e80b6f07e264b7bbdf330194d"
[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.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/31836#comment:7>
Django
values on SQLite.
-------------------------------------+-------------------------------------
| Felisiak
Type: Bug | Status: closed
Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Release blocker | Resolution: fixed
Keywords: | Triage Stage: Ready for
| checkin
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (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(json__some__path__contains='bar')
SELECT … FROM foo, json_each(foo.json, '$.some.path') WHERE
json_each.value = 'bar';
```
--
Ticket URL: <https://code.djangoproject.com/ticket/31836#comment:8>
Django
values on SQLite.
-------------------------------------+-------------------------------------
| Felisiak
Type: Bug | Status: closed
Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Release blocker | Resolution: fixed
Keywords: | Triage Stage: Ready for
| checkin
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (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';
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/31836#comment:9>