cursor.sort() (mongosh method)

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.

The following examples use the restaurants collection in the sample_restaurants database, which contains documents with a borough field that has duplicate values.

The following command uses the sort() method to sort on the borough field:

db.getSiblingDB("sample_restaurants").restaurants.find(
   { borough: { $in: [ "Brooklyn", "Manhattan" ] } },
   { name: 1, borough: 1 }
).sort( { borough: 1 } ).limit( 5 )

In this example, sort order may be inconsistent, since the borough field contains duplicate values. Documents are returned in alphabetical order by borough, but the order of those documents with duplicate values for borough might not be the same across multiple executions of the same sort.

To achieve a consistent sort, add a field which contains exclusively unique values to the sort. The following command uses the sort() method to sort on both the borough field and the _id field:

db.getSiblingDB("sample_restaurants").restaurants.find(
   { borough: { $in: [ "Brooklyn", "Manhattan" ] } },
   { name: 1, borough: 1 }
).sort( { borough: 1, _id: 1 } ).limit( 5 )

[
  {
    _id: ObjectId('5eb3d668b31de5d588f4292a'),
    borough: 'Brooklyn',
    name: 'Riviera Caterer'
  },
  {
    _id: ObjectId('5eb3d668b31de5d588f4292b'),
    borough: 'Brooklyn',
    name: "Wilken'S Fine Food"
  },
  {
    _id: ObjectId('5eb3d668b31de5d588f4292d'),
    borough: 'Brooklyn',
    name: "Wendy'S"
  },
  {
    _id: ObjectId('5eb3d668b31de5d588f42931'),
    borough: 'Brooklyn',
    name: 'Regina Caterers'
  },
  {
    _id: ObjectId('5eb3d668b31de5d588f42932'),
    borough: 'Brooklyn',
    name: 'Taste The Tropics Ice Cream'
  }
]

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.

Ascending/Descending Sort

Specify in the sort parameter the field or fields to sort by and a value of 1 or -1 to specify an ascending or descending sort respectively.

The following operation sorts the documents from the movies collection in the sample_mflix database, first by the year field in descending order and then by the title field in ascending order:

db.movies.find(
   { year: { $type: "int" } },
   { _id: 0, title: 1, year: 1 }
).sort( { year: -1, title: 1 } ).limit( 5 )

[
  { title: 'The Masked Saint', year: 2016 },
  { title: '(T)ERROR', year: 2015 },
  { title: '11 Minutes', year: 2015 },
  { title: '13 Minutes', year: 2015 },
  { title: '3 1/2 Minutes, Ten Bullets', year: 2015 }
]

When comparing values of different BSON types in sort operations, MongoDB uses the following comparison order, from lowest to highest:

1. MinKey (internal type)

2. Null

3. Numbers (ints, longs, doubles, decimals)

4. Symbol, String

5. Object

6. Array

7. BinData

8. ObjectId

9. Boolean

10. Date

11. Timestamp

12. Regular Expression

13. JavaScript Code

14. JavaScript Code with Scope

15. MaxKey (internal type)

For details on the comparison/sort order for specific types, see Comparison/Sort Order.

Text Score Metadata Sort

If you use $text, you can sort by descending relevance score using the { $meta: "textScore" } expression.

The following sample document specifies a descending sort by the "textScore" metadata:

db.users.find(
   { $text: { $search: "operating" } },
   { score: { $meta: "textScore" }}
).sort({ score: { $meta: "textScore" } })

The "textScore" metadata sorts in descending order.

For more information, see $meta for details.

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, the following queries use the movies collection in the sample_mflix database to sort documents by the genres array field in ascending and descending order:

db.movies.find(
   { genres: { $exists: true, $ne: [] } },
   { _id: 0, title: 1, genres: 1 }
).sort( { genres: 1 } ).limit( 3 )

db.movies.find(
   { genres: { $exists: true, $ne: [] } },
   { _id: 0, title: 1, genres: 1 }
).sort( { genres: -1 } ).limit( 3 )

In the ascending sort, MongoDB uses the first genre alphabetically as the sort key. In the descending sort, MongoDB uses the last genre alphabetically as the sort key.

db.movies.find(
   { genres: { $gt: "P" } },
   { _id: 0, title: 1, genres: 1 }
).sort( { genres: 1 } ).limit( 3 )

Sort and Index Use

MongoDB can obtain the results of a sort operation from an index which includes the sort fields. MongoDB may use multiple indexes to support a sort operation if the sort uses the same indexes as the query predicate.

If MongoDB cannot use an index or indexes to obtain the sort order, MongoDB must perform an in-memory sort operation on the data.

Sort operations that use an index often have better performance than in-memory sorts. For more information on creating indexes to support sort operations, see Use Indexes to Sort Query Results.

To check if MongoDB must perform an in-memory sort, append cursor.explain() to the query and check the explain results. If the query plan contains a SORT stage, then MongoDB must perform an in-memory sort operation.

To prevent in-memory sorts from consuming too much memory:

• Create an index to support the sort operation. See Use Indexes to Sort Query Results for more information and examples.

• Limit the amount of data to sort by using cursor.limit() with cursor.sort(). See Limit Results for more information and examples.

Limit Results

You can use sort() in conjunction with limit() to return the first (in terms of the sort order) k documents, where k is the specified limit.

If MongoDB cannot obtain the sort order via an index scan, then MongoDB uses a top-k sort algorithm. This algorithm buffers the first k results (or last, depending on the sort order) seen so far by the underlying index or collection access. If at any point the memory footprint of these k results exceeds 100 megabytes, the query will fail unless the query specifies cursor.allowDiskUse().

Interaction with Projection

When an operation both sorts and projects with the same fields, MongoDB sorts on the original field values before applying the projection.

Index Use

Queries that include a sort by $natural order do not use indexes to fulfill the query predicate with the following exception: If the query predicate is an equality condition on the _id field { _id: <value> }, then the query with the sort by $natural order can use the _id index.