How to query nested array with Cosmos DB

There is actually a really good doc for this here: https://learn.microsoft.com/en-us/azure/cosmos-db/sql-query-join and here: https://learn.microsoft.com/en-us/azure/cosmos-db/sql-query-object-array#arrays

For arrays, you want to utilize nested joins, like so:

Given:

{
    "id": "so-test",
    "Families": [
        {
            "Name": "Smith",
            "Children": [
                {
                    "Name": "Bob",
                    "Pets": [
                        {
                            "Name": "Oscar",
                            "Type": "Cat"
                        },
                        {
                            "Name": "Otto",
                            "Type": "Dog"
                        }
                    ]
                },
                {
                    "Name": "Brittney",
                    "Pets": [
                        {
                            "Name": "Isolde",
                            "Type": "Dog"
                        },
                        {
                            "Name": "Ignatz",
                            "Type": "Turtle"
                        }
                    ]
                }
            ]
        },
        {
            "Name": "Miller",
            "Children": [
                {
                    "Name": "Alex",
                    "Pets": [
                        {
                            "Name": "Elvis",
                            "Type": "Horse"
                        }
                    ]
                }
            ]
        }
    ]
}

You can use this query for A:

SELECT 
    f.Name as FamilyName,
    c.Name as KidName,
    p.Name as Petname
FROM d
JOIN f IN d.Families
JOIN c IN f.Children
JOIN p IN c.Pets
WHERE d.id = "so-test"

and you'll get this result:

[
    {
        "FamilyName": "Smith",
        "KidName": "Bob",
        "Petname": "Oscar"
    },
    {
        "FamilyName": "Smith",
        "KidName": "Bob",
        "Petname": "Otto"
    },
    {
        "FamilyName": "Smith",
        "KidName": "Brittney",
        "Petname": "Isolde"
    },
    {
        "FamilyName": "Smith",
        "KidName": "Brittney",
        "Petname": "Ignatz"
    },
    {
        "FamilyName": "Miller",
        "KidName": "Alex",
        "Petname": "Elvis"
    }
]

For B), use this query which adds the ARRAY method

SELECT 
    f.Name as FamilyName,
    c.Name as KidName,
    ARRAY(SELECT DISTINCT VALUE p.Name from p IN c.Pets) as Petname
FROM d
JOIN f IN d.Families
JOIN c IN f.Children
WHERE d.id = "so-test"

you'll get these results:

[
    {
        "FamilyName": "Smith",
        "KidName": "Bob",
        "Petname": [
            "Oscar",
            "Otto"
        ]
    },
    {
        "FamilyName": "Smith",
        "KidName": "Brittney",
        "Petname": [
            "Isolde",
            "Ignatz"
        ]
    },
    {
        "FamilyName": "Miller",
        "KidName": "Alex",
        "Petname": [
            "Elvis"
        ]
    }
]