MongoDB Aggregation: MIN, MAX, SUM, AVG, COUNT (with examples)

Introduction

MongoDB is a powerful NoSQL database that offers high flexibility and performance for applications dealing with complex data structures. One of the potent features of MongoDB is its aggregation framework which provides a rich set of operations for analyzing data sets. Aggregation operations process data records and return computed results. In this tutorial, we will explore how to use the aggregation framework to calculate the minimum (MIN), maximum (MAX), sum (SUM), average (AVG), and count (COUNT) in MongoDB, complete with detailed examples.

Understanding MongoDB Aggregations

The aggregation framework in MongoDB is like a pipeline, where documents enter and are transformed as they pass through multiple stages. The stages include filtering, projecting, grouping, and sorting documents. Through these stages, you can shape the data to summarize or calculate metrics easily.

Basic Aggregations

Let’s start with some basic examples. Say we have a collection named sales that contains documents in the following format:

{
  "_id": ObjectId("613a3asd8992"),
  "item": "laptop",
  "quantity": 5,
  "price": 1200,
  "date": ISODate("2023-01-01T09:00:00Z")
}

Counting Documents

To count documents in a collection, you can use the $count stage:

db.sales.aggregate([
  { $count: "total_sales" }
]);

Output:

{
  "total_sales": 500
}

Calculating Minimum and Maximum Values

For finding the minimum and maximum sale prices you can use the $group stage with the $min and $max accumulators:

db.sales.aggregate([
  {
    $group: {
      _id: null,
      minPrice: { $min: "$price" },
      maxPrice: { $max: "$price" }
    }
  }
]);

Output:

{
  "_id": null,
  "minPrice": 100,
  "maxPrice": 1500
}

Calculating the Sum and Average

Similar to MIN and MAX, we can calculate the total revenue (using $sum) and the average price (using $avg) of all sales:

db.sales.aggregate([
  {
    $group: {
      _id: null,
      totalRevenue: { $sum: { $multiply: ["$price", "$quantity"] } },
      averagePrice: { $avg: "$price" }
    }
  }
]);

Output:

{
  "_id": null,
  "totalRevenue": 270000,
  "averagePrice": 900
}

Advanced Aggregations

Moving beyond the basics, you can perform more complex aggregation operations by combining multiple stages and expressing conditional logic.

Conditional Aggregations with $cond

The $cond operator allows you to perform conditional logic within your aggregations. Imagine calculating the total revenue, but only considering sales above a certain quantity:

db.sales.aggregate([
  {
    $group: {
      _id: null,
      totalRevenue: {
        $sum: {
          $cond: {
            if: { $gt: ["$quantity", 10] },
            then: { $multiply: ["$price", "$quantity"] },
            else: 0
          }
        }
      }
    }
  }
]);

Output:

{
  "_id": null,
  "totalRevenue": 30000
}

Grouping with a Key

Aggregations can be even more meaningful when you group data based on a specific key, for instance, the item sold:

db.sales.aggregate([
  {
    $group: {
      _id: "$item",
      totalSold: { $sum: "$quantity" },
      averagePrice: { $avg: "$price" }
    }
  }
]);

Output:

[
  { "_id": "laptop", "totalSold": 150, "averagePrice": 1100 },
  { "_id": "mouse", "totalSold": 300, "averagePrice": 35 },
  ...
]

Working with Dates

When dealing with dates, MongoDB provides accumulators to help you group and perform calculations based on time frames, like by day, month, or year:

db.sales.aggregate([
  {
    $group: {
      _id: { $month: "$date" },
      totalRevenue: { $sum: { $multiply: ["$price", "$quantity"] } }
    }
  },
  { $sort: { _id: 1 } }
]);

Output:

[
  { "_id": 1, "totalRevenue": 14000 },
  { "_id": 2, "totalRevenue": 22000 },
  ...
]

Optimization and Indexing

Aggregations can be resource-intensive. It’s essential to use indexes appropriately to optimize your queries. Creating indexes on the fields involved in the match, sort, and group stages can dramatically improve performance.

Conclusion

MongoDB’s aggregation framework is a powerful tool for summarizing and analyzing data. By understanding the various stages and operators like MIN, MAX, SUM, AVG, and COUNT, you can extract valuable insights from your data. Remember to always consider the performance implications of your aggregations and use indexing to optimize your queries.