Querying & SQL API

Cosmos’s SQL surface is mostly familiar — SELECT, FROM, WHERE, ORDER BY, GROUP BY. But “mostly familiar” is the trap. Every difference compounds when you scale, and the biggest one is that there’s no relational planner — every query lives or dies by whether it touches one partition or many.

The mandatory rule

SELECT * FROM c WHERE c.id = "abc123" AND c.userId = "u-42"

That c.userId (assuming /userId is the partition key) is what turns this from a fan-out into a single-partition lookup. Without it, Cosmos has to broadcast the query to every physical partition and merge the results. Always include the partition key when you have it.

If you genuinely need to query across partitions — say, “all orders this month across all customers” — that’s fine, but treat it as an analytical query and budget the RUs accordingly. You might even ship the data to Synapse Link or a search index instead.

Projections, not SELECT *

Reading the whole document costs RUs proportional to its size. If you only need three fields, project them:

SELECT c.id, c.name, c.total
FROM c
WHERE c.userId = "u-42"

This isn’t just bandwidth — Cosmos charges RUs roughly by KB scanned. A 50-field document with three needed fields is wasted 90% of the time. Project early, project everywhere.

ORDER BY needs an index

By default Cosmos indexes everything as a hash index — perfect for equality, useless for ordering. To ORDER BY c.createdAt DESC, you need a range index on /createdAt. The default policy includes range indexes for everything; if you’ve narrowed the policy (lesson V07), make sure the path is still indexed for range.

Composite ORDER BYs (ORDER BY c.tenantId, c.createdAt) need a composite index, declared explicitly. Without it, the query fails — Cosmos is honest about this rather than scanning silently.

JOIN — but not the SQL kind

JOIN in Cosmos unpacks an array inside a document. Given:

{ "id": "o-1", "items": [{"sku": "A"}, {"sku": "B"}] }

SELECT o.id, i.sku
FROM o
JOIN i IN o.items
WHERE i.sku = "A"

That returns one row per matching array element. There is no cross-document JOIN. If you need data from two entities together, your model should already store them together (lesson V03).

Pagination, the right way

Two options:

OFFSET ... LIMIT — works for small offsets. Cosmos still has to walk every skipped doc, so OFFSET 10000 costs ~10000 docs of work.

Continuation tokens — the proper API. Each query response carries a token; pass it back in the next request. O(1) per page regardless of depth.

results = container.query_items(
    query="SELECT * FROM c WHERE c.userId = @uid",
    parameters=[{"name": "@uid", "value": "u-42"}],
    enable_cross_partition_query=False,
    max_item_count=20,
)
for page in results.by_page():
    for item in page:
        ...
    # The continuation token is opaque — store it client-side for "load more"

Reading the Diagnostics

Every response has a Diagnostics object. In production, log it for slow queries — it tells you:

• RU consumed per partition
• Which partitions were touched
• Time spent in each query operator
• Whether the index was used

If you see “Index utilization 0%” you’ve written a query that scans every doc. Either add an index path or restructure the filter.

What’s next

Lesson V07 is all about indexing — when to opt out of the default greedy policy, when to add composite indexes, when to use spatial. Lesson V09 is the RU economics that make all of this matter financially.