CODEWITHSUNDEEP
c#azureazure-cosmosdb
Krzysztof
Krzysztof

Reputation: 608

how to select from collection with guid id?

I've got this collection with guid id. When i use Azure Query Explorer with collection selected in the combo box above it's ok:

SELECT * FROM c

But when i try to select it like that (both from query exlorer and from c# code):

SELECT * FROM 357fa002-dc7d-4ede-935a-6a0c80cf9239 c

I get:

Syntax error, invalid numeric value token '357fa002'.

I've tried to put it in quotation marks (both single and double) around guid but with no success..

Microsoft docs state that this collection id doesn't break any rules: https://learn.microsoft.com/pl-pl/azure/documentdb/documentdb-create-collection

Collection names must be between 1 and 255 characters, and cannot contain / \ # ? or a trailing space

How can i query this collection using it's id?

Upvotes: 5

Views: 1377

Answers (3)

Krzysztof
Krzysztof

Reputation: 608

Bruce MSFT solved the puzzle. SELECT * FROM 357fa002-dc7d-4ede-935a-6a0c80cf9239 c can be executed like this:

var collectionId = "357fa002-dc7d-4ede-935a-6a0c80cf9239"
var uri = UriFactory.CreateDocumentCollectionUri(DatabaseId, collectionId);
var sql = string.Format("SELECT * FROM root c", attributeName);
var elements = client.CreateDocumentQuery(uri, sql).ToList();

Thanks!

Upvotes: 1

Bruce Chen
Bruce Chen

Reputation: 18435

According to your description, I checked the official document about FROM clause of DocumentDB SQL Syntax. Here is the syntax from the above document:

FROM <from_specification>  

<from_specification> ::=   
        <from_source> {[ JOIN <from_source>][,...n]}  

<from_source> ::=   
          <collection_expression> [[AS] input_alias]  
        | input_alias IN <collection_expression>  

<collection_expression> ::=   
        ROOT   
     | collection_name  
     | input_alias  
     | <collection_expression> '.' property_name  
     | <collection_expression> '[' "property_name" | array_index ']'

For <collection_expression>, we could specify the name of the collection currently connected to.

Based on your scenario, I tried it on my side and reproduced this issue. Also, I have tested this issue and found that the input_alias or collection_name could only made up of numbers and letters and the first must be a letter. Upon my test, I assumed that you couldn't achieve this purpose up to now. I would report this issue and you could add your feedback here.

UPDATE:

For the client libarary Microsoft Azure DocumentDB for C#, we could leverage Fiddler to capture the request when invoking DocumentClient.CreateDocumentQuery without specifying the sqlExpression parameter as follows:

var items =
    client.CreateDocumentQuery<GroupedSales>(UriFactory.CreateDocumentCollectionUri(DatabaseId,
        DocumentCollectionId)).Where(
            x =>
                x.Date >= filter.From.Date
                && x.Date <= filter.To.Date
                ).ToList();

Note: Without the Where clause, the request body would be empty. At this point, the query equals to "SELECT * FROM root".

Upvotes: 2

Aravind Krishna R.
Aravind Krishna R.

Reputation: 8003

In DocumentDB, queries are scoped to a single collection (not a database like in relational databases). In the REST API, queries are POST requests against the collection URI /dbs/<my-db>/colls/<my0coll>.

In the Azure portal's Query Explorer, you have to select the database/collection in the dropdown list, then query it.

Upvotes: 2

Related Questions