Azure Cosmos DB for NoSQL: Aggregate Functions with Examples

In today’s data-driven world, businesses need powerful database solutions that can handle large volumes of data while providing seamless scalability and low-latency access. Azure Cosmos DB, Microsoft’s globally distributed, multi-model NoSQL database service, is designed to meet these modern-day challenges. In this blog post, we will explore the concept of aggregate functions in Azure Cosmos DB and showcase their capabilities through illustrative examples.

Understanding Aggregate Functions

Aggregate functions are essential tools for data analysis and reporting. They allow you to perform calculations on a set of values to produce a single result. In SQL, you might be familiar with aggregate functions such as SUM, COUNT, AVG, MAX, and MIN. Similarly, Azure Cosmos DB’s SQL API provides support for aggregate functions to process data stored in its container.

Sample Data

Before diving into aggregate functions, let’s set up some sample data. Suppose we have a container called “orders” in our Azure Cosmos DB database, and it contains documents representing orders placed by customers. Each order document might look like this:

{
  "orderId": "123456",
  "customerId": "789012",
  "totalAmount": 150.00,
  "items": [
    { "productId": "345", "quantity": 2, "price": 50.00 },
    { "productId": "678", "quantity": 1, "price": 50.00 },
    { "productId": "901", "quantity": 3, "price": 10.00 }
  ]
}

{

"orderId": "123456",

"customerId": "789012",

"totalAmount": 150.00,

"items": [

{ "productId": "345", "quantity": 2, "price": 50.00 },

{ "productId": "678", "quantity": 1, "price": 50.00 },

{ "productId": "901", "quantity": 3, "price": 10.00 }

]

}

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

Aggregate Functions in Azure Cosmos DB

Let’s now explore some of the aggregate functions available in Azure Cosmos DB’s SQL API and see how they can be used to derive meaningful insights from our sample data.

Example 1: Calculating Total Order Amount

To calculate the total order amount for all orders in the “orders” container, we can use the SUM aggregate function:

SELECT SUM(c.totalAmount) AS totalOrderAmount FROM c

1	SELECT SUM(c.totalAmount) AS totalOrderAmount FROM c

In this query, SUM(c.totalAmount) calculates the sum of the totalAmount property in all documents, and AS totalOrderAmount gives the result a meaningful alias. Executing this query will return the total order amount across all orders.

Example 2: Counting the Number of Orders

To determine the total number of orders in the “orders” container, we can use the COUNT aggregate function:

SELECT COUNT(1) AS totalOrders FROM c

1	SELECT COUNT(1) AS totalOrders FROM c

Here, COUNT(1) counts the number of documents (orders) in the container, and AS totalOrders assigns a user-friendly name to the result. Running this query will yield the total number of orders.

Example 3: Finding the Average Order Amount

To find the average order amount for all orders, we can use the AVG aggregate function:

SELECT AVG(c.totalAmount) AS averageOrderAmount FROM c

1	SELECT AVG(c.totalAmount) AS averageOrderAmount FROM c

The AVG(c.totalAmount) calculates the average of the totalAmount property across all documents, and AS averageOrderAmount labels the result appropriately. Executing this query will provide the average order amount.

Example 4: Determining the Maximum Order Amount

To find the highest order amount among all orders, we can use the MAX aggregate function:

SELECT MAX(c.totalAmount) AS maximumOrderAmount FROM c

1	SELECT MAX(c.totalAmount) AS maximumOrderAmount FROM c

Using MAX(c.totalAmount), we can identify the maximum value of the totalAmount property in all documents. The result will contain the highest order amount.

Example 5: Getting the Minimum Order Amount

To find the lowest order amount among all orders, we can use the MIN aggregate function:

SELECT MIN(c.totalAmount) AS minimumOrderAmount FROM c

1	SELECT MIN(c.totalAmount) AS minimumOrderAmount FROM c

With MIN(c.totalAmount), we can find the minimum value of the totalAmount property in all documents. The result will provide the lowest order amount.

Conclusion

In this blog post, we explored the power of aggregate functions in Azure Cosmos DB’s SQL API. These functions allow us to perform powerful calculations and derive meaningful insights from the data stored in our Cosmos container.

Whether it’s calculating total amounts, counting records, finding averages, or determining maximum and minimum values, aggregate functions are essential tools for data analysis and reporting. With Azure Cosmos DB’s globally distributed and scalable nature, these aggregate functions can be applied to vast amounts of data across various regions, providing real-time analytics and enabling data-driven decision-making.

So, leverage the capabilities of Azure Cosmos DB and its aggregate functions to unlock the true potential of your data and build applications that cater to the evolving needs of your business.

Happy aggregating!