Azure Cosmos DB Queries Tutorial

Mastering Querying with SQL, LINQ, and More

Welcome to the Azure Cosmos DB Queries tutorial! This guide will walk you through the various ways to query your data in Azure Cosmos DB, leveraging its powerful and flexible query language.

Understanding Querying in Cosmos DB

Azure Cosmos DB supports multiple query paradigms, including a SQL-like dialect, LINQ for .NET developers, and MongoDB query API. The choice often depends on your application's technology stack and familiarity.

Querying with Azure Cosmos DB SQL

The native query language for Azure Cosmos DB is a dialect of SQL. It's designed to be familiar to developers with SQL experience while offering the flexibility needed for NoSQL data.

Basic SELECT Statements

You can retrieve specific properties or entire documents:

SELECT * FROM c WHERE c.category = 'electronics'

To select only specific fields:

SELECT c.name, c.price FROM c WHERE c.category = 'books'

Filtering with WHERE Clauses

The WHERE clause is fundamental for refining your results. You can use comparison operators, logical operators, and string functions.

  • =, !=, <, >, <=, >=
  • AND, OR, NOT
  • STARTSWITH(), CONTAINS(), ARRAY_CONTAINS()

Example using STARTSWITH:

SELECT c.id, c.orderDate FROM c WHERE STARTSWITH(c.shipmentId, 'SHP-')

Working with Arrays

Azure Cosmos DB excels at handling JSON documents, including arrays. The ARRAY_CONTAINS() function is particularly useful.

SELECT c.id FROM c WHERE ARRAY_CONTAINS(c.tags, 'new-arrival', true)

Using Joins (Limited)

While not a traditional relational database, Cosmos DB supports limited forms of joins using subqueries or self-joins, especially when querying within a single container. Cross-container joins are generally not supported directly.

SELECT VALUE ARRAY(SELECT f.name FROM f IN c.items) FROM c

Querying with LINQ (for .NET SDK)

For .NET developers, the Azure Cosmos DB SDK provides LINQ support, allowing you to write queries in C# that are translated into Cosmos DB SQL.

var query = container.GetItemLinqQuery<Product>()
    .Where(p => p.Category == "furniture" && p.Price > 100);

var results = await query.ToListAsync();

This abstracts away the SQL syntax, making development more intuitive.

Querying with MongoDB API

If you are using the MongoDB API for Azure Cosmos DB, you will use the standard MongoDB query syntax.

db.products.find({ "category": "toys", "rating": { $gte: 4 } })
Note: For optimal performance, it's crucial to design your queries to leverage partitioning effectively and minimize cross-partition queries. Indexing also plays a vital role.

Advanced Query Features

  • Aggregations: Use GROUP BY, COUNT(), SUM(), AVG() for data analysis.
  • UDFs (User-Defined Functions): Extend the query language with custom JavaScript functions.
  • Stored Procedures: Encapsulate complex logic and transactions on the server side.

Example of Aggregation

SELECT c.category, COUNT(c.id) as itemCount
FROM c
GROUP BY c.category
Tip: Explore the Azure Cosmos DB documentation for detailed examples of aggregations, UDFs, and stored procedures.

Next Steps

Now that you understand how to query your data, consider learning about performance optimization techniques and best practices for indexing in Azure Cosmos DB.