id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
28168	Using qs.order_by() and qs.values() with JSONFields	Austin Roberts		"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."	Cleanup/optimization	closed	contrib.postgres	1.11	Normal	duplicate		Marc Tamlyn	Unreviewed	0	0	0	0	0	0