How can I query nested arrays in Data Explorer?

Question

I am trying to write a query in Data Explorer over a Cosmos DB to give me a list of results where the order has a discount applied. That requires that I examine every element of the Totals array for a Discounts element that is not empty.

I've tried to use ARRAY_LENGTH within ARRAY_CONTAINS as shown below and that didn't return a result set. I know the ARRAY_CONTAINS is use to look for a field value within an array, but I was hoping that it would accept ARRAY_LENGTH command.

SELECT * FROM c where ARRAY_CONTAINS(c.OrderHeader.Totals,{ARRAY_LENGTH(Discounts):1},true))

I've also tried to check for a value in CampaignId field of the Discounts array using the following query. It didn't return a result set.

SELECT * FROM c where ARRAY_CONTAINS(c.OrderHeader.Totals.Discounts,{CampaignId:null},false)

I would assume there's a way to do this, so any input would be greatly appreciated!

{
    "OrderHeader": {

        "Totals": [
            {
                "Currency": "CAD",
                "Price": 10.00,
                "Discounts": []
            },
            {
                "Currency": "CAD",
                "Price": 20.00,
                "Discounts": []
            },
            {
                "Currency": "CAD",
                "Price": 30.00,
                    "Discounts": [
                        {
                            "CampaignId": "Campaign2",
                            "CouponDefinition": null,
                        }
                    ]
            }
}

I believe you need to JOIN to the totals array, SELECT * FROM c JOIN t IN c.OrderHeader.Totals WHERE ARRAY_CONTAINS(t.Discounts, {‘CampaignId’:null, false} you might need to play with the quoting of the null I am using an iPad so I cannot test until in office — Matt Douhan
Didn't seem to like that, Matt. No orders returned. I removed the quotes around CampaignId, but no luck. I'll keep on trying! Thanks again! — ChrisP
ill look properly when I get to office if no one else responds in the mean time pretty sure it’s a syntax thing, the join will get you into the array correctly — Matt Douhan

Steve Zhao Steve Zhao · Accepted Answer · 2020-05-22T01:54:10

Please try this sql:

SELECT t.Currency,t.Price,t.Discounts FROM c JOIN t 
IN c.OrderHeader.Totals WHERE ARRAY_LENGTH(t.Discounts) > 0

Here is the result:

[
    {
        "Currency": "CAD",
        "Price": 30,
        "Discounts": [
            {
                "CampaignId": "Campaign2",
                "CouponDefinition": null
            }
        ]
    }
]

Hope it can help you.

How can I query nested arrays in Data Explorer?

1 Answers