[Django] #29504: JSONField dictionary/object lookup using an "integer" key
Django
-----------------------------------------+------------------------
Reporter: Shaheed Haque | Owner: nobody
Type: Uncategorized | Status: new
Component: Documentation | Version: 2.0
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 1 |
-----------------------------------------+------------------------
The documentation on performing queries inside JSONField values
[https://docs.djangoproject.com/en/2.0/ref/contrib/postgres/fields/#key-
index-and-path-lookups] says:
{{{
If the key is an integer, it will be interpreted as an index lookup in an
array:
>>> Dog.objects.filter(data__owner__other_pets__0__name='Fishy')
}}}
Note the specific mention of **array**. While this might be true, it is
not the whole truth as applied to **dict/object**. For example, given a
**dict/object** whose keys are strings (as always in JSON) but which look
like integers:
{{{
"employee": {
"415": {
"email": "Sherloc...@acme.co.uk",
"mobile": "0700 1234567",
}}}
how is one supposed to select the **"415"** bit? It turns out that the
same syntax as for the array case applies:
{{{
Foo.objects.filter(snapshot__employee__415__mobile='0700 1234567')
}}}
This was not at all obvious to me at least, especially as if the **"415"**
is looked up as the terminal level in the query, the syntax becomes very
different:
{{{
Foo.objects.filter(snapshot__employee__has_key='415')
}}}
Since I wasted quite a bit of time on this, I thought it might be useful
to strengthen the documentation in this area to clarify how to lookup:
* In arrays and dict/objects
* if the key is the final term in the query
* if the key is not the final term in the query
--
Ticket URL: <https://code.djangoproject.com/ticket/29504>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
Reporter: Shaheed Haque | Owner: nobody
Type: Uncategorized | Status: new
Component: Documentation | Version: 2.0
Keywords: | Triage Stage: Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 1
Description changed by Shaheed Haque:
Old description:
New description:
The documentation on performing queries inside JSONField values
[https://docs.djangoproject.com/en/2.0/ref/contrib/postgres/fields/#key-
index-and-path-lookups] says:
{{{
If the key is an integer, it will be interpreted as an index lookup in an
array:
>>> Dog.objects.filter(data__owner__other_pets__0__name='Fishy')
}}}
Note the specific mention of **array**. While this might be true, it is
not the whole truth as applied to **dict/object**. For example, given a
**dict/object** whose keys are strings (as always in JSON) but which look
like integers:
{{{
"employee": {
"415": {
"email": "Sherloc...@acme.co.uk",
"mobile": "0700 1234567",
}}}
how is one supposed to select the **"415"** bit? It turns out that the
same syntax as for the array case applies:
{{{
Foo.objects.filter(snapshot__employee__415__mobile='0700 1234567')
}}}
This was not at all obvious to me at least, especially as if the **"415"**
is looked up as the final key in the query, the syntax becomes very
different:
{{{
Foo.objects.filter(snapshot__employee__has_key='415')
}}}
Since I wasted quite a bit of time on this, I thought it might be useful
to strengthen the documentation in this area to clarify how to lookup:
* In arrays and dict/objects
* if the key is the final term in the query
* if the key is not the final term in the query
--
--
Ticket URL: <https://code.djangoproject.com/ticket/29504#comment:1>
Django
Reporter: Shaheed Haque | Owner: nobody
Type: Uncategorized | Status: new
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
-------------------------------+------------------------------------
Changes (by Carlton Gibson):
* ui_ux: 1 => 0
* version: 2.0 => master
* stage: Unreviewed => Accepted
Comment:
OK, I'm going to provisionally accept this. If you can put together a
patch we can have a look and see if there's a clarification to be made.
I'm **half-minded** to say `wontfix` since JSON keys must always be
strings, and whilst, like `'415'`, they might look like integers, there's
no real ambiguity.
But lets see what improvement you have in mind. :)
--
Ticket URL: <https://code.djangoproject.com/ticket/29504#comment:2>
Django
Reporter: Shaheed Haque | Owner: nobody
Type: Uncategorized | Status: new
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
-------------------------------+------------------------------------
Changes (by Carlton Gibson):
* cc: Carlton Gibson (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/29504#comment:3>
Django
Reporter: Shaheed Haque | Owner: nobody
Type: Uncategorized | Status: new
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
-------------------------------+------------------------------------
Comment (by Shaheed Haque):
I've created a PR at [https://github.com/django/django/pull/10077]. Please
consider.
--
Ticket URL: <https://code.djangoproject.com/ticket/29504#comment:4>
Django
Reporter: Shaheed Haque | Owner: nobody
Type: Uncategorized | Status: new
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
-------------------------------+------------------------------------
Changes (by Shaheed Haque):
* has_patch: 0 => 1
--
Ticket URL: <https://code.djangoproject.com/ticket/29504#comment:5>
Django
Reporter: Shaheed Haque | Owner: nobody
Component: Documentation | Version: master
Severity: Normal | Resolution: wontfix
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
--------------------------------------+------------------------------------
Changes (by Tim Graham):
* status: new => closed
* type: Uncategorized => Cleanup/optimization
* resolution: => wontfix
Comment:
Closing per discussion on PR.
--
Ticket URL: <https://code.djangoproject.com/ticket/29504#comment:6>
Django
-------------------------------------+-------------------------------------
Cleanup/optimization |
Component: Documentation | Version: dev
Severity: Normal | Resolution:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
-------------------------------------+-------------------------------------
Changes (by Jacob Walls):
* cc: Clifford Gama (added)
* has_patch: 1 => 0
* resolution: wontfix =>
* stage: Accepted => Unreviewed
* status: closed => new
* summary: JSONField dictionary/object lookup using an "integer" key =>
Document limitations of using int()able keys with JSONField
Comment:
I think there's enough ambiguity and lack of support across databases to
be worth some documentation here. (Thanks Clifford for pointing me at this
from #37297.)
On Postgres, string keys that are `int()`able can't be queried in the
usual way unless more lookups are chained afterward, because when
`len(key_transforms) == 1`, we assume any `int()`able key is an int.
Then, if you do chain more lookups afterward, it works perfectly fine, but
only on Postgres, where the `#> ARRAY[...` syntax is flexible enough to
match either keys or indices. Other databases require specific SQL to
match either a key or an index.
{{{#!py
from django.db import models
class Person(models.Model):
metadata = models.JSONField()
def run():
metadata = {
"0": "foo",
"zero": "foo",
}
instance = Person.objects.create(metadata=metadata)
# assert Person.objects.filter(metadata__0="foo").exists() # fails
assert Person.objects.filter(metadata__zero="foo").exists() # passes
metadata = {
"0": {
"nested": "foo"
}
}
instance = Person.objects.create(metadata=metadata)
assert Person.objects.filter(metadata__0__nested="foo").exists() #
works on PG only
}}}
Given all that, I think we should clarify that int()able keys aren't
always queryable?
--
Ticket URL: <https://code.djangoproject.com/ticket/29504#comment:7>
Django
-------------------------------------+-------------------------------------
Cleanup/optimization |
Component: Documentation | Version: dev
Severity: Normal | Resolution:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
-------------------------------------+-------------------------------------
Comment (by abheet):
I'd like to work on this ticket. I'll submit a documentation patch
clarifying that int()-able keys in JSONField are not always queryable.
--
Ticket URL: <https://code.djangoproject.com/ticket/29504#comment:8>
Django
Severity: Normal | Resolution:
Needs tests: 0 | Patch needs improvement: 0
--------------------------------------+------------------------------------
Changes (by Clifford Gama):
* stage: Unreviewed => Accepted
Comment:
--
Ticket URL: <https://code.djangoproject.com/ticket/29504#comment:9>
Django
--------------------------------------+------------------------------------
Type: Cleanup/optimization | Status: assigned
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
--------------------------------------+------------------------------------
Changes (by Abheet):
* has_patch: 0 => 1
* status: new => assigned
--
Ticket URL: <https://code.djangoproject.com/ticket/29504#comment:10>