How do you return the top 3 highest-scoring documents per group in an aggregation pipeline?

## The shape of the problem "Top N per group" — three best scores per player, five most recent orders per customer, the latest reading per sensor — is one of the most frequently asked aggregation questions, because it has several correct answers with genuinely different cost profiles and the choice reveals whether the candidate thinks about memory. ## Option 1: $sort then $group with $push and $slice ```javascript db.scores.aggregate([ { $sort: { team: 1, score: -1 } }, { $group: { _id: "$team", docs: { $push: "$$ROOT" } } }, { $set: { docs: { $slice: ["$docs", 3] } } } ]) ``` The accumulator receives documents in the order the previous `$sort` produced, so `docs` is already ranked; `$slice` as an **expression** — `{ $slice: [array, n] }` — takes the first three. `$$ROOT` is the system variable for the whole current document; push only the fields you need if the documents are large. The cost is the point: every document of every group is accumulated before anything is discarded. For a hundred teams of a few dozen players that is nothing. For a sensor collection with a million readings per device, that is a per-group array of a million documents, and the aggregation will strain or fail. If a candidate offers this form, the good follow-up answer is "and I would only use it when group sizes are bounded". ## Option 2: bounded accumulators ($topN, $firstN) MongoDB 5.2 added accumulators that keep only what is needed: ```javascript db.scores.aggregate([ { $group: { _id: "$team", top: { $topN: { output: ["$player", "$score"], sortBy: { score: -1 }, n: 3 } } } } ]) ``` `$topN` carries its own `sortBy`, so no preceding `$sort` stage is needed and the accumulator's state stays bounded at `n` entries per group. `$bottomN` is the ascending counterpart; `$top` and `$bottom` are the single-result forms. `$firstN` and `$lastN` are the order-dependent siblings — they take `input` and `n` and mean "the first n documents that reached this group", which is only meaningful when a `$sort` precedes them. When you have the version available, this is the answer to lead with: same result, bounded memory. ## Option 3: $setWindowFields with $rank ```javascript db.scores.aggregate([ { $setWindowFields: { partitionBy: "$team", sortBy: { score: -1 }, output: { rank: { $denseRank: {} } } } }, { $match: { rank: { $lte: 3 } } } ]) ``` This is the direct analogue of a SQL window function, available from MongoDB 5.0. Its distinguishing merit is explicit tie handling: `$rank` leaves gaps after ties (1, 1, 3), `$denseRank` does not (1, 1, 2), and `$documentNumber` ignores ties entirely and just numbers rows. If the requirement is "everyone tied for third also counts", this is the only one of the three that expresses it directly — the other two silently cut at exactly three documents, choosing arbitrarily among tied candidates. ## Ties, determinism and the sort key Whatever form you pick, a sort on a non-unique key does not define a total order, so two runs can return different members of a tie and pagination over such a result is unstable. Add a tiebreaker to the sort — `{ score: -1, _id: 1 }` — whenever the result feeds anything that must be reproducible. This is the detail that most often separates a solid answer from a strong one. ## Indexes A `$sort` at the front of a pipeline can be served by an index, which avoids a blocking sort of the whole input. A compound index matching the sort — `{ team: 1, score: -1 }` — lets the sort be satisfied by index order. Note that this helps the option-1 form, where the sort is a real leading stage; `$topN` does its own ordering inside the group. Confirm which is actually happening with `explain()` rather than assuming, and remember an index only helps a sort at a point in the pipeline where the original collection order is still available. ## The single-latest special case "Most recent document per key" is the degenerate N=1 version and is worth recognising instantly: `$sort` by key then timestamp descending, `$group` with `{ $first: "$$ROOT" }` — or, on 5.2+, `{ $top: { output: "$$ROOT", sortBy: { ts: -1 } } }`. It appears constantly in time-series and event-log work, and the `$first` form is bounded already, since only one document per group is retained. ## Choosing Bounded accumulators when the version allows and the shape is simple; window fields when ties or ranking semantics matter, or when you need the rank value itself in the output; `$push` plus `$slice` only when group sizes are known to be small or when you must run on an older deployment.