CODEWITHSUNDEEP
azureazure-cosmosdbazure-cosmosdb-sqlapi
knutole
knutole

Reputation: 1787

How to query CosmosDB for nested object value

How can I retrieve objects which match order_id = 9234029m, given this document in CosmosDB:

{
    "order": {
        "order_id": "9234029m",
        "order_name": "name",
    }
}

I have tried to query in CosmosDB Data Explorer, but it's not possible to simply query the nested order_id object like this: 

SELECT * FROM c WHERE c.order.order_id = "9234029m"

(Err: "Syntax error, incorrect syntax near 'order'")

This seems like it should be so simple, yet it's not! (In CosmosDB Data Explorer, all queries need to start with SELECT * FROM c, but REST SQL is an alternative as well.)

Upvotes: 9

Views: 7778

Answers (2)

David Makogon
David Makogon

Reputation: 71118

As you discovered, order is a reserved keyword, which was tripping up the query parsing. However, you can get past that, and still query your data, with slightly different syntax (bracket notation):

SELECT *
FROM c
WHERE c["order"].order_id = "9234029m"

Upvotes: 7

knutole
knutole

Reputation: 1787

This was due, apparently, to order being a reserved keyword in CosmosDB SQL, even if used as above.

Upvotes: 1

Related Questions