Skip to main content
Azure · lesson 12 of 19

Beginner

4

Intermediate

13

Advanced

2

Also part of Azure

5
Open the Azure page →
Azure intermediate Lesson 12 of 19

Azure Cosmos DB: Queries

Run a parameterised SQL query and a cross-partition aggregate against Cosmos DB's vNext emulator with the Python SDK, and find enable_cross_partition_query is never enforced client-side — it's just a request option the server acts on.

By SkillByExample · Published

azure cosmos-db nosql

Cosmos DB’s query language looks like a subset of SQL, but it runs against JSON documents, not rows with a fixed schema — and on a real multi-partition account, a query that isn’t scoped to one partition key has to fan out across every physical partition to find every match. This lesson runs against the same vNext emulator image as the previous lesson, which — as the first Practice task below shows — only ever has one physical partition, so the fan-out itself isn’t something this emulator can demonstrate directly.

A Parameterised Filter and a Cross-Partition Aggregate

from azure.cosmos import CosmosClient, PartitionKey

ENDPOINT = "http://localhost:8081"
KEY = "C2y6yDjf5/R+ob0N8A7Cgv30VRDJIWEHLM+4QDU5DE2nQ9nDuVTqobD4b8mGGyPMbIZnqyMsEcaGQy67XIw/Jw=="

client = CosmosClient(ENDPOINT, credential=KEY)
db = client.create_database_if_not_exists("storedb")
container = db.create_container_if_not_exists(
    id="orders", partition_key=PartitionKey(path="/customer")
)
for order in [
    {"id": "o0", "customer": "alice", "total": 30},
    {"id": "o1", "customer": "bob", "total": 12},
    {"id": "o2", "customer": "alice", "total": 55},
]:
    container.upsert_item(order)

filtered = list(container.query_items(
    query="SELECT c.id, c.total FROM c WHERE c.total > @min",
    parameters=[{"name": "@min", "value": 20}],
    enable_cross_partition_query=True,
))
print("orders over 20:", filtered)

total = list(container.query_items(
    query="SELECT VALUE SUM(c.total) FROM c",
    enable_cross_partition_query=True,
))
print("sum of all totals:", total)
$ docker run -d --name az-cosmos-query-demo -e PROTOCOL=http mcr.microsoft.com/cosmosdb/linux/azure-cosmos-emulator:vnext-EN20261019
7a930d7ca148aabf7ea628997ee694a36ea740fef94031896a090376653f37f1
$ until docker logs az-cosmos-query-demo 2>&1 | grep -q 'System is now fully ready to accept requests'; do sleep 0.5; done
$ docker run --rm --network container:az-cosmos-query-demo -v "$(pwd)":/app -w /app python:3.14-slim bash -c "pip install -q azure-cosmos==4.17.1 2>&1 | tail -1 && python cosmos_query.py"
[notice] To update, run: pip install --upgrade pip
orders over 20: [{'id': 'o0', 'total': 30}, {'id': 'o2', 'total': 55}]
sum of all totals: [97]

SELECT VALUE SUM(...) returns [97] — a one-element list holding the raw number, not [{"$1": 97}] the way a plain SELECT SUM(c.total) FROM c would. Both queries ran across all three orders spanning two different partition key values (alice, bob); enable_cross_partition_query=True documents that on purpose, but — as Practice 1 below shows — the SDK never checks for it locally; it’s purely a request option the server is free to act on. Pass it anyway: it’s the one line in the call that tells the next reader “yes, this fans out to every partition on purpose,” which the query text alone doesn’t say.

Practice

1. Predict what happens if you drop enable_cross_partition_query entirely, then run it and check. 
from azure.cosmos import CosmosClient

ENDPOINT = "http://localhost:8081"
KEY = "C2y6yDjf5/R+ob0N8A7Cgv30VRDJIWEHLM+4QDU5DE2nQ9nDuVTqobD4b8mGGyPMbIZnqyMsEcaGQy67XIw/Jw=="

client = CosmosClient(ENDPOINT, credential=KEY)
container = client.get_database_client("storedb").get_container_client("orders")

result = list(container.query_items(query="SELECT c.id FROM c"))
print("no enable_cross_partition_query at all:", result)

# the SDK's low-level client exposes the physical partition key ranges directly
ranges = list(container.client_connection._ReadPartitionKeyRanges(container.container_link))
print("physical partition key ranges on this emulator:", len(ranges))
$ docker run --rm --network container:az-cosmos-query-demo -v "$(pwd)":/app -w /app python:3.14-slim bash -c "pip install -q azure-cosmos==4.17.1 2>&1 | tail -1 && python cosmos_query_no_cross.py"
[notice] To update, run: pip install --upgrade pip
no enable_cross_partition_query at all: [{'id': 'o0'}, {'id': 'o1'}, {'id': 'o2'}]
physical partition key ranges on this emulator: 1

