How to query CosmosDB for nested object value

Question

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.)

Answer 1

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"

Answer 2

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