$sort (aggregation)

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.

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 following restaurant collection:

db.restaurants.insertMany( [
   { "_id" : 1, "name" : "Central Park Cafe", "borough" : "Manhattan"},
   { "_id" : 2, "name" : "Rock A Feller Bar and Grill", "borough" : "Queens"},
   { "_id" : 3, "name" : "Empire State Pub", "borough" : "Brooklyn"},
   { "_id" : 4, "name" : "Stan's Pizzaria", "borough" : "Manhattan"},
   { "_id" : 5, "name" : "Jane's Deli", "borough" : "Brooklyn"},
] )

The following command uses the $sort stage to sort on the borough field:

db.restaurants.aggregate(
   [
     { $sort : { borough : 1 } }
   ]
)

In this example, sort order may be inconsistent, since the borough field contains duplicate values for both Manhattan and Brooklyn. Documents are returned in alphabetical order by borough, but the order of those documents with duplicate values for borough might not the be the same across multiple executions of the same sort. For example, here are the results from two different executions of the above command:

{ "_id" : 3, "name" : "Empire State Pub", "borough" : "Brooklyn" }
{ "_id" : 5, "name" : "Jane's Deli", "borough" : "Brooklyn" }
{ "_id" : 1, "name" : "Central Park Cafe", "borough" : "Manhattan" }
{ "_id" : 4, "name" : "Stan's Pizzaria", "borough" : "Manhattan" }
{ "_id" : 2, "name" : "Rock A Feller Bar and Grill", "borough" : "Queens" }
{ "_id" : 5, "name" : "Jane's Deli", "borough" : "Brooklyn" }
{ "_id" : 3, "name" : "Empire State Pub", "borough" : "Brooklyn" }
{ "_id" : 4, "name" : "Stan's Pizzaria", "borough" : "Manhattan" }
{ "_id" : 1, "name" : "Central Park Cafe", "borough" : "Manhattan" }
{ "_id" : 2, "name" : "Rock A Feller Bar and Grill", "borough" : "Queens" }

While the values for borough are still sorted in alphabetical order, the order of the documents containing duplicate values for borough (i.e. Manhattan and Brooklyn) 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 borough field and the _id field:

db.restaurants.aggregate(
   [
     { $sort : { borough : 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, create a shoes collection with these documents:

db.shoes.insertMany( [
   { _id: 'A', sizes: [ 7, 11 ] },
   { _id: 'B', sizes: [ 8, 9, 10 ] }
] )

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

// Ascending sort
db.shoes.aggregate( [
   {
      $sort: { sizes: 1 }
   }
] )
// Descending sort
db.shoes.aggregate( [
   {
      $sort: { sizes: -1 }
   }
] )

Both of the preceding queries return the document with _id: 'A' first because sizes 7 and 11 are the lowest and highest in the entries in the sizes array, respectively.

db.shoes.aggregate( [
   {
      $match: { sizes: { $gt: 9 } }
   },
   {
      $sort: { sizes: 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

Starting in MongoDB 6.0, 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. In earlier versions of MongoDB, you must pass { allowDiskUse: true } to individual find and aggregate commands to enable this behavior.

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.

See also: