Need help with a a very simple query "select * from c"

[sdg002]

Issue

Before submitting your issue for the CosmosDB project, please take a moment to
provide the following details:

• [5.1.1 ] Version of PowerShell you're using
• [ ISE] PowerShell host you're using (eg. Console Host, ISE, Visual Studio)
• [ Windows 10] Operating system you're running
• [ 3.7.0] Version of CosmosDB PowerShell Module you're using (use Get-Module -Name CosmosDB)

Thanks for contributing your feedback and support! You can optionally submit a
Pull Request against this project, if you have a fix you'd like to share.

Thank you very much for this utility. This is going to be very useful.

What works

Get-CosmosDbDocument -Context $cosmosDbContext -CollectionId "v2participantprofiles"

Got all the records from the container.

What did not work

Command

Get-CosmosDbDocument -Context $cosmosDbContext -CollectionId "v2participantprofiles" -Query "select c.id from c" 

Output

Invoke-WebRequest : The remote server returned an error: (400) Bad Request.
At C:\Users\corresilience\Documents\WindowsPowerShell\Modules\CosmosDB\3.7.0\CosmosDB.psm1:1171 char:30
+ ...        $requestResult = Invoke-WebRequest @invokeWebRequestParameters
+                             ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (System.Net.HttpWebRequest:HttpWebRequest) [Invoke-WebRequest], WebException
    + FullyQualifiedErrorId : WebCmdletWebResponseException,Microsoft.PowerShell.Commands.InvokeWebRequestCommand

No query appears to work. e.g.
select * from c

[PlagueHO]

Hi @sdg002 - that should work. But I just noticed I don't have an integration test that validates this. So let me add that and see if I can replicate your issue. I'll work on this tonight.

[PlagueHO]

Hi @sdg002 , I've added the additional tests for this and can confirm querying is working correctly. However, I suspect your query needs some adjustment.

Can you try:
"SELECT c.id FROM docs c"

From memory, when you run queries in the Azure Portal, it does some adjustments to the query to reduce typing.

[PlagueHO]

Sorry, got closed when I merged the new tests.

[sdg002]

Thanks for looking into this. I updated the module by running the UPDATE-MODULE command and this brough the module version to 4.0.0

I am able to retrieve the list of collection details, but could not retrieve documents.

Is there any specific way I need to create the context object? I have used the -Database, -Key and -Account parameters while calling New-CosmosDbContext cmdlet.

Thank you,
Sau

[PlagueHO]

Hi @sdg002 - I think your $context object must be correct because you're able to get document (without using Query). Using the -Database, -Key and -Account parameters is enough to query a collection (you can omit the -Database in the context, but then must specify it in the Get-CosmosDbDocument cmdlet).

The queries you are specifying do look like they should work just fine.

One thing I've just considered: Is your collection partitioned? If so your query will be a cross partition query: In which case you need to include the -QueryEnableCrossPartition parameter.

If you run the command with the -Verbose parameter you'll get back a deeper level of info about the exception returned by CosmosDB. If this is a partition issue then it'll usually tell you in the -Verbose output.

[sdg002]

Setting this parameter worked. Thanks for the tip

-QueryEnableCrossPartition $true

[tamusjroyce]

Requesting this be added to documentation. This was super helpful!

[PlagueHO]

Will do @tamusjroyce