The aggregation pipeline, exam-style

A pipeline is a conveyor belt of stages

Lesson 13's terminology table ended with a promise: what SQL calls "Aggregation (e.g. group by)", MongoDB calls the "aggregation pipeline" (p. 297). The lecture gives it one slide, "Further Read Features: Aggregates" (p. 317): "SQL-like aggregation functionality", "Pipeline documents from a collection pass through an aggregation pipeline", "Expressions produce output documents based on calculations performed on input documents". The exercise session turns those three lines into the tool behind most MongoDB queries of the exam (pp. 1439–1442).

Start with the first pipeline among the session's solutions, the answer to "Write a query to count the number of housings based on their price" (p. 1499):

// the number of housings for each price (p. 1499)
db.air_bnb_collection.aggregate([
  {
    "$group": {
      "_id": "$price",
      "housing_count": { "$sum": 1 }
    }
  }
])

Read it from the outside in. aggregate([…]) runs a pipeline on the collection: the square brackets hold an array of stages, applied in order, each one a document with a single key, the stage's name. Here there is one stage, $group. Its "_id": "$price" says which documents go together — those with the same price — and "housing_count": { "$sum": 1 } adds 1 for each document that joins a group. In SQL: SELECT price, COUNT(*) … GROUP BY price. Why not find? "When applying $unwind or $group stages (explained later), it is necessary to apply the aggregate(...) method" (p. 1439): find only filters and projects.

To watch a pipeline work you need data. This lesson uses five housings (Table 1). The first is Ribeira Charming Duplex, the document of the dataset slides (pp. 1472–1474), with the values they print — except its reviews: Théo's is on p. 1474, Bridget's is ours. The other four housings are ours too, chosen so that every stage of the session has something to bite on — a host with two housings, a missing cleaning fee, housings without reviews.

On these five, the pipeline of p. 1499 returns three documents: { _id: 50, housing_count: 1 }, { _id: 80, housing_count: 3 } and { _id: 120, housing_count: 1 }. Five documents went in, three came out, and none of them is a housing any more.

Exam questions need more than one stage. The third aggregation exercise of the session filters before it counts: "Write a query to compute the total number of housings based on their cancellation policy, whose accommodates is greater than 6 and whose host has a profile picture" (p. 1501).

// housings per cancellation policy, after two filters (p. 1501)
db.air_bnb_collection.aggregate([
  { "$match": { "accommodates": { "$gt": 6 } } },
  { "$match": { "host.host_has_profile_pic": true } },
  {
    "$group": {
      "_id": "$cancellation_policy",
      "cancellation_policy_count": { "$sum": 1 }
    }
  }
])

$match is the filter of find moved inside the pipeline — "$match defines filters" (p. 1442) — with the syntax of lesson 14. Two $match stages in a row act as an AND; one $match holding both conditions is the same query. Fig. 1 follows the five housings along this belt.

The figure is the habit the exercise sessions ask for: "Do not focus only on memorising the commands required to perform queries. Instead, focus on understanding how the query shapes the data. Think on how the dataset would change after each operation in the query is performed" (introduction to the exercise sessions). The session teaches six stages; p. 1442 lists them — "besides the previously explained $group, $unwind, $sort, and $limit stages, $match defines filters while $project defines projections" — and Table 2 gives each its SQL twin.

The lecture's next slide (p. 318), "Further Read Features: Map-Reduce", shows the older tool for the same job, and only its signature: db.collection.mapReduce( , , { out: , query: , sort: , limit: , … } ). The two empty slots before the options are where two JavaScript functions go: a map that emits key–value pairs from each document, and a reduce that combines the values of one key — the model of lesson 30, run inside the database. The current MongoDB manual deprecates map-reduce since version 5.0 in favour of the pipeline, and no exercise of the course uses it.

The $group stage: one bin per key

p. 1440 defines the stage: "Aggregate operations, i.e., operations aimed at grouping with respect to one or more fields, are achieved by applying the $group stage within the aggregate(...) method. Such a stage requires defining the list of fields to perform the aggregation and the aggregation functions to be applied." Picture a row of bins. The _id expression is written on each bin, every document drops into the bin of its value, and the aggregation functions are counters fixed to each bin. The MongoDB manual calls them accumulators; this lesson keeps the slides' name.

