Query Azure Cosmos DB for NoSQL

Azure Cosmos DB for NoSQL supports a rich SQL dialect for querying JSON documents. This makes it intuitive for developers to query hierarchical, schema-free data. Understanding how these queries execute across the distributed database infrastructure is critical for optimizing performance and Request Unit (RU) consumption.

Basic Queries and Filtering

The basic structure of a query in Cosmos DB uses standard SQL syntax. You can filter results using the WHERE clause:

SELECT c.id, c.title, c.content
FROM c
WHERE c.categoryId = "AI_Docs"

(Here c is an alias for the container.)

Parameterized Queries

When querying from an application, always use parameterized queries. This prevents SQL injection vulnerabilities and allows the Cosmos DB engine to cache and reuse the query execution plan.

query = "SELECT * FROM c WHERE c.categoryId = @categoryId"
parameters = [
    {"name": "@categoryId", "value": "AI_Docs"}
]

items = list(container.query_items(
    query=query,
    parameters=parameters,
    enable_cross_partition_query=False
))

Data Shaping and Projections

Cosmos DB allows structural transformation of returned JSON documents using projections. The SELECT clause can define custom JSON object structures or extract scalar arrays using the VALUE keyword, reducing network payload size and client-side deserialization overhead.

SELECT VALUE { 
    "documentId": c.id, 
    "isPublished": c.status = "published"
} 
FROM c

Aggregation Functions

The SQL dialect includes standard aggregate functions (COUNT, SUM, AVG, MIN, MAX). These functions execute cross-document statistical computations directly on the database engine. Since aggregations require scanning all documents matching the filter criteria, they consume Request Units proportionally to the volume of evaluated data.

SELECT VALUE COUNT(1) 
FROM c 
WHERE c.categoryId = "AI_Docs"

Array Querying

NoSQL documents frequently model one-to-many relationships as embedded arrays. The ARRAY_CONTAINS system function evaluates array membership. For complex intra-array filtering, the JOIN operator computes a cartesian product between the parent document and its array elements, effectively flattening the structure for standard evaluation.

SELECT c.title, tag 
FROM c 
JOIN tag IN c.tags 
WHERE tag IN ("python", "azure")

Cost Monitoring and Diagnostics

Resource consumption in Cosmos DB is measured in Request Units (RUs). The RU charge for every query execution is deterministically calculated based on CPU, memory, and IO utilization. The gateway returns this exact numerical cost in the x-ms-request-charge HTTP response header for telemetry and cost optimization.

# Intercepting RU charges from SDK response headers
headers = container.client_connection.last_response_headers or {} 
page_rus = float(headers.get('x-ms-request-charge', 0)) 

Query Execution Architecture

To optimize query performance, you must understand how Cosmos DB routes requests to its underlying physical partitions.

1. Single-Partition Query Routing

Technical Scenario: An application querying for a specific category's documents, where categoryId is the configured partition key for the container.

Execution Flow:

1. The Python SDK sends the query and explicitly includes the partition key value in the request metadata.
2. The Cosmos DB Gateway evaluates the partition key against its internal routing table to compute the exact physical partition containing the target data.
3. The query is routed directly to that single physical node, which executes the query locally against its data engine.
4. The results are returned to the client. This is the most efficient execution path, consuming the minimum necessary CPU, IO, and Request Units (RUs).

2. Cross-Partition (Fan-Out) Query Routing

Technical Scenario: An application querying for all documents published in the last 24 hours across the entire system, without filtering by the categoryId partition key.

Execution Flow:

1. The client issues a query that does not specify a partition key.
2. Because the Gateway cannot determine which physical partition holds the matching data, it broadcasts (fans out) the query to all active physical partitions in the container.
3. Each physical node executes the query against its local data and returns the partial results to the Gateway.
4. The Gateway buffers the incoming data in memory. If an ORDER BY clause is present, the Gateway performs a distributed merge-sort across all result sets.
5. The final aggregate result set is returned to the client. This operation scales poorly as data volume and physical partition count increase, resulting in high RU consumption and elevated latency.

3. Pagination and State Management

Technical Scenario: A REST API endpoint returning a large dataset of documents to a frontend application, requiring chunked responses to prevent memory exhaustion and timeout errors.

Execution Flow:

1. The client issues a query specifying a max_item_count limit.
2. The database engine executes the query, retrieving the requested block of items. Crucially, it generates a ContinuationToken. This token is an encoded string containing the internal execution state and index pointer where the database paused execution.
3. The database returns the items and the token, then terminates the operation. Cosmos DB does not maintain persistent connection state between requests.
4. When the client requires the next block of data, it re-issues the identical query, appending the ContinuationToken in the request headers.
5. The database decodes the token, resumes execution from the precise index pointer, and returns the next block alongside a new token.