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!

Evangelineevangelism

Have you tried square brackets? Alternates.[Value] = "NOCALL" – Tweeze

yes, I have: Syntax error, incorrect syntax near '['. – Evangelineevangelism

Seeing exact same issue for column name Order – Suanne

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'

Rident

Thanks Ryank for giving this, It saved a lot of time in refactoring the performance of my automation scripts, Can you please share the reference as well from where you learned this trick – Bookstore

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

Overweening