Azure Cosmos DB for NoSQL: SELECT Queries with Examples

In today’s rapidly evolving digital landscape, businesses are generating an unprecedented amount of data. To effectively handle this data explosion, modern applications require databases that can scale horizontally, provide low-latency access, and support a flexible schema. Azure Cosmos DB, a globally distributed, multi-model NoSQL database service from Microsoft, perfectly fits the bill. In this blog post, we’ll explore the power of Azure Cosmos DB for NoSQL and focus on SELECT queries with illustrative examples.

Sample Data

Before we dive into SELECT queries, let’s set up some sample data. Suppose we have a container called “products” in our Azure Cosmos DB database, and it contains documents representing various products in an online store. Here are a few examples of how product documents might look:

In the “products” container, we have multiple documents representing different products, each with its unique productId and corresponding attributes.

SELECT Queries in Azure Cosmos DB

In Azure Cosmos DB, SELECT queries are performed using the SQL API (formerly known as DocumentDB SQL). SQL API allows you to query JSON documents using familiar SQL-like syntax. The SELECT queries retrieve data from your Cosmos container based on specified criteria.

Example 1: Simple SELECT Query

Let’s start with a basic SELECT query to retrieve all documents from the “products” container:

In this query, c is an alias for the documents in the container, and * is a wildcard that represents all properties of the documents. Executing this query will return all documents in the “products” container.

Example 2: SELECT Query with Filtering

To narrow down the results, you can add a WHERE clause to the SELECT query. Let’s say we want to retrieve products from the “Electronics” category:

This query filters the documents based on the category property and returns all documents where the category is ‘Electronics’.

Example 3: SELECT Query with Projection

Projection queries allow you to retrieve only specific properties from the documents. For instance, suppose we need to fetch the names and prices of products from the “products” container:

By using the projection (c.productName, c.price), the query will only return the productName and price properties of the documents.

Example 4: SELECT Query with Order By

You can also order the results in ascending or descending order using the ORDER BY clause. Let’s retrieve the products from the “products” container, sorted by price in descending order:

This query returns all documents in the “products” container, sorted by the price property in descending order.

Example 5: SELECT Query with Paging

When dealing with a large number of documents, it’s essential to implement paging to retrieve results in smaller, manageable chunks. Azure Cosmos DB supports paging using the OFFSET and LIMIT clauses:

In this query, we skip the first 10 documents and fetch the next 5 documents in the result set.

Conclusion

In this blog post, we explored the powerful capabilities of Azure Cosmos DB for NoSQL data storage and the SQL API’s SELECT queries. The sample data in the “products” container allowed us to demonstrate various SELECT query examples, showcasing the flexibility and performance of Azure Cosmos DB in handling diverse data access patterns.

Embrace the versatility of Azure Cosmos DB and harness its potential to build data-driven, globally distributed applications that scale seamlessly with your business’s growth.

Happy querying!