$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 uses the $sort stage to sort 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 } }
] )

In this example, sort order may be inconsistent, since the year field contains duplicate values for both 1925 and 1926. The operation returns documents in ascending order by year, but the order of documents with duplicate values for year might not be the same across multiple executions of the same sort. For example, here are the results from two different executions of the above command:

[
   { 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 }
]

While the operation sorts values for year in ascending order, the order of the documents containing duplicate values for year (i.e. 1925 and 1926) is not the same.

To achieve a consistent sort, add a field which contains exclusively unique values to the sort. The following command uses the $sort stage to sort on both the year field and the _id field:

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

Since the _id field is always guaranteed to contain exclusively unique values, the returned sort order will always be the same across multiple executions of the same sort.

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.

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

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