Count of distinct doesnot work with cosmosdb

Question

The below distinct query now works:

SELECT DISTINCT c.name  FROM c

but the below query is not working, is there something done wrong here?

SELECT COUNT(DISTINCT c.name) FROM c

Failed to query documents for collection products: {"code":400,"body":"{\"code\":\"BadRequest\",\"message\":\"Message: {\\\"errors\\\":[{\\\"severity\\\":\\\"Error\\\",\\\"location\\\":{\\\"start\\\":13,\\\"end\\\":21},\\\"code\\\":\\\"SC1001\\\",\\\"message\\\":\\\"Syntax error, incorrect syntax near 'DISTINCT'.\\\"}]}\\r\\nActivityId: 05bf1757-ce1b-4ee4-9ae5-f2b778c55ded, Microsoft.Azure.Documents.Common/2.0.0.0\"}","activityId":"05bf1757-ce1b-4ee4-9ae5-f2b778c55ded"}

It looks like you'll have to get the result set from your first (working) query, and then do a count select on that. — Tim Biegeleisen, Oct 22 '18 at 07:08
If you think my answer helps you,please mark it for answer.Thanks. — Jay Gong, Oct 25 '18 at 09:12
Hope this will work. `SELECT VALUE COUNT(1) from (SELECT DISTINCT c.name from c)` — Antony, Aug 26 '19 at 04:02

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

SELECT COUNT(DISTINCT c.name) FROM c is syntax error which is not supported by Cosmos DB.

You could use Stored Procedure to do the count task for your query results:

function sample() {
    var collection = getContext().getCollection();
    var isAccepted = collection.queryDocuments(
        collection.getSelfLink(),
        'SELECT distinct r.name FROM root r',
    function (err, feed, options) {
        if (err) throw err;

        if (!feed || !feed.length) {
            var response = getContext().getResponse();
            response.setBody('no docs found');
            //or return 0 
        }
        else {
            var response = getContext().getResponse();
            response.setBody(feed.length);
        }
    });

    if (!isAccepted) throw new Error('The query was not accepted by the server.');
}

Best to just vote to close this question as duplicate, and then add your answer to the other question. — David Makogon, Oct 23 '18 at 01:39

Count of distinct doesnot work with cosmosdb

1 Answers1