CODEWITHSUNDEEP
azureazure-cosmosdb
Kamal Rathnayake
Kamal Rathnayake

Reputation: 582

In Azure DocumentDB some queries don't work

I used Azure DocumentDB to store some Key-Values pairs. This is the structure of the document I used

{
    "Key": "Deleted",
    "Value": {
      "email": "[email protected]"
    }
}

When i write DocumentDB query like this,

SELECT C.Value FROM C

This query does not work. Here is the error message I get.

Syntax error, incorrect syntax near 'Value'.

But this query works fine,

SELECT C.Key FROM C

I understand 'Value' should be a keyword in azure documentdb. How can i query it?

Upvotes: 4

Views: 2510

Answers (3)

Shireesh Thota
Shireesh Thota

Reputation: 303

In DocumentDB query grammar, the syntax < collection_expresion >.property_name is exactly same as < collection_expression >["property_name"]. So, effectively,

c.name

is same as 

c["name"]

The syntax < collection_expression >["property_name"] allows one to derefence properties which might have non-identifier characters like 'my value' (space between my and value), or keywords like 'Value', 'Select' etc.

Hence, 

SELECT C["Value"] 
FROM C

will work in your case.

Upvotes: 4

majita
majita

Reputation: 1306

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

Upvotes: 11

Yannick Meeus
Yannick Meeus

Reputation: 5890

Because your documents are stored as JSON, you can access them as follows:

SELECT C['Value'] FROM C

This will result in:

[
  {
    "Value": {
      "email": "[email protected]"
    }
  }
]

Upvotes: 6

Related Questions