$sort (aggregation stage)

Performance

$sort is a blocking stage, which causes the pipeline to wait for all input data to be retrieved for the blocking stage before processing the data. A blocking stage may reduce performance because it reduces parallel processing for a pipeline with multiple stages. A blocking stage may also use substantial amounts of memory for large data sets.

Limits

• You can sort on a maximum of 32 keys.

• Providing a sort pattern with duplicate fields causes an error.

Sort Consistency

MongoDB does not store documents in a collection in a particular order. When sorting on a field which contains duplicate values, documents containing those values may be returned in any order.

The $sort operation is not a "stable sort," which means that documents with equivalent sort keys are not guaranteed to remain in the same relative order in the output as they were in the input.

If the field specified in the sort criteria does not exist in two documents, then the value on which they are sorted is the same. The two documents may be returned in any order.

If consistent sort order is desired, include at least one field in your sort that contains unique values. The easiest way to guarantee this is to include the _id field in your sort query.

Consider the movies collection in the sample_mflix sample dataset. The following command sorts movies released in 1925 or 1926 by year in ascending order:

db.movies.aggregate( [
   { $match: { year: { $in: [ 1925, 1926 ] } } },
   { $sort: { year: 1 } },
   { $project: { _id: 0, title: 1, year: 1 } }
] )

Because the year field has duplicate values for 1925 and 1926, documents with the same value may sort differently across executions. For example, two runs of the previous command produce different orderings:

[
   { title: "Clash of the Wolves", year: 1925 },
   { title: "Lady Windermere's Fan", year: 1925 },
   { title: "Grass: A Nation's Battle for Life", year: 1925 },
   { title: "Beau Geste", year: 1926 },
   { title: "The Black Pirate", year: 1926 },
   { title: "Ella Cinders", year: 1926 },
   { title: "For Heaven's Sake", year: 1926 },
   { title: "The Son of the Sheik", year: 1926 },
   { title: "The Strong Man", year: 1926 }
]

[
   { title: "Lady Windermere's Fan", year: 1925 },
   { title: "Grass: A Nation's Battle for Life", year: 1925 },
   { title: "Clash of the Wolves", year: 1925 },
   { title: "The Strong Man", year: 1926 },
   { title: "The Black Pirate", year: 1926 },
   { title: "Ella Cinders", year: 1926 },
   { title: "Beau Geste", year: 1926 },
   { title: "The Son of the Sheik", year: 1926 },
   { title: "For Heaven's Sake", year: 1926 }
]

An operation sorts values for year in ascending order, but the order of the documents containing duplicate values for year is not the same. For example, duplicate values for 1925 and 1926.

To achieve a consistent sort, add a field that contains only unique values to the sort. The following command sorts on both the year field and the _id field:

db.movies.aggregate( [
   { $match: { year: { $in: [ 1925, 1926 ] } } },
   { $sort: { year: 1, _id: 1 } }
] )

The _id field always contains unique values and the sort order is consistent for multiple sort executions.

Sort by an Array Field

When MongoDB sorts documents by an array-value field, the sort key depends on whether the sort is ascending or descending:

• In an ascending sort, the sort key is the lowest value in the array.

• In a descending sort, the sort key is the highest value in the array.

The query filter does not affect sort key selection.

For example, consider these documents from the movies collection in the sample_mflix sample dataset:

{
  _id: ObjectId("573a1395f29313caabce166c"),
  title: "The Comancheros",
  genres: [ "Action", "Western", "Adventure" ]
}
{
  _id: ObjectId("573a1391f29313caabcd8521"),
  title: "The Son of the Sheik",
  genres: [ "Adventure", "Drama" ]
}

The following queries sort the documents by the genres field in ascending and descending order:

// Ascending sort
db.movies.aggregate( [
   {
      $match: {
         title: { $in: [
            "The Comancheros", "The Son of the Sheik"
         ] }
      }
   },
   {
      $sort: { genres: 1 }
   }
] )

// Descending sort
db.movies.aggregate( [
   {
      $match: {
         title: { $in: [
            "The Comancheros", "The Son of the Sheik"
         ] }
      }
   },
   {
      $sort: { genres: -1 }
   }
] )

Both of the preceding queries return The Comancheros first because Action and Western are the lowest and highest entries in its genres array, respectively.

Filter and Sort by an Array Field

When you filter and sort by a field that contains an array, the filter does not affect the value used as the sort key. The sort always considers all array values as potential sort keys.

For example, the following query finds movies whose genres array includes "Drama" or "Western" and sorts the results by genres in ascending order:

db.movies.aggregate( [
   {
      $match: {
         title: { $in: [
            "The Comancheros", "The Son of the Sheik"
         ] },
         genres: { $in: [ "Drama", "Western" ] }
      }
   },
   {
      $sort: { genres: 1 }
   }
] )

The sort is ascending. The sort key is the first value in the genres array when sorted alphabetically:

• In The Comancheros, the first genres element is Action. This value is used as the sort key even though it does not match the filter { genres: { $in: [ "Drama", "Western" ] } }.

• In The Son of the Sheik, the first genres element is Adventure. Similarly, this operation uses Adventure as the sort key even though it does not match the filter.

The query returns The Comancheros first.

$sort Operator and Memory

$sort and $limit Memory Optimization

When a $sort precedes a $limit and there are no intervening stages that modify the number of documents, the optimizer can coalesce the $limit into the $sort. This allows the $sort operation to only maintain the top n results as it progresses, where n is the specified limit, and ensures that MongoDB only needs to store n items in memory. This optimization still applies when allowDiskUse is true and the n items exceed the aggregation memory limit.

Optimizations are subject to change between releases.

$sort and Memory Restrictions

Pipeline stages that require more than 100 megabytes of memory to execute write temporary files to disk by default. These temporary files last for the duration of the pipeline execution and can influence storage space on your instance.

Individual find and aggregate commands can override the allowDiskUseByDefault parameter by either:

• Using { allowDiskUse: true } to allow writing temporary files out to disk when allowDiskUseByDefault is set to false

• Using { allowDiskUse: false } to prohibit writing temporary files out to disk when allowDiskUseByDefault is set to true

For additional details, see Aggregation Pipeline Limits.

$sort Index Use

The $sort operator can use an index when it is the first stage in a pipeline or is preceded only by a $match stage.

When you use $sort on a sharded cluster, each shard sorts its result documents using an index where available. Then the mongos or one of the shards performs a streamed merge sort.