How to Calculate an Average in MongoDB®

Need to find the average value of a field in your MongoDB documents? You’re in the right place! This step-by-step guide helps you calculate averages in MongoDB, from basic queries to advanced aggregations.

We’ll cover the $avg operator, show examples and even provide some performance tips. Whether you’re new to MongoDB or a seasoned pro, you’ll find something useful here. Let’s get started.

The $avg function and basic syntax

MongoDB, a NoSQL database, has an aggregation framework to perform calculations on your data. One of the most common calculations is to find the average of a field across a set of documents. Whether you’re calculating student grades, average order value, average quantity, average amount, or mean temperature in weather data, MongoDB has tools for this.

The hero of the show is the $avg aggregation operator. This operator makes it easy to calculate averages in your queries. It handles numbers, ignores non-numeric data and even handles missing values.

For example, let's pretend you have a collection called products with documents like this:

1
[
2
    { "name": "Laptop", "price": 1200, "category": "electronics" },
3
    { "name": "Tablet", "price": 300, "category": "electronics" },
4
    { "name": "Backpack", "price": 60, "category": "accessories" }
5
]

To calculate the average price of all products, you would use the $avg operator with this syntax:

1
{ $avg: <expression> }

where <expression> is the item field you want to average. In our example, the expression would be $price, so our usage would look like this:

1
{ $avg: $price }

Let's examine some examples and ways to leverage $avg within aggregation pipelines to give more concrete examples of how it can be used differently.

Calculating average of numeric values

When using the $avg operator, it can be used in various circumstances to handle different tasks. So you can see the logic and follow along, imagine we've inserted the following documents into our database:

1
db.sales.insertMany([
2
  { "_id": 1, "item": "Laptop", "price": 1200, "quantity": 10, "category": "electronics", "relatedItems": [2, 3] },
3
  { "_id": 2, "item": "Tablet", "price": 300, "quantity": 20, "category": "electronics", "relatedItems": [1] },
4
  { "_id": 3, "item": "Mouse", "price": 25, "quantity": 50, "category": "accessories", "relatedItems": [1, 4] },
5
  { "_id": 4, "item": "Keyboard", "price": 75, "quantity": 40, "category": "accessories", "relatedItems": [3] }
6
]);

This data is a sales collection with many documents reflecting various items for sale, including their name, price, category and other pieces of info. Let's start with a simple example outlining how to use $avg to calculate the average price of all the products listed. The query would look like this:

1
db.sales.aggregate([
2
  { $group: { _id: null, averagePrice: { $avg: "$price" } } }
3
]);

Running this query returns the average value of the price field for all items in the sales collection. In the case of our demo data, the output would be:

1
{ "_id" : null, "averagePrice" : 400 }

Of course, this is about as simple as it gets, but $avg can be used in several more scenarios and use cases. Combining $avg with different aggregation operators can quickly get your exact desired output. Let's take a look at a few ways we can aggregate data in conjunction with $avg.

Using $avg with $match

Next, we will examine how $avg can be used with the $match stage. This stage allows you to filter documents based on specified criteria. Although you can't use $avg directly within a $match stage, you can pre-calculate the average in a separate step and then use it within the $match stage. Your query would look like this:

1
// Step 1: Calculate the average price and store it in a variable
2
let avgPrice = db.sales.aggregate([
3
  { $group: { _id: null, averagePrice: { $avg: "$price" } } }
4
]).toArray()[0].averagePrice;
5

6
// Step 2: Use the calculated average price to filter documents
7
db.sales.aggregate([
8
  { $match: { price: { $gt: avgPrice } } }
9
]);

This will first calculate the average price of all items and then filter the documents to include only those where the price is greater than the calculated average. The output from this query would look like this:

1
[ { _id: 1,
2
    item: 'Laptop',
3
    price: 1200,
4
    quantity: 10,
5
    category: 'electronics',
6
    relatedItems: [ 2, 3 ] } ]

As you can see, only one item has a higher price than the average: the laptop entry.

Using $avg with $replaceRoot and $replaceWith

We can also use the $avg function with the $replaceRoot and $replaceWith stages. These stages allow you to replace the input document with a new document. You can use $avg within these stages to calculate an average, but you'll typically need to do this in a separate stage first. Here's what the syntax would look like for such a query:

