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.

Yasminyasmine

a.AlarmType != null should be a.AlarmType IS NOT NULL. – Grenade

Aure you sure you're talking about CosmosDB SQL and not TransactSQL for SQL Server ? – Comet

I am talking about SQL in general. (The <sql> tag says "Answers to questions tagged with SQL should use ISO/IEC standard SQL.") Does CosmosDB work differently here? – Grenade

Yes, it seems IEC standard SQL 'IS NOT NULL' is not Microsoft SQL compliant. – Comet

Very weird. Should you perhaps do IS_NULL(a.AlarmType)? – Grenade

Not needed, a.AlarmType != null works – Comet

As of May 2020, this query returns a more descriptive error "ORDER BY item expression could not be mapped to a document path", still does not work though. – Curlew

Answer 1

As of May 2024, 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)

Adminicle

Maybe if we all vote on the feedback they might reconsider feedback.azure.com/d365community/idea/… – Independent

Answer 2

I suggest you to create an user defined function. You can create a function to sort the elements and later use it in your SQL query.

Bourne

To add to @Brayan Hernández answer, make sure you understand the implications and limitations of user defined functions in Cosmos before you decide to implement them. They can have significant performance, cost, and scalability impacts, and also face a lot of limitations. Without deep knowledge of how Cosmos works, or at least their limitations, I'd be wary of using them. – Adminicle

Answer 3

I think you can achieve the result by using order by inside the query. like below.

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

OR

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

However cosmos db prefers alias for each and every conditions inside the query hence the error you are getting.

Bratton

No I tried this before attempting a nested query and the error is: 'ORDER BY' is not supported in presence of GROUP BY. – Comet