Trying to query a CosmosDB container from Powrshell and getting "Cross partition query is required but disabled"

Question

Hi All,

I'm trying to query a CosmosDB from Powershell to look through all items that have a Status of Pending.

I can make the connection and query the db when I filter against a particular id using the query...

SELECT * FROM c WHERE c.id = 'testuser' AND c.Status = 'Pending'.

However, when I try to retrieve all users with a Status of Pending using the following query...

SELECT * FROM c WHERE c.Status = 'Pending'

it fails with an error about "Invoke-WebRequest : {"code":"BadRequest","message":"Cross partition query is required but disabled. Please set x-ms-documentdb-query-enablecrosspartition to true, specify x-ms-documentdb-partitionkey, or revise your query to avoid this exception".  

I've checked the settings in the CosmosDB UI and I can confirm that "Enable cross-partition query" is enabled and when I run the query in the Data Explorer UI it works, but when using the following command in PS...

Get-CosmosDbDocument -Context $cosmosDbContext -CollectionId $cosmosDBCollectionID -Query $query

it fails. Any suggestions?

Regards

M.

Answer 1

@Michael Lalor Thank you for using Azure services.

My understanding is that you are getting an error while running a query in Azure Cosmos DB "cross partition key is required by disabled."

Cross-partition queries is a header, the error is saying it, you need to add the header if you expect the query to be cross partition.

You will need to add the "x-ms-documentdb-query-enablecrosspartition" header to the request with the "true" value.

See here https://learn.microsoft.com/en-us/rest/api/cosmos-db/query-documents

Hope that answered your question.

Please do not forget to mark as accept answer if the reply was helpful. This will be useful for other community members facing the same issue.

Regards,

Oury

Answer 2

Hi All,

I resolved the issue I was having by adding "-QueryEnableCrossPartition $True" to the powershell command. The following Powershell Command will query the cosmosDB and return the results as expected.

Get-CosmosDbDocument -Context $cosmosDbContext -CollectionId $cosmosDBCollectionID -Query $query1 -QueryEnableCrossPartition $True

Regards

M.