Using reserved word field name in DocumentDB

Question

I inherited a database loaded into DocumentDB, where field name happens to be "Value". Example of my structure is:

{
...
   "Alternates": [
      "Type": "ID",
      "Value" : "NOCALL"
   ]
}

when I query (using documentDB's SQL), trying to get back all documents where Alternates.Value = "NOCALL", I get syntax error near

"Value" error

. If I query for Type = "ID", it is all fine.

Seems that the word Value, having a special meaning on DocumentDB is causing an issue.

Putting punctuation (e.g. quotes/double quotes) around "Value" does not seem to help.

Any suggestion on how to resolve this will be much appreciated!

Thank you in advance!

Answer 1

You are correct. Value is a reserved keyword. To escape this use [""] syntax.

So in your case of

"Alternates": [ "Type": "ID", "Value" : "NOCALL" ]

SELECT c FROM c JOIN alt IN c.Alternates WHERE alt["Value"] = 'NOCALL'

Answer 2

In my case, the structure looks something like this - { "name": "ABC", "Value": 123 }.

I could escape the reserved keyword using [""] (as answered by others) along with <source_name> i.e.

SELECT c["Value"] FROM c -- 123

Ref.: Querying in Azure Cosmos DB