The key works because of the dollar. Inside $group, a string that starts with $ means "the value of this field in the current document"; without it, it is just a string. A $match filter is the exception: there, as in find, "$price" is plain text. p. 1441 shows both and draws the lesson: "Whenever a grouping operation is to be performed on the whole dataset, it is possible to apply a dummy _id in the $group stage. It's enough to set the _id to true or a fixed value. The latter is why the $ in the grouping stage is important!" Drop the dollar from "$price" and "price" is that fixed value (Fig. 2).

Used on purpose, a fixed key answers "the total number of bedrooms available across all housings" (p. 1500): one bin for the whole collection, and $sum over a field instead of over 1.

// total bedrooms, one group for all (p. 1500, corrected)
db.air_bnb_collection.aggregate([
  {
    "$group": {
      "_id": true,
      "bedroom_count": { "$sum": "$bedrooms" }
    }
  }
])

On the five housings: { _id: true, bedroom_count: 11 }, that is 3 + 1 + 5 + 2 + 0. "_id": null works the same way. The slide's own version differs from this one by a single character — keep the hook in mind.

The slide lists them: "sum, avg, min, max, etc." (p. 1440). Table 3 runs each on the five housings.

To group by two fields at once, write _id as a sub-document. p. 1504 groups by "_id": { "n_bed": "$bedrooms", "n_bath": "$bathrooms" }: one bin per pair, with names you choose on the left and field paths, dollar included, on the right. Its output keys look like { n_bed: 5, n_bath: 2 }; the whole query is in section 3.

What survives? "Whenever a $group stage is applied, only the fields used to perform the aggregation or created by it will be available in the next stages" (p. 1440). After the stage of p. 1499 each document is { _id, housing_count } and nothing else: name, bedrooms, amenities are gone, and a later stage that reads them finds nothing — a $match on accommodates placed after this $group lets no document through. It is SQL's rule after GROUP BY: only the grouped columns and the aggregates remain.

After a $group, a document is its _id and the fields its aggregation functions created — nothing else goes on.

Arrays need $unwind; groups are filtered after grouping

"Write a query to compute the total number of housings for each amenity" (p. 1485) hides a problem: amenities is an array, and $group sees whole documents. Group by "$amenities" directly and each whole list becomes a key — five bins of one housing each (try it in the widget). The array has to be taken apart first, which is what $unwind does: "It shapes the collection so that each document is replaced with a set of new ones, i.e., one for each element in the document's array on which the unwind stage is applied. These new documents contain all the fields from the main one and a field with the name of the array field that contains one of its documents" (p. 1439).

After the unwind, amenities holds one string per document, and grouping by it groups by amenity (p. 1502):

// the number of housings for each amenity (p. 1502)
db.air_bnb_collection.aggregate([
  { "$unwind": { "path": "$amenities" } },
  {
    "$group": {
      "_id": "$amenities",
      "housing_with_amenity_count": { "$sum": 1 }
    }
  }
])

The five housings become 3 + 2 + 3 + 1 + 2 = 11 documents, and the 11 fall into four bins: Wifi 4, TV 3, Iron 3, Cable TV 1. The slide writes the long form { "path": "$amenities" }; { "$unwind": "$amenities" } is the same stage. Either way the path has its dollar: it reads a field.

A document whose array is empty or missing yields no copy at all. River Loft and Attic Room have no reviews, so unwinding "$reviews" leaves three housings. Harmless when you count elements; a trap when you count parents — "the number of reviews of each housing" would lose the housings with none instead of showing 0. The manual's option "preserveNullAndEmptyArrays": true keeps them once; the slides do not use it.

Now a condition on the bins: "returning only the amenities whose count is greater than 15" (p. 1503).

// amenities offered by more than 15 housings (p. 1503)
db.air_bnb_collection.aggregate([
  { "$unwind": { "path": "$amenities" } },
  {
    "$group": {
      "_id": "$amenities",
      "housing_with_amenity_count": { "$sum": 1 }
    }
  },
  { "$match": { "housing_with_amenity_count": { "$gt": 15 } } }
])

A $match after the $group sees bins, not housings: it filters by what the group computed, under the exact name the $group gave it. It cannot move up — before the $group the field it tests does not exist yet. This is SQL's HAVING; the $match before a group is its WHERE. On five housings no amenity reaches 16, and the empty answer is correct. In the widget below you can lower the threshold.

p. 1504 combines a compound key with two HAVING filters:

