$sort, $limit และ $skip ในไปป์ไลน์

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 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().

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.

$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?'

$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.

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.

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.

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.

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.

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.

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.

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.