MongoDB Aggregation

The aggregation pipeline lets you group, count, total and reshape documents, like GROUP BY and JOIN in SQL.

The examples use the original six students from the insert lesson.

How a Pipeline Works

aggregate() takes an array of stages. The documents flow through the stages in order, like water through pipes. Each stage changes the documents and passes the result to the next one.

Example

javascript

db.students.aggregate([
  { $match: { ... } },   // 1. keep some documents
  { $group: { ... } },   // 2. group them and calculate
  { $sort:  { ... } }    // 3. sort the results
])

Stage	What it does	SQL idea
`$match`	Filters documents	`WHERE`
`$group`	Groups documents and calculates totals	`GROUP BY`
`$sort`	Sorts	`ORDER BY`
`$project`	Chooses, renames or calculates fields	`SELECT` columns
`$limit`, `$skip`	Takes only some documents	`LIMIT`, `OFFSET`
`$unwind`	Makes one document per array item	(no direct match)
`$lookup`	Brings in documents from another collection	`LEFT JOIN`
`$count`	Counts the documents	`COUNT(*)`

$group: Totals per Group

_id in $group says what to group by. A field name is written with a $ in front, like "$city".

Example

javascript

db.students.aggregate([
  { $group: {
      _id: "$city",
      students: { $sum: 1 },
      avgMarks: { $avg: "$marks" }
  } },
  { $sort: { avgMarks: -1 } }
])

Output

[
  { _id: 'Islamabad', students: 1, avgMarks: 92 },
  { _id: 'Lahore', students: 2, avgMarks: 86.5 },
  { _id: 'Karachi', students: 3, avgMarks: 70.66666666666667 }
]

{ $sum: 1 } adds 1 for each document, which counts them.

Group Accumulators

Accumulator	Result
`$sum`	Total (or a count with `$sum: 1`)
`$avg`	Average
`$min`, `$max`	Smallest and largest value
`$push`	An array of all values in the group
`$addToSet`	An array of the different values
`$first`, `$last`	The first or last value (after a sort)

$match Before $group

Students without a course would make a group with _id: null. Filter them out first:

Example

javascript

db.students.aggregate([
  { $match: { course: { $exists: true } } },
  { $group: {
      _id: "$course",
      students: { $sum: 1 },
      names: { $push: "$name" }
  } },
  { $sort: { students: -1, _id: 1 } }
])

Output

[
  {
    _id: 'Python Programming',
    students: 2,
    names: [ 'Ayesha Siddiqui', 'Sana Iqbal' ]
  },
  {
    _id: 'Web Development',
    students: 2,
    names: [ 'Ali Khan', 'Bilal Ahmed' ]
  },
  { _id: 'Graphic Designing', students: 1, names: [ 'Fatima Noor' ] }
]

Put $match as early as possible. Fewer documents go through the later stages, and an early $match can use indexes.

$project: Shape the Output

$project keeps, removes, renames or calculates fields. Here every student gets a result field:

Example

javascript

db.students.aggregate([
  { $match: { city: "Lahore" } },
  { $project: {
      _id: 0,
      name: 1,
      result: { $cond: [ { $gte: ["$marks", 80] }, "A grade", "Pass" ] }
  } }
])

Output

[
  { name: 'Ayesha Siddiqui', result: 'A grade' },
  { name: 'Sana Iqbal', result: 'A grade' }
]

$cond works like an if/else: [condition, value if true, value if false].

$unwind: One Document per Array Item

Which skills are most common? $unwind turns a student with two skills into two documents, one per skill, so they can be grouped:

Example

javascript

db.students.aggregate([
  { $unwind: "$skills" },
  { $group: { _id: "$skills", count: { $sum: 1 } } },
  { $sort: { count: -1, _id: 1 } },
  { $limit: 3 }
])

Output

[
  { _id: 'HTML', count: 2 },
  { _id: 'Python', count: 2 },
  { _id: 'CSS', count: 1 }
]

Hamza has no skills field, so $unwind leaves him out.

$lookup: Join Another Collection

$lookup adds matching documents from another collection as an array. Suppose the courses collection has a document { title: "Graphic Designing", months: 3 }:

Example

javascript

db.students.aggregate([
  { $match: { name: "Fatima Noor" } },
  { $lookup: {
      from: "courses",
      localField: "course",
      foreignField: "title",
      as: "courseInfo"
  } },
  { $project: { _id: 0, name: 1, "courseInfo.title": 1, "courseInfo.months": 1 } }
])

Output

[
  {
    name: 'Fatima Noor',
    courseInfo: [ { title: 'Graphic Designing', months: 3 } ]
  }
]

If nothing matches, courseInfo is an empty array. Add { $unwind: "$courseInfo" } after $lookup to turn the array into a single embedded document.

Exercise

Which stage should you use to count students per city?

$match$group$project$unwind