[Django] #28168: Using qs.order_by() and qs.values() with JSONFields
Django
------------------------------------------------+------------------------
Reporter: Austin Roberts | Owner: (none)
Type: Cleanup/optimization | Status: new
Component: contrib.postgres | Version: 1.11
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
------------------------------------------------+------------------------
When using JSONFields, I expected to be able to construct a queryset like:
MyModel.objects.all().order_by("metadata__some__field")
where "metadata" is a JSONField, "some" is a key in that JSONField, and
"field" is a key within the "some" dictionary.
When I try, however, Django treats it as a join instead of a metadata
field lookup, and I get this:
{{{
Traceback (most recent call last):
File "<console>", line 1, in <module>
File "/home/aroberts/.virtualenvs/example/local/lib/python2.7/site-
packages/django/db/models/query.py", line 226, in __repr__
data = list(self[:REPR_OUTPUT_SIZE + 1])
File "/home/aroberts/.virtualenvs/example/local/lib/python2.7/site-
packages/django/db/models/query.py", line 250, in __iter__
self._fetch_all()
File "/home/aroberts/.virtualenvs/example/local/lib/python2.7/site-
packages/django/db/models/query.py", line 1102, in _fetch_all
self._result_cache = list(self._iterable_class(self))
File "/home/aroberts/.virtualenvs/example/local/lib/python2.7/site-
packages/django/db/models/query.py", line 53, in __iter__
results = compiler.execute_sql(chunked_fetch=self.chunked_fetch)
File "/home/aroberts/.virtualenvs/example/local/lib/python2.7/site-
packages/django/db/models/sql/compiler.py", line 863, in execute_sql
sql, params = self.as_sql()
File "/home/aroberts/.virtualenvs/example/local/lib/python2.7/site-
packages/django/db/models/sql/compiler.py", line 412, in as_sql
extra_select, order_by, group_by = self.pre_sql_setup()
File "/home/aroberts/.virtualenvs/example/local/lib/python2.7/site-
packages/django/db/models/sql/compiler.py", line 47, in pre_sql_setup
order_by = self.get_order_by()
File "/home/aroberts/.virtualenvs/example/local/lib/python2.7/site-
packages/django/db/models/sql/compiler.py", line 298, in get_order_by
field, self.query.get_meta(), default_order=asc))
File "/home/aroberts/.virtualenvs/example/local/lib/python2.7/site-
packages/django/db/models/sql/compiler.py", line 593, in
find_ordering_name
field, targets, alias, joins, path, opts = self._setup_joins(pieces,
opts, alias)
File "/home/aroberts/.virtualenvs/example/local/lib/python2.7/site-
packages/django/db/models/sql/compiler.py", line 626, in _setup_joins
pieces, opts, alias)
File "/home/aroberts/.virtualenvs/example/local/lib/python2.7/site-
packages/django/db/models/sql/query.py", line 1427, in setup_joins
names, opts, allow_many, fail_on_missing=True)
File "/home/aroberts/.virtualenvs/example/local/lib/python2.7/site-
packages/django/db/models/sql/query.py", line 1395, in names_to_path
" not permitted." % (names[pos + 1], name))
FieldError: Cannot resolve keyword 'some' into field. Join on 'metadata'
not permitted.
}}}
For now, I'm able to work around this by instead doing:
{{{
from django.db.models.expressions import OrderBy, RawSQL
MyModel.objects.all().order_by(OrderBy(RawSQL("metadata #> '{some}' #>
'{field}'", []))
}}}
Similarly, I have cases where I want to be able to do
{{{
MyModel.objects.all().values("metadata__some__field")
}}}
I've worked out a similar work around of
{{{
MyModel.objects.all().annotate(some_field=RawSQL("metadata #> '{some}' #>
'{field}'", [])).values("some_field")
}}}
Both of these are things it would be nice to have cleaner, more natural
syntax for in Django. I'm not sure how deep the changes would have to be,
whether it could just be addressed in the contrib or if it would require
core changes. In any case, I suspect there will be other people looking
for workarounds like this, so I figured I'd share my findings.
--
Ticket URL: <https://code.djangoproject.com/ticket/28168>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
Reporter: Austin Roberts | Owner: (none)
Cleanup/optimization |
Component: contrib.postgres | Version: 1.11
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Tim Graham):
* cc: Marc Tamlyn (added)
Old description:
New description:
`MyModel.objects.all().order_by("metadata__some__field")`
{{{
MyModel.objects.all().values("metadata__some__field")
}}}
--
Comment:
Marc, do you have any thoughts?
--
Ticket URL: <https://code.djangoproject.com/ticket/28168#comment:1>
Django
Reporter: Austin Roberts | Owner: (none)
Cleanup/optimization |
Component: contrib.postgres | Version: 1.11
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Matthew Wilkes):
I think this might be a subset of #24747 ?
--
Ticket URL: <https://code.djangoproject.com/ticket/28168#comment:2>
Django
Reporter: Austin Roberts | Owner: (none)
Cleanup/optimization |
Component: contrib.postgres | Version: 1.11
Severity: Normal | Resolution: duplicate
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Tim Graham):
* status: new => closed
* resolution: => duplicate
Comment:
Closing this as a duplicate of that ticket.
--
Ticket URL: <https://code.djangoproject.com/ticket/28168#comment:3>