search

PlagueHO / CosmosDB

PowerShell Module for working with Azure Cosmos DB databases, collections, documents, attachments, offers, users, permissions, triggers, stored procedures and user defined functions.
http://dscottraynsford.com
MIT License
152 stars 46 forks source link

Get-CosmosDbDocument not able to query certain items from a container #465

Open Sachin27j opened 1 year ago

Sachin27j commented 1 year ago

Version - PowerShell 5.1 Host - ISE, Also tested on Azure Automation Account OS - Windows 10 CosmosDb Powershell version - 4.6.0

The following is returning me 0 items even though data is there in cosmosdb. Upon changing the value in the filter for c.dwnStrmAppName to something else, it does give me an output. Essentially, the cmd is working for some items with a certain partitionKey in cosmosdb but not working for others.

(Get-CosmosDbDocument -Context $newcosmosDbContext -CollectionId $cd_containerName -Query "SELECT c.createdOn FROM c WHERE c.env='preprod' and c.action='SPN Creation' and c.status='Success' and lower(c.dwnStrmAppName)=lower('XYZ')" -QueryEnableCrossPartition 1).createdOn |Select-Object -Last 1

Sachin27j commented 1 year ago

Did some more digging, apparently the query is only running for one physical partition, while there exists 2 partitions for the collection I am running it for. Anyone with any information related to this?

PlagueHO commented 1 year ago

Hi @Sachin27j - Because you're doing a cross partition query (using the -QueryEnableCrossPartition parameter), it should be executing the query on all physical partitions. So, I don't see why your query wouldn't return the results you expect.

Have you run the same query in the Azure Portal in the Cosmos DB explorer - and do you get the same output?

IAmWhitBran commented 1 year ago

Hi, I am also getting this issue (or similar). I have run the same query from the Azure portal as I have in my powershell instance. Powershell returns no record, Azure portal returns 1.

EDIT: To update further, I can get the record to return by specifying the document id and partition key, though this does defeat the point of my cross partition query...