How to order results of a query by the results of an aggregate function in ComosDb?

Question

I use Cosmos Db and I need results to be sorted by the results of a COUNT.

Instead of sorting the results each time myself (or create a service for it), I prefer having the results sorted directly in the output of the query from Cosmosdb.

In Document Explorer of Azure, I execute the following request on the AggregatedEvent collection:

SELECT * FROM (
    SELECT COUNT(1) AS AlarmCount, a.AlarmType FROM AggregatedEvent a
    WHERE a.AlarmType != null and a.Prom > 0
    GROUP BY a.AlarmType ) AS g
ORDER BY g.AlarmCount

It results the following error message:

Identifier 'AlarmCount' could not be resolved.

If I use another property for ordering g.AlarmType, a property that exists in the document, then the ordering is performed.

Please add an answer only if you have a way to archieve the ordering with CosmosDb.

Answer 1

As of May 2022, this functionality is still not supported. The documentation from Microsoft defines the limitation here: https://docs.microsoft.com/en-us/azure/cosmos-db/sql-query-group-by

The GROUP BY clause does not allow any of the following:

• Aliasing properties or aliasing system functions (aliasing is still allowed within the SELECT clause)
• Subqueries
• Aggregate system functions (these are only allowed in the SELECT clause)