Reputation: 608
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
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
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
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