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	QuerySet.order_by() chained with values() crashes on JSONField with a custom decoder on PostgreSQL.	Marc DEBUREAUX	Mariusz Felisiak	"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
}}}
"	Bug	closed	Database layer (models, ORM)	3.1	Release blocker	fixed		sage Simon Charette Thiago Bellini Ribeiro	Accepted	1	0	0	0	0	0