Querying Cosmos DB with SQL API

Basic SELECT, FROM, and WHERE

Cosmos DB SQL queries follow the structure SELECT … FROM … WHERE …. The FROM clause names the container alias — it is always the container you are querying, not a table name. The query runs within the container you send it to.

-- Return all items in the container
SELECT * FROM c

-- Select specific fields (projection)
SELECT c.id, c.customerId, c.total, c.status
FROM c

-- Filter with WHERE
SELECT * FROM c
WHERE c.status = "shipped"

-- Multiple conditions
SELECT * FROM c
WHERE c.status = "shipped"
  AND c.total > 100

-- Filter with IN for multiple values
SELECT * FROM c
WHERE c.status IN ("shipped", "delivered")

Field paths use dot notation. Nested fields are accessed with dots, and array elements are accessed with bracket notation or the IN keyword for array membership.

-- Query a nested field
SELECT * FROM c
WHERE c.shippingAddress.state = "WA"

-- Query by a top-level array element value
SELECT * FROM c
WHERE ARRAY_CONTAINS(c.tags, "premium")

Point reads vs queries: RU cost difference

The most efficient operation in Cosmos DB is a point read — fetching a single item by its full primary key (partition key value + id). A point read costs exactly 1 RU for a 1 KB item, regardless of container size. It goes directly to the physical partition without any query execution.

A query runs the SQL engine, which must scan or use an index to find matching items. Even with a perfect index hit on the partition key, a query that returns one item costs more than 2–3 RUs because of query parsing and execution overhead.

In code (using the .NET SDK as an example), a point read looks like:

// Point read — cheapest possible operation
var response = await container.ReadItemAsync<Order>(
    id: "order-8f3a2c1b",
    partitionKey: new PartitionKey("cust-00192")
);
// Cost: ~1 RU

Design your data model so that the most frequent lookups can be point reads. If you frequently look up a session by session ID, make session ID both the partition key and the item ID. If you frequently look up an order by order ID and you already know the customer ID, store the customer ID alongside and use it as the partition key.

ORDER BY, TOP, and OFFSET/LIMIT

Sort results with ORDER BY. Cosmos DB requires a composite index to order by a field that is different from the partition key.

-- Most recent orders for a customer
SELECT TOP 10 c.id, c.orderDate, c.total
FROM c
WHERE c.customerId = "cust-00192"
ORDER BY c.orderDate DESC

-- Pagination with OFFSET and LIMIT
SELECT c.id, c.orderDate, c.total
FROM c
WHERE c.customerId = "cust-00192"
ORDER BY c.orderDate DESC
OFFSET 20 LIMIT 10

To use ORDER BY across all partitions (a cross-partition ORDER BY), the container needs a composite index on the relevant fields. Add composite indexes through the indexing policy:

{
  "compositeIndexes": [
    [
      { "path": "/orderDate", "order": "descending" },
      { "path": "/total", "order": "descending" }
    ]
  ]
}

Array operations and subqueries

Cosmos DB has several functions for working with arrays in documents. Arrays are common in document models — an order has an items array, a user has a roles array, a product has a tags array.

-- Check if an array contains a specific value
SELECT * FROM c
WHERE ARRAY_CONTAINS(c.tags, "electronics")

-- ARRAY_CONTAINS with partial match for object arrays
SELECT * FROM c
WHERE ARRAY_CONTAINS(c.items, {"productId": "prod-441"}, true)

-- Get the length of an array
SELECT c.id, ARRAY_LENGTH(c.items) AS itemCount
FROM c
WHERE c.customerId = "cust-00192"

-- JOIN to flatten an array (self-join within one document)
SELECT c.id, item.productId, item.name, item.quantity
FROM c
JOIN item IN c.items
WHERE c.customerId = "cust-00192"
  AND item.quantity > 1

The JOIN keyword in Cosmos DB is not a relational join between two containers. It is a self-join that flattens a nested array within a single document. Each element of the array becomes a separate row in the result set. This is useful for queries that need to filter or return individual array elements.

Aggregation functions

Cosmos DB supports COUNT, SUM, MIN, MAX, and AVG. Without a GROUP BY clause, these aggregate across all items in the query result. GROUP BY support was added in SDK version 3.x and API version 2019-11-01+.

-- Count all orders for a customer
SELECT COUNT(1) AS orderCount
FROM c
WHERE c.customerId = "cust-00192"

-- Sum of order totals by status
SELECT c.status, SUM(c.total) AS totalRevenue, COUNT(1) AS orderCount
FROM c
GROUP BY c.status

-- Average order value for a specific customer
SELECT AVG(c.total) AS avgOrderValue
FROM c
WHERE c.customerId = "cust-00192"

-- Maximum order value across all orders (cross-partition)
SELECT MAX(c.total) AS maxOrder
FROM c

Aggregations without a partition key filter are cross-partition operations. Use them for analytics and reporting queries where cost is expected, not on user-facing hot paths.

Useful built-in functions

Cosmos DB SQL includes string, math, type-checking, and date functions.

-- String functions
SELECT * FROM c
WHERE STARTSWITH(c.customerId, "cust-")

SELECT UPPER(c.status) AS statusUpper FROM c

SELECT * FROM c
WHERE CONTAINS(c.shippingAddress.city, "Seattle")

-- Type checking (useful for heterogeneous containers)
SELECT * FROM c
WHERE IS_STRING(c.orderId)

SELECT * FROM c
WHERE IS_DEFINED(c.promoCode)

-- Date functions (using Unix timestamp stored as number)
SELECT * FROM c
WHERE c._ts > (GetCurrentTimestamp() / 1000) - 86400

-- String formatting and concatenation
SELECT CONCAT(c.shippingAddress.city, ", ", c.shippingAddress.state) AS location
FROM c
WHERE c.customerId = "cust-00192"

Running queries in the Azure Portal

The Azure Portal provides a Data Explorer for Cosmos DB where you can write and execute queries, see results, and — critically — see the RU charge for each query. Navigate to your Cosmos DB account, select your container, and open New SQL Query.

The Query Stats panel below the results shows:

• Request Charge — total RUs consumed by the query.
• Retrieved document count — number of documents Cosmos DB examined (before filtering).
• Output document count — number of documents returned in results.
• Index utilization — whether the query used an index or fell back to a full scan.

A large gap between retrieved document count and output document count indicates a full scan with poor selectivity — the query is reading many items to return few. This is where an index change or partition key filter will reduce RU cost significantly.