MongoDB - Aggregation

Aggregation in MongoDB is a powerful feature. It can be used to:

• Group values from multiple documents together.
• Perform operations on the grouped data to return a single result.
• Analyze data changes over time.

source

Getting Started

Select (use) database testagg:

MongoDB

use testagg

Run the command below that adds a new collection called orders:

MongoDB

db.createCollection("orders")

And copy-paste and run the command below that adds some orders into the collection orders:

MongoDB

db.orders.insertMany([
  { username: "matt", category: "Computer", itemName: "Shell Computer", itemPrice: 1290.15 },
  { username: "karen", category: "Computer", itemName: "Win Computer", itemPrice: 2052 },
  { username: "karen", category: "Game", itemName: "Game 2", itemPrice: 40 },
  { username: "john", category: "Game", itemName: "Game 1", itemPrice: 50 },
  { username: "john", category: "Game", itemName: "Game 1", itemPrice: 50 },
  { username: "john", category: "Game", itemName: "Game 2", itemPrice: 40 },
  { username: "matt", category: "Game", itemName: "Game 1", itemPrice: 40 },
  { username: "anna", category: "Game", itemName: "Game 3", itemPrice: 3 },
  { username: "john", category: "Game", itemName: "Game 3", itemPrice: 3 },
])

Aggregation

Aggregation consists of one or more stages. Each stage is executed and then all documents that have passed the stage are being passed to the next stage.

During each of the stages you can for example filter documents, add new fields, count documents (sum, total, maximum, minimum), limit, merge documents, write the output to another collection, group by... Read more

During the aggregation process any fields can be used as variables by appending $ in front of it's name. For example to use the field category, this can be used: $category.

1 Stage

Let's start by creating a simple aggregation pipeline that consists only of one stage in where we filter documents:

MongoDB

db.orders.aggregate([
  // Stage 1: Filter by category
  {
    $match: { category: "Game" }
  }
])

Example Output

[
  {
    _id: ObjectId("62ab28f91d70abe84b6d27dd"),
    username: 'karen',
    category: 'Game',
    itemName: 'Game 2',
    itemPrice: 40
  },
  {
    _id: ObjectId("62ab28f91d70abe84b6d27de"),
    username: 'john',
    category: 'Game',
    itemName: 'Game 1',
    itemPrice: 50
  },
  {
    _id: ObjectId("62ab28f91d70abe84b6d27df"),
    username: 'john',
    category: 'Game',
    itemName: 'Game 1',
    itemPrice: 50
  },
  {
    _id: ObjectId("62ab28f91d70abe84b6d27e0"),
    username: 'john',
    category: 'Game',
    itemName: 'Game 2',
    itemPrice: 40
  },
  {
    _id: ObjectId("62ab28f91d70abe84b6d27e1"),
    username: 'matt',
    category: 'Game',
    itemName: 'Game 1',
    itemPrice: 40
  },
  {
    _id: ObjectId("62ab28f91d70abe84b6d27e2"),
    username: 'anna',
    category: 'Game',
    itemName: 'Game 3',
    itemPrice: 3
  },
  {
    _id: ObjectId("62ab28f91d70abe84b6d27e3"),
    username: 'john',
    category: 'Game',
    itemName: 'Game 3',
    itemPrice: 3
  }
]

You can see that above we are finding all orders that have the category Game.

Let's continue further by adding another stage.

2 Stages

Below is an example on how we can pass the results to another stage:

MongoDB

db.orders.aggregate([
  // Stage 1: Filter by category "Game"
  {
    $match: { category: "Game" }
  },
  // Stage 2: Sum the total amount of different games together
  {
    $group: {
      _id: "$itemName",
      totalPurchasesSum: { $sum: "$itemPrice" },
    }
  }
])

Example Output

[
  { _id: 'Game 2', totalPurchasesSum: 80 },
  { _id: 'Game 1', totalPurchasesSum: 140 },
  { _id: 'Game 3', totalPurchasesSum: 6 }
]

In the first stage we will find all documents from the category Game. During the second phase all games are being grouped using the name of the game and then summed together using the itemPrice field.

Going Further

There can be infinite amount of stages for aggregation operation and each stage can consists of multiple operations.

For example, to also calculate the average purchase amount and number of purchases during the stage 2, we could do:

MongoDB

db.orders.aggregate([
  // Stage 1: Filter by category "Game"
  {
    $match: { category: "Game" }
  },
  // Stage 2: Sum the total amount of different games together, also count the number of games found and average purchase amount
  {
    $group: {
      _id: "$itemName",
      totalPurchasesSum: { $sum: "$itemPrice" },
      avgPurchaseSum: { $avg: "$itemPrice" },
      purchaseCount: { $count: {} },
    }
  }
])

Example Output

[
  {
    _id: 'Game 2',
    totalPurchasesSum: 80,
    avgPurchaseSum: 40,
    purchaseCount: 2
  },
  {
    _id: 'Game 1',
    totalPurchasesSum: 140,
    avgPurchaseSum: 46.666666666666664,
    purchaseCount: 3
  },
  {
    _id: 'Game 3',
    totalPurchasesSum: 6,
    avgPurchaseSum: 3,
    purchaseCount: 2
  }
]