Skip to main content
MongoDB · lesson 4 of 10

Beginner

1

Intermediate

6

Advanced

3

Also part of MongoDB

1
Open the MongoDB page →
MongoDB intermediate Lesson 4 of 10

The Aggregation Stage-Order Bug: Wrong Results, No Error

$limit before $sort doesn't error — it just silently returns the wrong documents. $match, $group, $sort, $limit, and $lookup, and the one ordering mistake that returns confidently wrong data.

By SkillByExample · Published

mongodb aggregation pipeline group lookup

Two stages, reversed, and MongoDB doesn’t complain — it just answers a different question than the one that was actually intended.

match, group, sort, limit

docker exec mongo-lesson mongosh --quiet --eval '
db = db.getSiblingDB("bookshop");
printjson(db.books.aggregate([
  { $match: { genre: "scifi" } },
  { $group: { _id: "$author", bookCount: { $sum: 1 }, avgPrice: { $avg: "$price" } } },
  { $sort: { bookCount: -1 } },
  { $limit: 3 }
]).toArray());
'
[
  { _id: 'Author 1596', bookCount: 14, avgPrice: 51 },
  { _id: 'Author 1176', bookCount: 14, avgPrice: 31 },
  { _id: 'Author 2898', bookCount: 14, avgPrice: 53 }
]

Filter to scifi ($match), group by author counting books and averaging price ($group), order by book count descending ($sort), keep the top 3 ($limit) — each stage’s output feeds the next, in the exact order written.

The bug: limit before sort

Want the 3 highest-priced books in the whole catalog? Write the stages in the wrong order:

docker exec mongo-lesson mongosh --quiet --eval '
db = db.getSiblingDB("bookshop");
printjson(db.books.aggregate([
  { $limit: 3 },
  { $sort: { price: -1 } }
]).toArray().map(b => ({title: b.title, price: b.price})));
'
[
  { title: 'Book 2', price: 7 },
  { title: 'Book 1', price: 6 },
  { title: 'Book 0', price: 5 }
]

No error. No warning. Just confidently wrong output — $limit: 3 grabbed the first three documents in whatever order the collection happened to store them (here, insertion order: Book 0, Book 1, Book 2), and then $sort ordered just those three by price. Prices 5, 6, and 7 are nowhere near the actual highest prices in a 200,000+ document collection that goes up to 54.

The fix: sort before limit

docker exec mongo-lesson mongosh --quiet --eval '
db = db.getSiblingDB("bookshop");
printjson(db.books.aggregate([
  { $sort: { price: -1 } },
  { $limit: 3 }
]).toArray().map(b => ({title: b.title, price: b.price})));
'
[
  { title: 'Book 149', price: 54 },
  { title: 'Book 99', price: 54 },
  { title: 'Book 49', price: 54 }
]

Sort the entire collection by price first, then take the top 3. All three at 54, the real maximum. Same two stages, reversed order, completely different (and this time correct) answer. The general rule: filter, then order, then cut down — never cut down before ordering.

Practice

1. Join two collections with $lookup. 
docker exec mongo-lesson mongosh --quiet --eval '
db = db.getSiblingDB("bookshop");
db.authors.insertMany([
  { name: "Author 1596", country: "USA" },
  { name: "Author 1176", country: "UK" }
]);
printjson(db.books.aggregate([
  { $match: { author: { $in: ["Author 1596", "Author 1176"] } } },
  { $group: { _id: "$author", bookCount: { $sum: 1 } } },
  { $lookup: { from: "authors", localField: "_id", foreignField: "name", as: "authorInfo" } }
]).toArray());
'
[
  {
    _id: 'Author 1596',
    bookCount: 40,
    authorInfo: [ { _id: ObjectId('6aa6c4949f801220bf8b5e91'), name: 'Author 1596', country: 'USA' } ]
  },
  {
    _id: 'Author 1176',
    bookCount: 40,
    authorInfo: [ { _id: ObjectId('6aa6c4949f801220bf8b5e92'), name: 'Author 1176', country: 'UK' } ]
  }
]

$lookup adds authorInfo as an array (a left outer join), not merged top-level fields — an author with no match in the authors collection would get an empty array here, not a missing document.

2. Filter on a field that only exists after grouping. 
docker exec mongo-lesson mongosh --quiet --eval '
db = db.getSiblingDB("bookshop");
printjson(db.books.aggregate([
  { $group: { _id: "$genre", avgPrice: { $avg: "$price" } } },
  { $match: { avgPrice: { $gt: 29.5 } } },
  { $sort: { avgPrice: -1 } }
]).toArray());
'
[
  { _id: 'biography', avgPrice: 30.000240002400023 },
  { _id: 'fantasy', avgPrice: 30 },
  { _id: 'romance', avgPrice: 29.999759997599977 }
]

avgPrice doesn’t exist until $group creates it — this $match has to come after $group to filter on it at all. A $match before $group would only ever see the raw per-book price field, never the computed average.

3. Count documents at each stage to see the pipeline narrow. 
docker exec mongo-lesson mongosh --quiet --eval '
db = db.getSiblingDB("bookshop");
print("total books:", db.books.countDocuments());
print("scifi books:", db.books.countDocuments({ genre: "scifi" }));
print("scifi authors:", db.books.aggregate([{ $match: { genre: "scifi" } }, { $group: { _id: "$author" } }]).toArray().length);
'
total books: 200000
scifi books: 33334
scifi authors: 2500

Each stage narrows the working set, and the last number is worth actually checking rather than assuming: only 2,500 of the 5,000 authors ever appear in scifi, not all 5,000. The seed data assigns genre by i % 6 and author by i % 5000 — since 5000 % 6 == 2, each author’s 40 books cycle through only 3 of the 6 genres (a parity effect from the two cycle lengths sharing a common factor), not all six. A $group count is what caught this instead of an assumption doing the counting.

Next: measuring and optimizing aggregation pipeline performance with explain().

Frequently Asked Questions

Why didn't MongoDB raise an error when $limit came before $sort?
Both stages are individually valid at any position in a pipeline — $limit doesn't require a sorted input, and $sort doesn't require a limited one. MongoDB has no way to know that limiting to 3 documents before sorting wasn't the actual intent; the pipeline you write is the pipeline that runs, stage by stage, in the exact order given.
What's the general rule for ordering $match, $sort, and $limit?
Filter first ($match), then order the remaining candidates ($sort), then cut down to the count wanted ($limit) — in that sequence. Reversing sort and limit answers a completely different question ("sort these first N arbitrary documents" instead of "find the top N documents"), and reversing match and everything else usually just wastes work rather than producing wrong results, but filtering as early as possible is still the right default.
Does $group need a $match before it?
Not structurally, but it's almost always a performance win when one is available and doesn't change the semantics — $match before $group discards non-matching documents before the (often expensive) grouping work runs, rather than grouping everything and then discarding whole groups afterward. Put $match as early in the pipeline as the actual filter allows.
How is $lookup different from a SQL JOIN?
$lookup performs a left outer join against another collection in the same database, adding an array field (empty if nothing matched) rather than flattening into wider rows the way a SQL JOIN does — that's why authorInfo in this lesson's example comes back as an array with one element, not a merged set of top-level fields. There's no equivalent to an INNER JOIN that drops non-matching documents without an additional stage filtering out empty arrays afterward.
Can I filter on a field that a $group stage just computed, like the average price?
Yes, with a $match stage placed after the $group that created it — $match can filter on any field present in the documents flowing through the pipeline at that point, including ones $group just added like avgPrice. A $match before $group has no access to that field at all, since it doesn't exist until $group creates it.