Cosmos DB's SQL API supports a special type of join operation called JOIN IN, which is specifically designed for working with nested arrays within documents. Unlike traditional SQL joins that combine data from separate tables, JOIN IN allows you to "flatten" and query nested array elements within a single document.

Document Structure Example

Consider a document in a 'restaurants' collection with the following structure:

        {
            "id": "3",
            "name": "DEV Park Bake Shop",
            "cuisine": "Bakery",
            "grades": [
                {
                    "date": 1393804800000,
                    "grade": "D",
                    "score": 2
                },
                {
                    "date": 1378857600000,
                    "grade": "A",
                    "score": 6
                }
            ]
        }
    

SQL Query Syntax

To query nested array elements, use the following SQL syntax:

        SELECT c.Id, g.grade, g.score, g.date
        FROM restaurants c
        JOIN g IN c.grades
        WHERE c.[name] = 'DEV Park Bake Shop'
    

CosmosDB Translation

The query is automatically translated to CosmosDB's SQL API format:

        SELECT c["Id"], g["grade"], g["score"], g["date"]
        FROM C AS c
        JOIN g IN c.grades
        WHERE c["name"] = "DEV Park Bake Shop"