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

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"

Solution

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.