#28168: Using qs.order_by() and qs.values() with JSONFields -------------------------------------+------------------------------------- Reporter: Austin Roberts | Owner: (none) Type: | Status: new 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: > 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. New description: 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. -- Comment: Marc, do you have any thoughts? -- Ticket URL: <https://code.djangoproject.com/ticket/28168#comment:1> Django <https://code.djangoproject.com/> The Web framework for perfectionists with deadlines. -- You received this message because you are subscribed to the Google Groups "Django updates" group. To unsubscribe from this group and stop receiving emails from it, send an email to django-updates+unsubscr...@googlegroups.com. To post to this group, send email to django-updates@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/django-updates/063.df0de04121545a17b50c3a2c442f706f%40djangoproject.com. For more options, visit https://groups.google.com/d/optout.