CosmosDB Cross-Partition Queries failing using API in PowerShell "SELECT VALUE COUNT(1) FROM c"

Question

Issue

I have a PowerShell script that works fine to do several things such as query a specific item from a container by id, a group of items under the same partition key, and so on.

However, when it comes to cross-partition queries, the same script fails:

Invoke-RestMethod : The remote server returned an error: (400) Bad Request.
At line:1 char:15
+ ...   $result = Invoke-RestMethod -Uri $requestURI -Headers $header -Meth ...
+                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (System.Net.HttpWebRequest:HttpWebRequest) [Invoke-RestMethod], WebException
    + FullyQualifiedErrorId : WebCmdletWebResponseException,Microsoft.PowerShell.Commands.InvokeRestMethodCommand

The request header is as the following:

$header = @{
        "Authorization"                              = "$Authorization";
        "Content-Type"                               = "application/query+json";
        "x-ms-date"                                  = $($xmsdate.ToString().ToLower());
        "x-ms-documentdb-isquery"                    = "true";
        "x-ms-documentdb-query-enablecrosspartition" = "true";
        "x-ms-documentdb-populatequerymetrics"       = "true";  
    }

Examples

This work:

$body = @{
    query = 'SELECT VALUE COUNT(1) FROM c WHERE c.partitionKey IN ("ef5af949")'
    parameters = @()
} | ConvertTo-Json

This work:

$body = @{
    query = 'SELECT VALUE COUNT(1) FROM c WHERE c.partitionKey = "ef5af949"'
    parameters = @()
} | ConvertTo-Json

This fail:

$body = @{
    query = 'SELECT VALUE COUNT(1) FROM c'
    parameters = @()
} | ConvertTo-Json

This fail:

$body = @{
    query = 'SELECT VALUE COUNT(1) FROM c WHERE c.partitionKey IN ("ef5af949", "eaafb5a5")'
    parameters = @()
} | ConvertTo-Json

The ask

What is the issue? I simply want to run 'SELECT VALUE COUNT(1) FROM c WHERE' so I can count the item count in a given container.

Please let me know if you need additional information. Also, I am not sure if this is a limitation "Queries that cannot be served by gateway"

Answer 1

You provided your own answer at the very bottom.

"Queries that cannot be served by gateway"

Viewing that link, we see this information as the first text shown:

Any query that requires state across continuations cannot be served by the gateway. This includes:

• ...
• Aggregates
• ...

COUNT() is an aggregate function, and when asking for more than one partition key you are trying to use it across "continuations".

I suspect you will need to run this for each partition separately, and then sum the results in your client code.