Cosmos DB SQL API query for children of nested objects

Question

I would like to find a better way to search for if documents in a collection have a property with more than 0 elements in the array, i.e. anything that isn't empty.

such as: select * from c where c.property = 'x' and array_length(c.child) > 0 and array_length(c.child.grandchild) > 0

The first arraylength works. Adding the second with just this dot notation doesn't work as I read somewhere else. How can I ensure that I can accomplish this. The grandchild will be anywhere from 0 to many number where it has a greater array length than 0.

Please let me know if more clarification is needed.

Jay Gong Jay Gong · Accepted Answer · 2018-08-15T03:05:39

Please use below sql :

SELECT distinct c.id,c.name,c.child FROM c
join child in c.child 
 where array_length(c.child) > 0 
and array_length(child.grandchild) > 0

My sample documents:

[
    {
        "id": "1",
        "name": "Jay",
        "child": [
            {
                "name": "A",
                "grandchild": [
                    {
                        "name": "A1"
                    },
                    {
                        "name": "A2"
                    }
                ]
            },
            {
                "name": "B",
                "grandchild": [
                    {
                        "name": "B1"
                    },
                    {
                        "name": "B2"
                    }
                ]
            }
        ]
    },
    {
        "id": "2",
        "name": "Tom",
        "child": [
            {
                "name": "A",
                "grandchild": []
            },
            {
                "name": "B",
                "grandchild": []
            }
        ]
    }
]

Hope it helps you.

Cosmos DB SQL API query for children of nested objects

1 Answers