Nested query not working in Cosmos DB more than 1 level deep

1
votes

I can't get Cosmos DB to find a record more than 1 level deep in a query:

Document:

{
    "CustomerId": 1,
    "Orders": [
        {
            "OrderId": 1,
            "OrderCode": "ONE",
            "OrderName": "Order One",
            "Products": [
                {
                    "ProductCode": "ONE",
                    "ProductName": "Product One"
                }
            ]
        }
    ]
}

Query: db.customers.find({ "Orders.Products.ProductCode": "ONE" })

It works in MongoDB shell, but not in CosmosDB. Am I missing something obvious?

2
mongodb-queryazure-cosmosdb

2 Answers

0
votes

I think the key of the issue is that there are nested arrays in your document, and the query you provide may only apply to the following document:

"CustomerId" : 1,
"Orders" : {
    "OrderId" : 1,
    "OrderCode" : "ONE",
    "OrderName" : "Order One",
    "Products" : {
        "ProductCode" : "ONE",
        "ProductName" : "Product One"
    }
}

You could refer to MongoDB official documentation and use $elemmatch operator to query fields in nested documents.

query:

{ "Orders": { $elemMatch: { Products : {$elemMatch : {ProductCode : "ONE" } } } } }

Hope it helps you.

0
votes

Cosmosdb does not have something to query in nested document. you need to use ARRAY_CONTAINS built-in function. So it will be something like this:

SELECT c.Orders.Products.ProductCode, c.Orders.Products.ProductName 
FROM c
WHERE c.Orders.Products.ProductCode = "ONE" or ARRAY_CONTAINS(c.Orders.Products, { "ProductCode": "ONE" })