Query Data with Azure Cosmos DB SQL API

Azure Cosmos DB is a globally distributed, multi-model database service. The SQL API for Azure Cosmos DB allows you to query your data using a familiar SQL syntax. This document covers the core concepts and syntax for querying your data.

Basic SELECT Statements

The fundamental way to retrieve data is using the SELECT statement. You can select specific properties or all properties from your items.

Selecting All Properties

To retrieve all properties of an item, use the wildcard *.

SELECT * FROM c

In this query, c is an alias representing the current item in the container. By convention, containers are often aliased as c.

Selecting Specific Properties

You can specify the properties you want to retrieve by listing them after SELECT.

SELECT c.name, c.age FROM c

Selecting Nested Properties

Access nested properties using dot notation.

SELECT c.address.city, c.address.zipCode FROM c

Filtering Data with WHERE Clause

The WHERE clause is used to filter results based on specific conditions.

Equality Comparisons

SELECT * FROM c WHERE c.city = 'Seattle'

Range Comparisons

SELECT * FROM c WHERE c.age > 30

Using Logical Operators

Combine multiple conditions using AND and OR.

SELECT * FROM c WHERE c.city = 'London' AND c.isActive = true

Using IN Operator

Check if a property's value is within a list of values.

SELECT * FROM c WHERE c.country IN ('USA', 'Canada', 'Mexico')

Sorting Data with ORDER BY

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

Ascending Order

SELECT * FROM c ORDER BY c.name ASC

Descending Order

SELECT * FROM c ORDER BY c.registrationDate DESC

Multiple Sort Criteria

SELECT * FROM c ORDER BY c.country ASC, c.city DESC

Limiting Results with TOP

Use the TOP keyword to limit the number of results returned.

SELECT TOP 5 * FROM c ORDER BY c.creationDate DESC

Aggregations

Azure Cosmos DB supports several aggregate functions like COUNT, SUM, AVG, MIN, and MAX.

Counting Items

SELECT COUNT(c.id) FROM c

Calculating Average

SELECT AVG(c.price) FROM c

Grouping Results with GROUP BY

Use GROUP BY to group items based on a property and then apply aggregate functions.

SELECT c.category, COUNT(c.id) AS itemCount FROM c GROUP BY c.category
Note: When using GROUP BY, you must include all selected non-aggregated properties in the GROUP BY clause.

Working with Arrays

You can query elements within arrays. For example, to find items where a specific tag exists in an array named tags:

SELECT * FROM c WHERE ARRAY_CONTAINS(c.tags, 'api')

Common Query Examples

Description SQL Query
Find all users in California SELECT * FROM c WHERE c.address.state = 'CA'
Get the names and ages of users older than 25 SELECT c.name, c.age FROM c WHERE c.age > 25
Count orders placed today (assuming a orderDate field) SELECT COUNT(c.id) FROM c WHERE STARTSWITH(c.orderDate, '2023-10-27')
Find products with a price greater than 100 and sorted by name SELECT * FROM c WHERE c.price > 100 ORDER BY c.name ASC
Tip: Use the Azure Cosmos DB emulator or the Azure portal's Data Explorer to test your queries interactively.

Next Steps