CosmosDB + group by

Question

I need to use "GROUP BY" clause on Azure Data Explorer but I think it is unsupported.

Someone have any idea to solve or avoid group by?

Best regards,

Answer 1

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>

Answer 2

Cosmos DB doesn't support group by feature,you could vote up this if you have urgent need.

Provide a third-party package documentdb-lumenize for your reference here which supports group by feature,it has .net example:

string configString = @"{
    cubeConfig: {
        groupBy: 'state', 
        field: 'points', 
        f: 'sum'
    }, 
    filterQuery: 'SELECT * FROM c'
}";
Object config = JsonConvert.DeserializeObject<Object>(configString);
dynamic result = await client.ExecuteStoredProcedureAsync<dynamic>("dbs/db1/colls/coll1/sprocs/cube", config);
Console.WriteLine(result.Response);

You could group by assetId column and get the max timestamp.

Besides,you could refer to my previous case:how to count distinct value in cosmos DB to use stored procedure to implement some aggregation features.

Answer 3

There is no group by in Azure Data Explorer as of now. However, there is the summarize operator that can help achieve many of the things that you would use GROUP BY for in SQL.

You can find it at https://docs.microsoft.com/en-us/azure/kusto/query/summarizeoperator