CODEWITHSUNDEEP
azure-cosmosdb
Slicc
Slicc

Reputation: 3435

Alternative to group by for cosmos db

Given that cosmos db does not support group by, what is a good alternative to achieve similar functionality:

Select sum(*) , groupterm from tble group by groupterm

Can I efficiently achieve this in a cosmos stored procedure?

Upvotes: 10

Views: 26131

Answers (3)

Sajeetharan
Sajeetharan

Reputation: 222532

Finally, Azure Cosmos DB currently supports GROUP BY in .NET SDK 3.3 or later. Support for other language SDK's and the Azure Portal is not currently available but is planned.

<group_by_clause> ::= GROUP BY <scalar_expression_list>

<scalar_expression_list> ::=
          <scalar_expression>
        | <scalar_expression_list>, <scalar_expression>

enter image description here

Upvotes: 4

Apurv Gupta
Apurv Gupta

Reputation: 860

Group by is now supported in Cosmos db SQL API. You will be needing SDK version 3.3 or higher

Azure Cosmos DB currently supports GROUP BY in .NET SDK 3.3 or later. Support for other language SDK's and the Azure Portal is not currently available but is planned.

https://learn.microsoft.com/en-gb/azure/cosmos-db/sql-query-group-by

Upvotes: 8

Bruce Chen
Bruce Chen

Reputation: 18465

As Cosmos_DB states as follows:

Aggregation capability in SQL limited to COUNT, SUM, MIN, MAX, AVG functions. No support for GROUP BY or other aggregation functionality found in database systems. However, stored procedures can be used to implement in-the-database aggregation capability.

Can I efficiently achieve this in a cosmos stored procedure?

For .NET and Node.js

Larry Maccherone has provided a great package documentdb-lumenize which supports Aggregations (Group-by, Pivot-table, and N-dimensional Cube) and Time Series Transformations as Stored Procedures in DocumentDB.

Additionally, for Python and Scala, you could refer to azure-cosmosdb-spark.

Upvotes: 11

Related Questions