Property reference is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

Question

Azure Cosmos Document DB throws an error on executing a query like this -

SELECT DISTINCT VALUE 
{ 
    DocumentName: c.Name, 
    Count: COUNT(c.id),
    Target: c.Target
}
FROM c where c.Target != null

Error -

SC2102: Property reference 'c.Name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

SC2102: Property reference 'c.Target' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

In regular SQL I would solve this by adding

GROUP BY c.Name, c.Target

at the end of the query but Cosmos DB does not seem to support a group clause.

SC1001: Syntax error, incorrect syntax near 'GROUP'.

I'm wondering if a GROUP-like clause is supported. And if it is not supported, what is the meaning of this error?

Answer 1

Based on the azure cosmos db feedback, group by is currently in active development and will be shipped ASAP.

For now, you could refer to the SO thread Grouping by a field in DocumentDB which is very helpful for you. The answer has written a library documentdb-lumenize based on Document Db stored procedure that you can try it.

If you do concern the RUs, of course you could metric RUs consumption of stored procedure.

You could call executeStoredProcedure method in Cosmos DB SDK, then use getRequestCharge() method. It won't show in the portal.

Such as Java code:

StoredProcedureResponse resourceResponse = documentClient.executeStoredProcedure("dbs/db/colls/item/sprocs/b",requestOptions ,null);
System.out.println(resourceResponse.getRequestCharge());