CODEWITHSUNDEEP
azure-cosmosdbazure-cosmosdb-sqlapi
schwifty
schwifty

Reputation: 185

CosmosDB filter based on array property, Greater than

I have an item in a container of the structure:

{
    "order_id": 6,
    "customer_id": 94,
    "order_status": 4,
    "order_date": "2016-01-04",
    "required_date": "2016-01-07",
    "shipped_date": "2016-01-05",
    "store_id": 2,
    "staff_id": 6,
    "order_details": [
        {
            "order_id": 6,
            "item_id": 1,
            "product_id": 18,
            "quantity": 1,
            "list_price": 449,
            "discount": 0.07
        },
        {
            "order_id": 6,
            "item_id": 2,
            "product_id": 12,
            "quantity": 2,
            "list_price": 549.99,
            "discount": 0.05
        },
        {
            "order_id": 6,
            "item_id": 3,
            "product_id": 20,
            "quantity": 1,
            "list_price": 599.99,
            "discount": 0.1
        },
        {
            "order_id": 6,
            "item_id": 4,
            "product_id": 3,
            "quantity": 2,
            "list_price": 999.99,
            "discount": 0.07
        },
        {
            "order_id": 6,
            "item_id": 5,
            "product_id": 9,
            "quantity": 2,
            "list_price": 2999.99,
            "discount": 0.07
        }
    ],
    "id": "63bfc98b-d7d1-4090-b293-305980e626b0",
    "_rid": "7mM8AIbq5iEEAAAAAAAAAA==",
    "_self": "dbs/7mM8AA==/colls/7mM8AIbq5iE=/docs/7mM8AIbq5iEEAAAAAAAAAA==/",
    "_etag": "\"00000000-0000-0000-248e-8dec8fa101d8\"",
    "_attachments": "attachments/",
    "_ts": 1645164769
}

I want to filter items based on 'order_details'. I can filter based on 'item_id' with something like this:

SELECT * FROM c
where ARRAY_CONTAINS(c.order_details,{"item_id":1},true)

If I want to achieve filtering out items with discount (let's say) >0.04. How can this be achieved?

Upvotes: 1

Views: 884

Answers (1)

Sajeetharan
Sajeetharan

Reputation: 222722

What you need is JOIN query

SELECT c
FROM c
JOIN details IN c.order_details
WHERE details.discount > 0.05

you need to format in the way you need the output!

Upvotes: 1

Related Questions