Not able to query cosmos database

[tkumark]

trafficstars

When I run the below code I do not get any output. am I doing anything wrong? also for another type of collection I get 400 Bad Request. I have added script on the cosmos database I have used.

$cosmosAccount = "Cosmosdev"
$cosmosDb = "dbDev"
$resourceGroup = 'cosmosdev'
$collection = 'Grade'

$primaryKey = ConvertTo-SecureString -String 'secureString' -AsPlainText -Force

$cosmosDbContext = New-CosmosDbContext -Account $cosmosAccount -Database $cosmosDb -Key $primaryKey

#$cosmosDbContext = New-CosmosDbContext -Account $cosmosAccount -Database $cosmosDb -ResourceGroupName $resourceGroup -MasterKeyType PrimaryReadonlyMasterKey

$query = "SELECT VALUE COUNT(1) FROM c"
$val = Get-CosmosDbDocument -Context $cosmosDbContext -CollectionId $collection -Query $query

Output:

Id Etag ResourceId Timestamp Uri

---

               01-01-1970 5:30:00    

Cosmos Creation

$resourceGroupName='mytestcosmos'
$accountName='testcosmosaccount' 
az group create -l westus -n $resourceGroupName
az cosmosdb create --name $accountName --resource-group $resourceGroupName 


$databaseName='database1'

az cosmosdb database create -n $accountName -g $resourceGroupName -d $databaseName --throughput 1000

$collectionName = 'collection1'
$partitionKey = '/name'

az cosmosdb collection create -g $resourceGroupName -n $accountName -d $databaseName -c $collectionName --partition-key-path $partitionKey

[PlagueHO]

Hi @tkumark - I'll take a look at this and see if I can find any issues with it. Sorry about the delay.

[tkumark]

Thanks you looking into it have updated my comments.

[markwragg]

I'm trying to do this also but it seems like maybe the REST API doesn't support aggregates (which is what the COUNT function is). With -Verbose on, I see the underlying error returned is:

VERBOSE: {"code":"BadRequest","message":"The provided cross partition query can not be directly served by the gateway. This is a first chance (internal) exception that all newer clients will know how to handle gracefully. This exception is traced, but unless you see it bubble up as an exception (which only happens on older SDK clients), then you can safely ignore this message.\r\nActivityId: fdd81336-9e1e-4fcb-8896-eea65c1f84cc, Microsoft.Azure.Documents.Common/2.10.0","additionalErrorInfo":"{"partitionedQueryExecutionInfoVersion":2,"queryInfo":{"distinctType":"None","top":null,"offset":null,"limit":null,"orderBy":[],"orderByExpressions":[],"groupByExpressions":[],"groupByAliases":[],"aggregates":["Count"],"groupByAliasToAggregateType":{},"rewrittenQuery":"SELECT VALUE [{\"item\": count(1)}]\nFROM c","hasSelectValue":true},"queryRanges":[{"min":"","max":"FF","isMinInclusive":true,"isMaxInclusive":false}]}"}

That led me here: https://docs.microsoft.com/en-us/rest/api/cosmos-db/querying-cosmosdb-resources-using-the-rest-api#queries-that-cannot-be-served-by-gateway

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

TOP ORDER BY OFFSET LIMIT Aggregates DISTINCT GROUP BY

There is this example of getting the query to run via a C# app:

https://microsoft.github.io/AzureTipsAndTricks/blog/tip152.html

But I think that maybe isn't using the REST API.

[PlagueHO]

What I think I'll need to do here is catch this exception and bubble it up with a more useful message. I could try and prevent the SQL from being executed in the first place on these conditions, but that will likely be a bit hit and miss. So catching the exception is probably the best method.