Using Reserved Word Field Name in Documentdb

Cosmos DB SQL API - How to query a field name that uses a reserved word

top is a reserved keyword. To escape this use [""] syntax.

SELECT  c.code,c["top"] FROM c

Unable to fetch results when JSON includes sql keywords

Your query should use [] instead of dot when you are querying nested objects

Square bracket notation lets you to access properties containing special characters

SELECT * 
FROM Rx c 
where c.tenantId = '5c6cb2d77c1c2edc001b9007' AND 
      c["left"]["series"] ='Clariti 1 Day Toric 30pk'

DEMO

DocumentDb - Is Index a reserved word?

No, index is not reserved. You should be able to use this.

AWS Athena: How to escape reserved word column name and cast to integer

The problem is not your escaping logic, but that your dataset contains empty strings in the timestamp column which can not be casted. You can avoid that, by filtering out the empty string records.

SELECT "timestamp"
FROM (SELECT "timestamp" from my_table where "timestamp" != '')
WHERE from_unixtime(cast("timestamp" as integer)) >= date_add('day', -7, now())

DocumentDb cannot handle hyphen (-) in column names

Well the trick was to use CollectionName.DocumentName instead of just the DocumentName, like this (thanks to @Laan for pointing me in that direction) :):

SELECT * FROM TestProject.DocumentDbTest_Countries c where c["@country"] = "C26092630539"

But then I still miss the Document.Id and Document.SelfLink data in the return Document data.

In Azure DocumentDB some queries don't work

Value is a keyword in DocumentDB syntax so this is why you get an error. See Value keyword in this article DocumentDB syntax - The VALUE keyword provides a way to return JSON value.

To get around this you can query it the way Yannick has said i.e.

SELECT C['Value'] FROM C

Return the content of a specific object in an array — CosmosDB

Your idea and direction is right absolutely, I simplified and tested your sql.

SELECT detail.value  FROM c 
join detail in c.EventType.EndDeviceEventDetail
WHERE c.EventType.EndDeviceEventType.eventOrAction = '93'
AND ARRAY_CONTAINS(c.EventType.EndDeviceEventDetail,{"name": 
"RCDSwitchReleased","value": "true" })

Found the error message as below:

It because that the value is the reserved word in cosmos db sql syntax,please refer to this case:Using reserved word field name in DocumentDB

You could try to modify the sql like:

SELECT detail["value"]  FROM c 

How to use SQL Query with hyphen in Collection name?

When you query via DocumentDB SQL, you don't need to specify the exact collection name; you're really specifying an alias in your query.

In the Query Explorer (via the portal), all queries are done by first selecting a collection. At that point, use a simple alias to query. For example, here is a query against a collection called sample-data (as selected in the "Collections" dropdown), but I don't need to specify the actual collection name in the query. I simply use mycollection as an alias for sample-data:

And in code, the SDK call includes both a database parameter and a collection parameter, again obviating the need for the actual collection name in the query. For example, the same query, called from c#, might look something like this:

client.CreateDocumentQuery<MyObject>(
            UriFactory.CreateDocumentCollectionUri("mydb", "sample-data"),
            "SELECT * FROM mycollection WHERE mycollection.id = '3829503'");

---

Related Topics