// averages per (bedrooms, bathrooms), two filters (p. 1504)
db.air_bnb_collection.aggregate([
  {
    "$group": {
      "_id": { "n_bed": "$bedrooms", "n_bath": "$bathrooms" },
      "avg_price": { "$avg": "$price" },
      "avg_cleaning_fees": { "$avg": "$cleaning_fee" }
    }
  },
  { "$match": { "avg_price": { "$gt": 60 } } },
  { "$match": { "avg_cleaning_fees": { "$gt": 40 } } }
])

Before the filters there are five bins, one per (bedrooms, bathrooms) pair. avg_price > 60 removes (1, 1), Tiny Studio's 50. avg_cleaning_fees > 40 removes (3, 1), Ribeira's 35, and (0, 1): Attic Room has no cleaning_fee, its average is null, and null is not greater than 40. Two bins remain: (2, 1) with 120 and 50, (5, 2) with 80 and 60. Note the names: the field is cleaning_fee, the created field avg_cleaning_fees. The names a $group creates are yours; field names are the data's.

A $match takes any filter of lesson 14, array operators included. The session's last two filters are written as pipelines (pp. 1505–1506):

// housings with one review that has both values (p. 1505)
db.air_bnb_collection.aggregate([
  {
    "$match": {
      "reviews": {
        "$elemMatch": {
          "listing_id": "10006546",
          "reviewer_id": "11263097"
        }
      }
    }
  }
])

// a review of 10006546 and a review by Bridget (p. 1506)
db.air_bnb_collection.aggregate([
  { "$match": { "reviews.listing_id": "10006546" } },
  { "$match": { "reviews.reviewer_name": "Bridget" } }
])

The first returns Ribeira alone: its second review, by reviewer 11263097, has both values. The second also returns Big Villa, whose first review carries listing id 10006546 and whose second is by Bridget. Same two conditions, two questions: "one review with A and B" against "one with A and one with B" — the distinction of pp. 1436–1438, taught in lesson 14.

Top N, and counting distinct things

$sort and $limit are the pipeline's ORDER BY and LIMIT, with the numbers lesson 14 used for sort(): 1 ascending, -1 descending. A "top N" question is a count, a sort and a cut:

// the three amenities offered by the most housings
db.air_bnb_collection.aggregate([
  { "$unwind": { "path": "$amenities" } },
  { "$group": { "_id": "$amenities",
                "housing_with_amenity_count": { "$sum": 1 } } },
  { "$sort": { "housing_with_amenity_count": -1 } },
  { "$limit": 3 }
])

Result: Wifi 4, then TV and Iron with 3 — in either order, since MongoDB promises no order among equal values; add a second key, "_id": 1, when the order matters. Keep $sort before $limit. With find, MongoDB applies sort() before limit() however you chain them; a pipeline runs its stages as written. { "$limit": 2 } placed first keeps the first two housings in storage order — Ribeira (8 guests) and Tiny Studio (2) — and only then sorts them, while the right order returns Big Villa (10) and Ribeira (8).

$project keeps the fields marked 1 (and _id) or removes those marked 0. p. 1442 chains five of the six stages — all but $limit — on the phone numbers of the session's people collection:

// providers with over 10 numbers, count hidden (p. 1442)
db.people_collection.aggregate([
  { "$unwind": { "path": "$mobile_phone_numbers" } },
  {
    "$group": {
      "_id": {
        "provider": "$mobile_phone_numbers.service_provider"
      },
      "phone_numbers_per_provider": { "$sum": 1 }
    }
  },
  { "$match": { "phone_numbers_per_provider": { "$gt": 10 } } },
  { "$sort": { "phone_numbers_per_provider": -1 } },
  { "$project": { "phone_numbers_per_provider": 0 } }
])

Read it aloud: one document per phone number; one bin per provider, with its count; keep the providers with more than 10 numbers; largest first; hide the count. Sorting by a field and removing it in the very next stage is fine — by then the order is made.

The session ends with a different kind of count: "For each country, compute the total number of unique hosts" (p. 1507).

// for each country, the number of distinct hosts (p. 1507)
db.air_bnb_collection.aggregate([
  {
    "$group": {
      "_id": {
        "country": "$address.country", "host_id": "$host.host_id"
      },
      "duplication_count_per_host": { "$sum": 1 }
    }
  },
  {
    "$group": {
      "_id": "$_id.country",
      "unique_host_count": { "$sum": 1 }
    }
  }
])

