Cosmos DB - Select root document based on child data

Question

Sorry if this is a newbie question, but I am a newbie to Cosmos DB.

I am trying to select all the root documents from my collection where there a child element matches specified (multiple) criteria.

Lets assume you have an ORDER document, which has ORDERITEMS as sub-data document, what I need to do is to query all the orders where a particular product has been ordered, and to return the whole order document.

[
    {
        "order": {
            "id": "1",
            "orderiems": [
                {
                    "partcode": "A",
                    "qty": "4"
                },
                {
                    "partcode": "B",
                    "qty": "4"
                },
                {
                    "partcode": "C",
                    "qty": "4"
                }
            ]
        }
    },
    {
        "order": {
            "id": "2",
            "orderiems": [
                {
                    "partcode": "A",
                    "qty": "4"
                },
                {
                    "partcode": "B",
                    "qty": "4"
                },
                {
                    "partcode": "A",
                    "qty": "4"
                }
            ]
        }
    },
    {
        "order": {
            "id": "3",
            "orderiems": [
                {
                    "partcode": "A",
                    "qty": "1"
                }
            ]
        }
    }
]

My query is

SELECT order from order
JOIN items in order.orderitem
WHERE item.partcode = '<mypartcode>
  AND item.qty > 1

Now, this sort of works and returns me the orders, but it is returning

• id: 1
• id: 2
• id: 2 << repeated

because id: 2 has two of the same item.... id: 3 excluded because it's only 1 item

In normal SQL Server SQL I would simply have

SELECT *
  from Orders o 
 where exists (select 1 
                 from OrderItems oi 
                where oi.ordID = o.ID 
                  and oi.partcode = 'A'
                  and oi.qty > 1)

How can I stop the duplication please

Please note that the above is a hand-crafted representation to simplify the problem as the document model I am actually working on a extremely large

Answer 1

With the current version of the Azure Cosmos DB SQL API you can use some of these:

SELECT distinct VALUE order 
FROM order
JOIN item in order.orderitems
WHERE item.partcode = '<Partcode>'
  AND item.qty > 1

Or:

SELECT order 
FROM order
WHERE EXISTS (
    SELECT NULL 
    FROM item IN order.orderitems
    item.partcode = '<Partcode>'
    AND item.qty > 1
)

Answer 2

Cosmos DB now supports the DISTINCT keyword and it will actually work on document use cases such as yours.