Opened 5 years ago

Last modified 21 months ago

#31956 closed Bug

JSONField doesn't support ORDER BY clauses after aggregation — at Version 1

Reported by: Marc DEBUREAUX Owned by: nobody
Component: Database layer (models, ORM) Version: 3.1
Severity: Release blocker Keywords:
Cc: sage, Simon Charette, Thiago Bellini Ribeiro Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by Marc DEBUREAUX)

When using ORDER BY clause after an aggregation on a JSONField as described below:

MyModel.objects.values('jsonfield__subfield').annotate(count=Count('id')).order_by('jsonfield__subfield')

I got the following error:

column "myapp_mymodel.jsonfield" must appear in the GROUP BY clause or be used in an aggregate function

The SQL query seems OK at first glance:

SELECT (("mymodel"."jsonfield" -> 'subfield'))::text, COUNT("mymodel"."id") AS "id_count" FROM "mymodel" GROUP BY (("mymodel"."jsonfield" -> 'subfield'))::text ORDER BY ("mymodel"."jsonfield" -> 'subfield') ASC

But it fails on PostgreSQL 12+ because ORDER BY clause doesn't include ::text casting. Instead the query must be:

SELECT (("mymodel"."jsonfield" -> 'subfield'))::text, COUNT("mymodel"."id") AS "id_count" FROM "mymodel" GROUP BY (("mymodel"."jsonfield" -> 'subfield'))::text ORDER BY (("mymodel"."jsonfield" -> 'subfield'))::text ASC

Or without casting at all (prone to error?):

SELECT ("mymodel"."jsonfield" -> 'subfield'), COUNT("mymodel"."id") AS "id_count" FROM "mymodel" GROUP BY ("mymodel"."jsonfield" -> 'subfield') ORDER BY ("mymodel"."jsonfield" -> 'subfield') ASC

Change History (1)

comment:1 by Marc DEBUREAUX, 5 years ago

Description: modified (diff)
Note: See TracTickets for help on using tickets.
Back to Top