Azure Cosmos DB vNext Emulator: Query and Observability Enhancements

Source: Dev.to

Query Improvements

This emulator release enables several query patterns that were previously unsupported. In this post, we’ll focus on the following enhancements to query capabilities:

• Improved JOIN Operations – Support for nested JOINs across multiple array levels, cross‑product JOINs, and self‑JOINs on primitive arrays
• Enhanced Operator Support – Support for string manipulation functions (CONCAT, LENGTH) and array operations (ARRAY_LENGTH, direct array indexing)
• Better Subdocument Handling – Improved querying of deeply nested object properties and proper handling of missing properties

Let’s explore these with practical examples.

Improved JOINs

In Azure Cosmos DB, JOINs enable you to flatten and traverse data within documents, allowing you to work with nested and hierarchical data structures.

Below is a sample family dataset where each document represents a family with nested arrays. The dataset includes three families (Andersen, Wakefield, and Miller) with varying numbers of children and pets, demonstrating how JOINs handle different data structures.

[
    {
        "id": "AndersenFamily",
        "lastName": "Andersen",
        "parents": [
            {"firstName": "Thomas", "relationship": "father"},
            {"firstName": "Mary Kay", "relationship": "mother"}
        ],
        "children": [
            {
                "firstName": "Henriette",
                "grade": 5,
                "pets": [{"name": "Fluffy", "type": "Rabbit"}]
            }
        ],
        "tags": ["seattle", "active", "family-friendly"],
        "address": {"state": "WA", "city": "Seattle"}
    },
    {
        "id": "WakefieldFamily",
        "lastName": "Wakefield",
        "parents": [
            {"firstName": "Robin", "relationship": "mother"},
            {"firstName": "Ben", "relationship": "father"}
        ],
        "children": [
            {
                "firstName": "Jesse",
                "grade": 8,
                "pets": [
                    {"name": "Goofy", "type": "Dog"},
                    {"name": "Shadow", "type": "Horse"}
                ]
            },
            {"firstName": "Lisa", "grade": 1, "pets": []}
        ],
        "tags": ["newyork", "urban"],
        "address": {"state": "NY", "city": "New York"}
    },
    {
        "id": "MillerFamily",
        "lastName": "Miller",
        "parents": [{"firstName": "David", "relationship": "father"}],
        "children": [
            {
                "firstName": "Emma",
                "grade": 6,
                "pets": [{"name": "Whiskers", "type": "Cat"}]
            }
        ],
        "tags": ["boston", "academic"],
        "address": {"state": "MA", "city": "Boston"}
    }
]

Basic JOIN – Flattening Arrays

Generate a list of all children across families (e.g., for a school roster) by flattening the children array:

SELECT f.id, f.lastName, c.firstName, c.grade 
FROM f 
JOIN c IN f.children

Result: one row per child – Henriette (Andersen), Jesse & Lisa (Wakefield), Emma (Miller).

JOIN with Filter

Find children within a specific grade range (e.g., middle‑school students, grade ≥ 6):

SELECT f.id, f.lastName, c.firstName, c.grade 
FROM f 
JOIN c IN f.children 
WHERE c.grade >= 6

Result: Jesse (grade 8) and Emma (grade 6) only.

Nested JOIN – Traversing Hierarchical Data

Identify all pets and their owners by chaining JOINs through children → pets:

SELECT f.id, c.firstName AS child, p.name AS pet, p.type 
FROM f 
JOIN c IN f.children 
JOIN p IN c.pets

Result:

• Henriette – Fluffy (Rabbit)
• Jesse – Goofy (Dog) & Shadow (Horse)
• Emma – Whiskers (Cat)

(Lisa is omitted because her pets array is empty, demonstrating inner‑join behavior.)

Cross‑Product JOIN – Combining Independent Arrays

Pair each child with every family tag to analyze patterns or preferences:

SELECT f.id, c.firstName AS child, t AS tag 
FROM f 
JOIN c IN f.children 
JOIN t IN f.tags

Result:

• Henriette appears three times (tags: “seattle”, “active”, “family‑friendly”)
• Jesse & Lisa each appear twice (tags: “newyork”, “urban”)
• Emma appears twice (tags: “boston”, “academic”).

JOIN on Primitive Arrays

Flatten simple value arrays such as tags:

SELECT f.id, f.lastName, t AS tag 
FROM f 
JOIN t IN f.tags

Result: each tag becomes its own row linked to the corresponding family.

Self‑JOIN – Finding Combinations

Discover which tags frequently appear together by joining the same array with different aliases:

SELECT f.id, t1 AS tag1, t2 AS tag2 
FROM f 
JOIN t1 IN f.tags 
JOIN t2 IN f.tags 
WHERE t1 < t2

Result:

• Andersen family yields three unique pairs: (“active”, “seattle”), (“active”, “family‑friendly”), (“family‑friendly”, “seattle”)
• Wakefield and Miller families each yield one pair from their two tags.

Complex Filters – Multi‑Level Conditions

Combine root‑level properties with deeply nested array elements. For example, find all rabbits owned by families located in Washington state:

SELECT f.id, f.lastName, c.firstName AS child, p.name AS pet, p.type 
FROM f 
JOIN c IN f.children 
JOIN p IN c.pets 
WHERE f.address.state = "WA" AND p.type = "Rabbit"

(Continue building queries as needed for your specific scenarios.)