azure - SELECT VALUE COUNT(1) FROM (SELECT DISTINCT c.UserId FROM root c) AS t not working

Question

In a Cosmos DB stored procedure, I'm using a inline sql query to try and retrieve the distinct count of a particular user id.

I'm using the SQL API for my account. I've run the below query in Query Explorer in my Cosmos DB account and I know that I should get a count of 10 (There are 10 unique user ids in my collection):

SELECT VALUE COUNT(1) FROM (SELECT DISTINCT c.UserId FROM root c) AS t

However when I run this in the Stored Procedure portal, I either get 0 records back or 18 records back (total number of documents). The code for my Stored Procedure is as follows:

function GetDistinctCount() {
    var collection = getContext().getCollection();

    var isAccepted = collection.queryDocuments(
        collection.getSelfLink(),
        'SELECT VALUE COUNT(1) FROM (SELECT DISTINCT c.UserId  FROM root c) AS t',      
        function(err, feed, options) {
            if (err) throw err;

            if (!feed || !feed.length) {
                var response = getContext().getResponse();
                var body = {code: 404, body: "no docs found"}
                response.setBody(JSON.stringify(body));
            } else {
                var response = getContext().getResponse();
                var body = {code: 200, body: feed[0]}
                response.setBody(JSON.stringify(body));
            }
        }
    )
}

After looking at various feedback forums and documentation, I don't think there's an elegant solution for me to do this as simply as it would be in normal SQL.

the UserId is my partition key which I'm passing through in my C# code and when I test it in the portal, so there's no additional parameters that I need to set when calling the Stored Proc. I'm calling this Stored Proc via C# and adding any further parameters will have an effect on my tests for that code, so I'm keen not to introduce any parameters if I can.

标签： azureazure-cosmosdbazure-cosmosdb-sqlapi

Answer 1

Your problem is caused by that you missed setting partition key for your stored procedure.

Please see the statements in the official document:

And this:

So,when you execute a stored procedure under a partitioned collection, you need to pass the partition key param. It's necessary! (Also this case explained this:Documentdb stored proc cross partition query)

Back to your question,you never pass any partition key, equals you pass an null value or "" value for partition key, so it outputs no data because you don't have any userId equals null or "".

My advice: You could use normal Query SDK to execute your sql, and set the enableCrossPartitionQuery: true which allows you scan entire collection without setting partition key. Please refer to this tiny sample:Can't get simple CosmosDB query to work via Node.js - but works fine via Azure's Query Explorer