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?

I think you may refer the accepted answer of this question : https://stackoverflow.com/questions/45270125/alternative-to-group-by-for-cosmos-db — Barbaros Özhan, Oct 22 '18 at 07:18
I wanted to do this in the query like the one in question since it is possible to optimize by seeing what uses fewer RUs this way. Doing it in LINQ (or lumenize as well) might not give me that advantage. — Nikhil Girraj, Oct 22 '18 at 07:23

score 0 · Accepted Answer · edited Aug 30 '19 at 21:02

0

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());

edited Aug 30 '19 at 21:02

halfer

19,824
17
99
186

answered Oct 23 '18 at 01:41

Jay Gong

23,163
2
27
32

So do you think that the current message is misleading? – Nikhil Girraj Oct 24 '18 at 06:09
@NikhilGirraj Yes, I think it's misleading. Maybe you could submit feedback to azure team. – Jay Gong Oct 24 '18 at 06:10
With this comment, this answer seems what I needed. Thanks. – Nikhil Girraj Oct 24 '18 at 06:21

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

1 Answers1