A single $group by country with { "$sum": 1 } counts housings: Portugal 3, because host 51399391 has two housings there. The session groups twice. The first $group makes one document per (country, host) pair, however many housings the pair has — the duplicates collapse here. The second groups those pair-documents by country, reading it inside the compound key with "$_id.country", and counts them: Portugal 2, Spain 2. duplication_count_per_host is not needed by the question; it only shows what collapsed. In SQL this is COUNT(DISTINCT host_id) … GROUP BY country, an aggregate the taught stages do not have.

To count distinct things per key: group by (key, thing), then group by key and count.

From the exam's sentence to the stages

The paper of 30 January 2026 ends every MongoDB query with "Use only the pipeline stages discussed during the practical lectures." Those are the six of Table 2. $lookup, the stage that joins two collections, is not among them, and the same paper bans it in the design question: "You must design the relationships without using the MongoDB $lookup operation." So every query runs on one collection, over documents nested as lesson 13 designed them. Other stages exist — $count, $sortByCount, $addFields — but were not discussed: leave them out. Aggregation functions such as $push or $first are not stages; prefer the slide's sum, avg, min and max where they suffice, and say in a comment what any other one does.

One habit before the first stage: your query can only be read against your own documental representation, so state it — or point to your design answer — and name the arrays you will unwind. Then translate the sentence phrase by phrase. Here is the method on the exam of 12 September 2025, exercise 5 ("tracking farmers and their sales at local markets"). Its ER has Farm (Name, Address, State, Description) and Animal (Tag, Name, Age, Animal Type (e.g., Cow), Breed), joined by House, with Housing Date: Animal (1, 1) – Farm (1, N). The query: "Write a query to compute the total number of farms that house at least 10 cows of at least 5 different breeds. (2 PT)"

// assumed: collection farm, animals nested in each farm
{
  "_id": "Green Acres",                     // Name: String
  "Address": "SP 12, km 3",                 // String
  "State": "Italy",                         // String
  "Description": "Dairy farm",              // String
  "animals": [
    { "Tag": "A-001",                       // String
      "Name": "Bessy",                      // String
      "Age": 6,                             // Integer
      "Animal_Type": "Cow",                 // String
      "Breed": "Holstein",                  // String
      "Housing_Date": ISODate("2020-03-01") // Date, of House
    }
  ]
}

// farms with at least 10 cows of at least 5 breeds
db.farm.aggregate([
  { "$unwind": { "path": "$animals" } },
  { "$match": { "animals.Animal_Type": "Cow" } },
  { "$group": { "_id": { "farm": "$_id",
                         "breed": "$animals.Breed" },
                "cows_of_breed": { "$sum": 1 } } },
  { "$group": { "_id": "$_id.farm",
                "n_breeds": { "$sum": 1 },
                "n_cows": { "$sum": "$cows_of_breed" } } },
  { "$match": { "n_cows": { "$gte": 10 },
                "n_breeds": { "$gte": 5 } } },
  { "$group": { "_id": true, "n_farms": { "$sum": 1 } } }
])

The second $group adds up two things: 1 per (farm, breed) document, which counts the breeds, and the field cows_of_breed, which counts the cows. On five test farms the pipeline turns 77 animals into 47 cows after the $match, then 25 (farm, breed) pairs, five farms with their two counts, two farms over both thresholds and finally { _id: true, n_farms: 2 }. Leave out the $match on Cow and a farm with six cows and ten sheep slips in — 16 animals of 8 breeds — and the answer becomes 3. One more detail is worth a comment in your answer: if no farm qualifies, the last $group receives nothing and returns no document, not a 0.

Silent wrong answers, and the missing letter

Back to the hook. p. 1500 sums "$bedroom", and the field is bedrooms — "bedrooms: 3" on the dataset slide (p. 1472). $sum adds the values that are numbers and skips the rest; a field that does not exist is not a number; the total of nothing is 0. MongoDB raises no error because it has no schema to check names against: a misspelt field is simply a field no document has. That is schema-on-read (lesson 4) seen from the query side.

The missing letter has relatives. Each row of Table 5 is a pipeline that runs, returns something and is wrong; all were run on MongoDB 8.2.

MongoDB never complains about a field it cannot find: check every name against the ER diagram and every stage against the documents it receives.