Cosmos DB Querying - MSDN Documentation

Mastering Queries in Azure Cosmos DB

Azure Cosmos DB is a globally distributed, multi-model database service. Efficiently querying your data is crucial for performance and cost optimization. This article delves into advanced querying techniques to help you get the most out of Cosmos DB.

Understanding the Query Language

Cosmos DB supports a SQL-like query language that allows for complex data retrieval and manipulation. This language is optimized for the NoSQL nature of the database, enabling you to query JSON documents with ease.

Key Querying Concepts

SELECT: Used to specify the properties you want to retrieve. You can use wildcards like SELECT * to retrieve all properties.
FROM: Specifies the container (or collection) from which to retrieve data.
WHERE: Filters the results based on specified conditions.
ORDER BY: Sorts the results based on one or more properties.
OFFSET LIMIT: Used for pagination to retrieve a subset of results.
JOIN: Combines documents from different arrays or datasets within a single document.

Advanced Querying Patterns

1. Array Manipulation

Working with arrays within your JSON documents is a common requirement. Cosmos DB provides powerful functions to handle these.

Example: Retrieving items from an array where a specific condition is met:

SELECT VALUE item FROM c JOIN item IN c.items WHERE item.price > 50

2. Spatial Queries

Cosmos DB has built-in support for spatial data types (like GeoJSON points, polygons, linestrings) and spatial functions, enabling location-aware queries.

Example: Finding documents within a certain distance:

SELECT * FROM c WHERE ST_DISTANCE(c.location, { "type": "Point", "coordinates": [-122.1, 47.6] }) < 10000

3. Self-JOINs and Correlated Subqueries

While not a traditional relational database, Cosmos DB allows for patterns that mimic self-joins and correlated subqueries using the JOIN clause.

4. User-Defined Functions (UDFs)

For complex logic that goes beyond standard SQL functions, you can write User-Defined Functions (UDFs) in JavaScript to extend the query capabilities.

UDF Example (JavaScript)

Function: calculateDiscount

function calculateDiscount(price, discountPercentage) {
    return price * (1 - discountPercentage / 100);
}

Usage in Query:

SELECT c.id, udf.calculateDiscount(c.price, 10) AS discountedPrice FROM c

5. Stored Procedures

Stored Procedures offer a way to encapsulate complex transactional logic directly within Cosmos DB, improving performance by reducing network latency.

Performance Considerations

Indexing: Understand how Cosmos DB indexing works and ensure your indexing policy is optimized for your query patterns.
Partition Keys: Design your partition keys carefully to distribute your data and query load effectively.
Request Units (RUs): Monitor your RU consumption. Efficient queries use fewer RUs, leading to cost savings.
Query Optimization: Avoid SELECT * when possible, use appropriate filters, and consider projections.