[Feature Request] Orderable ArrayAgg and StringAgg in contrib.postgres.aggregates

[Floris den Hengst]

The excellent ArrayAgg and StringAgg Postgres-specific aggregates were introduced in contrib.postgres in Django 1.9 and I've been quite happy using them here and there.

Thanks for the keeping Django awesome!

The documentation of Postgres 9.0 first mentions the possiblity of ordering the results within such aggregations.

This could be useful in some cases.

For example: it could make sense to perform the StringAgg in lexicographical order in some cases.

The simple format of ordering the aggregation result in SQL is quite simple:

SELECT ARRAY_AGG(some_field ORDER BY some_field ASC) FROM table;
SELECT ARRAY_AGG(some_field ORDER BY some_field DESC) FROM table;
SELECT ARRAY_AGG(some_field ORDER BY other_field ASC) FROM table;

It would be nice if the above would be supported as follows:

Model.objects.aggregate(ArrayAgg(some_field, order_by='some_field'))
Model.objects.aggregate(ArrayAgg(some_field, order_by='-some_field'))
Model.objects.aggregate(ArrayAgg(some_field, order_by='other_field'))

where order_by is an optional parameter. If it not specified, behavior can remain unchanged from the current implementation.

Note that the documentation for Postgres >= 9.3 also mentions json_agg ordering and Postgres >= 9.4 mentions json_object_agg as well.

I'm unsure whether these should be included as well as I couldn't find the matching implementation in the ORM atm and have no knowledge on plans in that direction.

[Josh Smeaton]

Seems reasonable enough to me. Expressions already support generating an ORDER BY clause by calling .asc() or .desc() on them. That'd allow your proposed API to support:

Model.objects.aggregate(ArrayAgg(some_field, order_by=F('some_field').asc()))

Or any other expression. Consider that any ordering added within *may* need to be contributed to GROUP BY, but I haven't read the documentation to say that is a requirement.

Feel free to open a ticket in Trac to track the feature. If you're also up for implementing it (..and testing.. and documenting..) I'm quietly confident that it'll be fairly straight forward to add, given familiarity with Expressions API.

Additionally, if you think json_agg and json_object_agg should be implemented, you can open a feature ticket on Trac for those too. 

Regards,

[Tim Graham]

Ticket for JSON_AGG: https://code.djangoproject.com/ticket/26327

[Floris den Hengst]

@Josh, thanks for pointing out the Expressions API

I will look into it and see if I can come up with a solution that looks reasonable (including an investigation into requirements on contributing to the GROUP BY) and report here / in Trac.

[Floris den Hengst]

I investigated the GROUP BY requirements when using an ORDER BY within these aggregates, but there appear to be none.

Further details can be found in the discussion in Trac: https://code.djangoproject.com/ticket/26067.

Based on this observation, I went on to create a PR: https://github.com/django/django/pull/6886

If anyone is up for it, please have a look!

[Floris den Hengst]

Thanks for all the great comments & suggestions :)

The original PR was declined due to inactivity.

A new PR with fixes can be found here: https://github.com/django/django/pull/7604