$sort, $limit, and $skip in the Pipeline
Learners will order and paginate aggregation results and understand the optimizer rules for pushing $match and $sort before $group.
$sort, $limit, and $skip in the Pipeline is a free MongoDB Academy lesson on CoddyKit — lesson 4 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the MongoDB Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.
Overview: Ordering and Paginating Results
Three aggregation pipeline stages control the order and volume of results: $sort orders documents by one or more fields, $limit takes only the first N documents, and $skip discards the first N documents. Together they implement sorting and pagination. Understanding where to place them in the pipeline has a major impact on performance.
The $sort Stage
The $sort stage orders documents by the values of one or more fields. Use 1 for ascending and -1 for descending. When multiple fields are specified, documents are sorted by the first field first, then by the second field among ties, and so on—exactly like multi-key sorting in SQL or MongoDB's find().sort().
// Sort by revenue descending, then alphabetically by name for ties
db.products.aggregate([
{ $group: { _id: '$category', revenue: { $sum: '$price' } } },
{ $sort: { revenue: -1, _id: 1 } } // revenue desc, category name asc
]);$sort and Index Use
When $sort is the first pipeline stage (or follows a $match that uses an index), MongoDB can use an index to satisfy the sort—avoiding an in-memory sort entirely. When $sort appears after stages that produce new fields (like $group or $project), no index is available and MongoDB performs an in-memory sort. Large in-memory sorts (>100 MB) require allowDiskUse: true.
// Index-backed sort: $sort on indexed field before any transformation
db.orders.aggregate([
{ $match: { userId: 'u1' } },
{ $sort: { createdAt: -1 } } // if createdAt is indexed, no in-memory sort
]);
// In-memory sort: $sort after $group (computed field, no index)
db.orders.aggregate([
{ $group: { _id: '$userId', total: { $sum: '$amount' } } },
{ $sort: { total: -1 } } // in-memory: 'total' is a computed field
]);The $limit Stage
$limit passes only the first N documents downstream and discards the rest. It's straightforward—pass an integer specifying the maximum number of documents to allow through. $limit placed before expensive stages like $lookup dramatically reduces the number of joins or lookups performed. Always ask: 'Can I limit early?'
// Top 10 best-selling products
db.orders.aggregate([
{ $group: { _id: '$productId', sold: { $sum: '$qty' } } },
{ $sort: { sold: -1 } },
{ $limit: 10 }, // only top 10 pass through
// Only 10 lookups instead of thousands:
{ $lookup: { from: 'products', localField: '_id', foreignField: '_id', as: 'product' } }
]);The $skip Stage
$skip discards the first N documents from the stream. It's the aggregation equivalent of offset pagination: page 2 starts at skip 10 (assuming limit 10 per page). $skip always comes after $sort to produce consistent results—skipping unsorted documents gives unpredictable pages. Like SQL OFFSET, $skip on large offsets is slow because MongoDB must still process all skipped documents.
const PAGE = 2;
const PAGE_SIZE = 10;
db.articles.aggregate([
{ $match: { status: 'published' } },
{ $sort: { publishedAt: -1 } },
{ $skip: (PAGE - 1) * PAGE_SIZE }, // skip page 1's 10 docs
{ $limit: PAGE_SIZE } // take page 2's 10 docs
]);Optimizer: $sort + $limit Merge
MongoDB's aggregation optimizer detects when a $sort stage is immediately followed by a $limit stage and merges them into a top-N sort. Instead of sorting all documents and then discarding most, MongoDB keeps only the top N candidates in a priority queue during the sort, which uses O(N) memory rather than O(total) memory. This optimisation is automatic—you get it just by writing $sort followed by $limit.
// This pattern triggers the top-N sort optimisation
db.reviews.aggregate([
{ $match: { productId: 'p1' } },
{ $sort: { rating: -1, helpful: -1 } }, // sort
{ $limit: 5 } // $sort + $limit merged into top-5 sort internally
]);
// MongoDB never sorts ALL reviews; it tracks only the best 5Optimizer: $match and $sort Reordering
The aggregation optimizer also automatically moves a $match stage earlier in the pipeline when it's safe to do so. For example, if $sort appears before $match on a field that $sort didn't compute, the optimizer moves $match before $sort to reduce the number of documents that need to be sorted. You can observe optimizer changes in explain() output.
// You write:
db.orders.aggregate([
{ $sort: { amount: -1 } },
{ $match: { status: 'completed' } } // optimizer moves this BEFORE $sort
]);
// Optimized execution (equivalent to):
db.orders.aggregate([
{ $match: { status: 'completed' } }, // fewer docs to sort
{ $sort: { amount: -1 } }
]);Combining All Four: A Complete List Endpoint
A realistic API endpoint that lists products with filters, sorting, and pagination combines $match, $sort, $skip, and $limit in the correct order. The pattern is always: filter → sort → skip → limit. This order ensures MongoDB uses indexes for both filtering and (when possible) sorting before discarding unneeded documents.
async function getProducts({ category, sort = 'price', page = 1, pageSize = 20 }) {
return db.products.aggregate([
{ $match: { category, inStock: true } }, // 1. filter
{ $sort: { [sort]: 1 } }, // 2. sort
{ $skip: (page - 1) * pageSize }, // 3. offset
{ $limit: pageSize } // 4. take page
]).toArray();
}Performance of $skip at Deep Offsets
The fundamental problem with $skip is that MongoDB must still process and discard every skipped document. On page 1000 with 20 items per page, MongoDB discards 19,980 documents before returning 20. This gets progressively slower with deeper pages. For public-facing search or feed APIs where users rarely go beyond page 5-10, offset pagination is fine. For high-frequency deep pagination, keyset pagination is required.
// Page 1000 is SLOW: MongoDB processes and discards 19,980 docs
db.posts.aggregate([
{ $sort: { createdAt: -1 } },
{ $skip: 19980 }, // 999 * 20
{ $limit: 20 }
]);
// Keyset pagination: always fast regardless of page depth
db.posts.aggregate([
{ $match: { createdAt: { $lt: lastSeenTimestamp } } }, // cursor
{ $sort: { createdAt: -1 } },
{ $limit: 20 }
]);$sort Memory Limit and allowDiskUse
An in-memory $sort in the aggregation pipeline is limited to 100 MB by default. If the sort exceeds this limit, the pipeline fails with a QueryExceededMemoryLimitNoDiskUseAllowed error. Pass { allowDiskUse: true } as the second argument to aggregate() to enable spilling to disk. Alternatively, apply an early $match or use a supporting index to reduce the sort input size.
// Enable disk use for large sorts
db.events.aggregate(
[
{ $match: { year: 2024 } },
{ $sort: { timestamp: -1 } },
{ $group: { _id: '$userId', events: { $push: '$type' } } }
],
{ allowDiskUse: true } // allows spilling to disk
);Getting the Total Count Alongside Results
A common API pattern is to return both the paginated results and the total count in a single database round-trip. Use $facet to split the pipeline into two parallel branches: one for $skip/$limit results and one for $count. This avoids two separate aggregation calls and is more efficient than counting all documents separately.
db.products.aggregate([
{ $match: { category: 'electronics' } },
{ $sort: { price: 1 } },
{ $facet: {
// Branch 1: paginated results
data: [
{ $skip: 0 },
{ $limit: 20 }
],
// Branch 2: total count
total: [
{ $count: 'count' }
]
}}
]);
// Output: { data: [...20 docs...], total: [{ count: 348 }] }Quick Check
Test your understanding of $sort, $limit, and $skip in the aggregation pipeline.
Lesson Recap
In this lesson you learned: $sort orders documents and benefits from index use when placed early, $limit takes the first N documents and merges with $sort into an efficient top-N sort, and $skip implements offset pagination but degrades at deep offsets. Next up we explore advanced aggregation stages including $lookup for joining collections.
Frequently asked questions
Is the “$sort, $limit, and $skip in the Pipeline” lesson free?
Yes — the full text of “$sort, $limit, and $skip in the Pipeline” is free to read here on the web, and the MongoDB Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the MongoDB Academy course, upgrade to CoddyKit PRO.
What will I learn in “$sort, $limit, and $skip in the Pipeline”?
Learners will order and paginate aggregation results and understand the optimizer rules for pushing $match and $sort before $group. You practise MongoDB Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.
Do I need any experience to start MongoDB Academy?
No prior experience is required. MongoDB Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 4 of 4, so you can start here or from the beginning and move at your own pace.
How long does the “$sort, $limit, and $skip in the Pipeline” lesson take?
Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.
Can I write and run code in this MongoDB Academy lesson?
Yes. Every MongoDB Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.
All lessons in this course
- Pipeline Concepts: Stages, Operators, and Expressions
- $match and $project: Filter and Reshape
- $group: Aggregating and Computing Totals
- $sort, $limit, and $skip in the Pipeline