All three orders came back whether or not enable_cross_partition_query was passed — the SDK’s own source shows why: the flag is only ever copied into a request option, never checked against the query locally. The partition key range count confirms the other half of it: this container has exactly one physical partition, no matter how many distinct partition key values it holds, so there’s nothing for a query to fan out across on this emulator in the first place.

2. Count orders per customer with GROUP BY instead of fetching every item and counting in Python. 
from azure.cosmos import CosmosClient

ENDPOINT = "http://localhost:8081"
KEY = "C2y6yDjf5/R+ob0N8A7Cgv30VRDJIWEHLM+4QDU5DE2nQ9nDuVTqobD4b8mGGyPMbIZnqyMsEcaGQy67XIw/Jw=="

client = CosmosClient(ENDPOINT, credential=KEY)
container = client.get_database_client("storedb").get_container_client("orders")

counts = list(container.query_items(
    query="SELECT c.customer, COUNT(1) AS orders FROM c GROUP BY c.customer",
    enable_cross_partition_query=True,
))
print("orders per customer:", sorted(counts, key=lambda r: r["customer"]))
$ docker run --rm --network container:az-cosmos-query-demo -v "$(pwd)":/app -w /app python:3.14-slim bash -c "pip install -q azure-cosmos==4.17.1 2>&1 | tail -1 && python cosmos_query_groupby.py"
[notice] To update, run: pip install --upgrade pip
orders per customer: [{'customer': 'alice', 'orders': 2}, {'customer': 'bob', 'orders': 1}]

alice has two orders in this data set, bob has one — GROUP BY does the counting inside the query engine, the same reason it’s preferable over fetching every item cross-partition just to count them in application code.

3. Run a query scoped to one partition key and confirm it works without enable_cross_partition_query. 
from azure.cosmos import CosmosClient, PartitionKey

ENDPOINT = "http://localhost:8081"
KEY = "C2y6yDjf5/R+ob0N8A7Cgv30VRDJIWEHLM+4QDU5DE2nQ9nDuVTqobD4b8mGGyPMbIZnqyMsEcaGQy67XIw/Jw=="

client = CosmosClient(ENDPOINT, credential=KEY)
container = client.get_database_client("storedb").get_container_client("orders")

alice_orders = list(container.query_items(
    query="SELECT c.id, c.total FROM c WHERE c.customer = @who",
    parameters=[{"name": "@who", "value": "alice"}],
    partition_key="alice",
))
print("alice's orders (single-partition query):", alice_orders)
$ docker run --rm --network container:az-cosmos-query-demo -v "$(pwd)":/app -w /app python:3.14-slim bash -c "pip install -q azure-cosmos==4.17.1 2>&1 | tail -1 && python cosmos_query_single_partition.py"
[notice] To update, run: pip install --upgrade pip
alice's orders (single-partition query): [{'id': 'o0', 'total': 30}, {'id': 'o2', 'total': 55}]

Passing partition_key="alice" instead of enable_cross_partition_query=True is what actually scopes this to one partition — the WHERE c.customer = @who clause is redundant for that purpose, since partition_key= alone routes the request. The WHERE clause is still doing real work here, just a different job: filtering within that one partition, the same way it would inside any query.

Cleanup

$ docker rm -f az-cosmos-query-demo
az-cosmos-query-demo

Next: conditional writes with ETags and atomic patch operations.

Frequently Asked Questions

Does query_items actually require enable_cross_partition_query=True for a query that isn't scoped to one partition key?
The Python SDK itself never enforces it client-side, in any version checked here — the source just copies the flag into a feed_options dict that becomes a request header; it's the service on the other end that's free to act on it or not. On this lesson's vNext emulator, which has exactly one physical partition regardless of how many distinct partition key values exist, there's nothing to fan out across either way, so the flag has no observable effect at all. Pass it anyway against a real multi-partition account, where it documents intent even if nothing locally checks it.
Does Cosmos DB support parameterised queries like a SQL database?
Yes — the query string uses @name placeholders and a separate parameters list of {"name": "@name", "value": ...} dicts, the same separation-of-query-from-data that parameterised SQL queries use elsewhere, and for the same reason: it avoids building query strings by concatenating untrusted values into them.
What does SELECT VALUE do differently from a plain SELECT in Cosmos DB's query language?
A plain SELECT wraps each result row in a JSON object with field names (e.g. {"$1": 97}), even for an aggregate; SELECT VALUE unwraps that and returns the raw value directly, so SELECT VALUE SUM(c.total) FROM c returns [97] instead of [{"$1": 97}]. It's the difference between a query returning documents and a query returning scalars.
Is pagination (max_item_count, continuation tokens) covered in this lesson?
No. It's part of the real SDK (query_items(...).by_page(), with a continuation_token you can save and resume from), but max_item_count is documented as a hint rather than a guarantee: the service is free to return fewer items per page than requested, so the exact number of pages for a given query isn't something code should hardcode. Loop until the pages are exhausted rather than assuming a fixed page count.