1
db.sales.aggregate([
2
  { $unwind: "$relatedItems" }, // Unwind the relatedItems array
3
  {
4
    $lookup: {
5
      from: "sales",
6
      localField: "relatedItems",
7
      foreignField: "_id",
8
      as: "related"
9
    }
10
  },
11
  { $unwind: "$related" }, // Unwind the related array
12
  {
13
    $group: {
14
      _id: "$_id",
15
      item: { $first: "$item" },
16
      avgRelatedPrice: { $avg: "$related.price" }
17
    }
18
  },
19
  {
20
    $replaceRoot: {
21
      newRoot: {
22
        item: "$item",
23
        avgRelatedPrice: "$avgRelatedPrice"
24
      }
25
    }
26
  }
27
]);

This pipeline unwinds the relatedItems array and then performs the $lookup to fetch related items. It then unwinds the related array and uses $group to calculate the average related price for the original document's _id. Finally, it uses $replaceRoot to create the desired output document. To break things down a bit further, for the "Laptop" document to be returned, it would calculate the average price of "Tablet" and "Mouse". The calculation would look like this:

1
 ((300 + 25) / 2 = 162.5)

Once the query has been executed, the resulting output will look like this:

1
[ { item: 'Keyboard', avgRelatedPrice: 25 },
2
  { item: 'Mouse', avgRelatedPrice: 637.5 },
3
  { item: 'Laptop', avgRelatedPrice: 162.5 },
4
  { item: 'Tablet', avgRelatedPrice: 1200 } ]

The result shows each item along with the average price of related products and accessories.

These examples demonstrate how $avg can be used effectively within various aggregation pipeline stages to perform meaningful calculations on your data. Now that you know how to use $avg in different queries, let's look at some of the best practices and use cases in more detail in the next section.

Tip, tricks and use cases of the $avg function
Although the $avg operator is pretty straightforward to use, there are a few nuances to remember when using it in your queries. Here are a few things to consider when using $avg:

Data type: The field you're averaging should have a numeric data type (e.g., int, double, decimal). $avg will ignore non-numeric values, which can lead to unexpected results if your data isn't consistent.
Missing values. $avg will ignore missing values. If a document doesn't have the field you're averaging, it won't contribute to the calculation. This can be good in some cases, but if you need to handle missing values differently, consider using other aggregation operators like $ifNull.
Performance. To optimize the performance of aggregation queries using $avg, consider filtering your data with a $match stage before performing the aggregation. The $match stage can utilize indexes, significantly reducing the number of documents processed in the pipeline. This approach minimizes the workload for stages like $group or $project, which operate on all input documents.
Combining with other stages. Use the power of the aggregation pipeline by combining $avg with other stages like $match, $sort and $limit to filter, sort and limit your results based on the calculated averages.

Common use cases for $avg

Calculating the average of numeric fields is quite handy in many situations. Calculating the average of a metric is one of the fundamental steps in many different data analysis use cases. Here are some examples where $avg is useful:

Average grades. Calculate the average grade for students in a class or across different subjects
Sales data. Average order value, average purchase frequency, average product price
Website metrics. Average time on page, average page views, average bounce rate
Sensor data. Average temperature, humidity, etc
Financial. Average transaction value, average account balance, average investment return

Using these best practices along with understanding where $avg can be used, you can deploy $avg to pull a lot of great insights from the data residing in your database. Beyond just being useful within MongoDB, developers can also leverage SingleStore to run their MQL (Mongo Query Language) queries alongside SQL queries. How? Let's take a look at that in the next section.

Get more speed with SingleStore Kai™️
Are you looking to speed up your MongoDB aggregations, especially those with leveraging $avg on large datasets? SingleStore Kai has you covered. SingleStore Kai is a MongoDB-compatible API. You can use your existing MongoDB drivers, tools and aggregation pipelines with SingleStore Kai but with much faster performance as compared to MongoDB. Here’s why SingleStore Kai is good for MongoDB analytics:

Lightning fast performance. SingleStore Kai is designed for analytical workloads and can deliver up to 100x faster performance for common MongoDB aggregations, including those with $avg.
Scalability. SingleStore Kai scales horizontally to handle big data and high query volume, so your analytical queries will run smoothly as your data grows.
It's the best of both worlds. You can use your existing MongoDB tools and pipelines, leveraging the power of SQL for more advanced analytics.

Ready to turbocharge your MongoDB analytics? Connect to SingleStore Kai using your existing MongoDB connection string and run your aggregations as usual. We take care of the optimization and execution, giving you better performance and more possibilities for your data.