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

score 6 · Accepted Answer · answered Nov 29 '19 at 12:18

6

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"

answered Nov 29 '19 at 12:18

David Makogon

69,407
21
141
189

score 1 · Answer 2 · answered Nov 29 '19 at 11:47

1

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

answered Nov 29 '19 at 11:47

knutole

1,709
2
22
41

How to query CosmosDB for nested object value

2 Answers2