CODEWITHSUNDEEP
azureazure-cosmosdb
Dinuka Wanasinghe
Dinuka Wanasinghe

Reputation: 775

How to write azure cosmos database complex select queries

As below image shows I have a collections of data in cosmos-db and I need to query those collection by order.id

Cosmos Document List Screenshot

{
    "id": "0f994473-5288-44e8-8bfd-a19445e6fb51",
    "ecom_id": "ECOM005",
    "create_date_time": "2018-07-04T05:30:41.6180888+00:00",
    "modify_date_time": "2018-07-04T06:14:35.6422331+00:00",
    "ecom": "",
    "status": "Shipped",
    "order": {
        "id": 549096652915,
        "email": "",
        "closed_at": null,
        "created_at": "2018-07-04T05:26:26+00:00",
        "updated_at": "2018-07-04T05:26:29+00:00",
        "number": 347,
        "note": null
    }
}

I wrote following query and it's gives an error 

SELECT * FROM NovaFulfillments f WHERE f.order.id = 549096652915

Cosmos Database Query Error Screenshot

Upvotes: 2

Views: 1881

Answers (1)

Jay Gong
Jay Gong

Reputation: 23782

Your order field name conflict with sql syntax keyword.(such as order,join,select,from etc.) This is the database field naming convention.

Please try below sql :

SELECT * FROM NovaFulfillments f WHERE f['order'].id = 549096652915

Syntax official doc: https://learn.microsoft.com/en-us/azure/cosmos-db/sql-api-sql-query#EscapingReservedKeywords

Hope it helps you.

Upvotes: 2

Related Questions