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
31956	JSONField doesn't support ORDER BY clauses after aggregation	Marc DEBUREAUX	nobody	"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') 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
}}}
"	Bug	new	Database layer (models, ORM)	3.1	Normal				Unreviewed	0	0	0	0	0	0