Azure Cosmos DB SQL API nested arrays query

Question

I need help with the following issue. I have the following JSON: (https://pastecode.io/s/sg2kJB0PLD)

"cities": 
[  
    {
        "name" : 'NYC',
        "neighbourhoods":
        {
            "buildings":
            [
                {
                  "grade": "A",
                    
                },
                {
                  "grade": "B"
                },
                {
                  "grade": "C"
                }
            ]
        }
    },
    {
        "name" : 'CHICAGO',
        "neighbourhoods":
        {
            "buildings":
            [
                {
                  "grade": "D",
                    
                },
                {
                  "grade": "E"
                },
                {
                  "grade": "F"
                }
            ]
        }
    }
]

I need to check if any building has a grade that equals "F" by using SQL Query over Cosmos Db.

Any resources will be greatly appreciated.

Thanks

Answer 1

You can use ARRAY_CONTAINS function to achieve this.

Something like this SQL:

SELECT value r 
FROM r IN c.cities 
WHERE Array_contains(r.neighbourhoods.buildings,{"grade":"F"},true)

Result:

[
    {
        "name": "CHICAGO'",
        "neighbourhoods": {
            "buildings": [
                {
                    "grade": "D"
                },
                {
                    "grade": "E"
                },
                {
                    "grade": "F"
                }
            ]
        }
    }
]