Azure Cosmos DB Query Tutorials

Learn how to effectively query your data in Azure Cosmos DB.

Querying Data with Azure Cosmos DB

Azure Cosmos DB is a globally distributed, multi-model database service. This tutorial focuses on how to effectively query the data stored within your Cosmos DB containers, primarily using the SQL API.

Introduction to SQL API Queries

The SQL API for Azure Cosmos DB provides a rich query language that is familiar to anyone who has used SQL. You can perform standard CRUD operations, complex filtering, projections, joins, and more.

Basic SELECT Statements

The simplest query is a `SELECT * FROM c` statement, which retrieves all items from a container. The alias `c` represents the container itself.

SELECT * FROM c

To select specific properties, you can list them:

SELECT c.name, c.age FROM c

Filtering Data with WHERE Clauses

The `WHERE` clause allows you to filter the results based on specific conditions. You can use various operators like `=`, `!=`, `>`, `<`, `>=`, `<=`, `AND`, `OR`, `IN`, `LIKE`, etc.

SELECT c.name, c.city FROM c WHERE c.city = "New York"

Using logical operators:

SELECT c.name, c.age FROM c WHERE c.age > 30 AND c.city = "London"

Sorting Results with ORDER BY

The `ORDER BY` clause sorts the results in ascending (`ASC`) or descending (`DESC`) order.

SELECT c.name, c.registrationDate FROM c ORDER BY c.registrationDate DESC

Projections and Aliases

You can rename properties in your query results using aliases.

SELECT c.id AS userId, c.email FROM c

Working with Arrays and Nested Data

Azure Cosmos DB documents are often nested JSON. You can query into these structures.

SELECT c.productName, p.name AS categoryName FROM c JOIN p IN c.categories WHERE ArrayContains(c.tags, "electronics")

Performance Tip

When querying nested arrays, using `JOIN` with `ArrayContains` or other array functions can be more efficient than complex `WHERE` clause logic for large datasets.

Aggregations

Azure Cosmos DB supports aggregation functions like `COUNT`, `SUM`, `AVG`, `MIN`, and `MAX`.

SELECT COUNT(c) AS totalItems FROM c
SELECT c.city, COUNT(c) AS numberOfCustomers FROM c GROUP BY c.city

Top and Skip

Use `TOP` to limit the number of results and `SKIP` to skip a certain number of results, useful for pagination.

SELECT TOP 10 c.name FROM c ORDER BY c.name ASC
SELECT * FROM c ORDER BY c.id OFFSET 20 LIMIT 10

Using Stored Procedures and UDFs

For more complex logic or reusable query patterns, consider using stored procedures or user-defined functions (UDFs) in JavaScript.

Refer to the official Azure Cosmos DB documentation for a comprehensive list of supported query features and examples.