cursor.sort()
Definition
cursor.sort(sort)
Important
mongosh Method
This page documents a
mongosh
method. This is not the documentation for a language-specific driver, such as Node.js.For MongoDB API drivers, refer to the language-specific MongoDB driver documentation.
Specifies the order in which the query returns matching documents. You must apply
sort()
to the cursor before retrieving any documents from the database.
Compatibility
You can use cursor.sort(sort)
for deployments hosted in the following
environments:
MongoDB Atlas: The fully managed service for MongoDB deployments in the cloud
MongoDB Enterprise: The subscription-based, self-managed version of MongoDB
MongoDB Community: The source-available, free-to-use, and self-managed version of MongoDB
Syntax
The sort()
method has the following parameter:
Parameter | Type | Description |
---|---|---|
sort | document | A document that defines the sort order of the result set. |
The sort
parameter contains field and value pairs, in the
following form:
{ field: value }
The sort document can specify ascending or descending sort on existing fields or sort on text score metadata.
Behavior
Limits
You can sort on a maximum of 32 keys.
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()
method to sort
on the borough
field:
db.restaurants.find().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
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()
method to sort on both the borough
field
and the _id
field:
db.restaurants.find().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.
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 first by the age
field
in descending order and then by the posts
field in ascending order:
db.users.find({ }).sort( { age : -1, posts: 1 } )
When comparing values of different BSON types in sort operations, MongoDB uses the following comparison order, from lowest to highest:
MinKey (internal type)
Null
Numbers (ints, longs, doubles, decimals)
Symbol, String
Object
Array
BinData
ObjectId
Boolean
Date
Timestamp
Regular Expression
MaxKey (internal type)
For details on the comparison/sort order for specific types, see Comparison/Sort Order.
Text Score Metadata Sort
Note
$text
provides text query capabilities for self-managed
(non-Atlas) deployments. For data hosted on MongoDB Atlas, MongoDB
offers an improved full-text query solution, Atlas Search.
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, 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.find().sort( { sizes: 1 } ) // Descending sort db.shoes.find().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.
The following query finds shoes with sizes greater than 10 and sorts the results by shoe size in ascending order:
db.shoes.find( { sizes: { $gte: 7 } } ).sort( { sizes: 1 } )
This query returns document with _id: 'A'
first even though the
filter includes a condition on sizes
greater than 7
because the
query filter does not affect sort key selection.
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 a blocking sort operation on the data. A blocking sort indicates that MongoDB must consume and process all input documents to the sort before returning results. Blocking sorts do not block concurrent operations on the collection or database.
Sort operations that use an index often have better performance than blocking sorts. For more information on creating indexes to support sort operations, see Use Indexes to Sort Query Results.
If MongoDB requires using more than 100 megabytes of system memory for
the blocking sort operation, MongoDB returns an error unless the query
specifies cursor.allowDiskUse()
.
allowDiskUse()
allows MongoDB to use temporary files
on disk to store data exceeding the 100 megabyte system memory limit
while processing a blocking sort operation.
To check if MongoDB must perform a blocking sort, append
cursor.explain()
to the query and check the
explain results. If the query plan
contains a SORT
stage, then MongoDB must perform a
blocking sort operation subject to the 100 megabyte memory limit.
To prevent blocking 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()
withcursor.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 a set of results are both sorted and projected, the MongoDB query engine will always apply the sorting first.
Examples
A collection orders
contain the following documents:
{ _id: 1, item: { category: "cake", type: "chiffon" }, amount: 10 } { _id: 2, item: { category: "cookies", type: "chocolate chip" }, amount: 50 } { _id: 3, item: { category: "cookies", type: "chocolate chip" }, amount: 15 } { _id: 4, item: { category: "cake", type: "lemon" }, amount: 30 } { _id: 5, item: { category: "cake", type: "carrot" }, amount: 20 } { _id: 6, item: { category: "brownies", type: "blondie" }, amount: 10 }
The following query, which returns all documents from the orders
collection, does not specify a sort order:
db.orders.find()
The query returns the documents in indeterminate order:
{ "_id" : 1, "item" : { "category" : "cake", "type" : "chiffon" }, "amount" : 10 } { "_id" : 2, "item" : { "category" : "cookies", "type" : "chocolate chip" }, "amount" : 50 } { "_id" : 3, "item" : { "category" : "cookies", "type" : "chocolate chip" }, "amount" : 15 } { "_id" : 4, "item" : { "category" : "cake", "type" : "lemon" }, "amount" : 30 } { "_id" : 5, "item" : { "category" : "cake", "type" : "carrot" }, "amount" : 20 } { "_id" : 6, "item" : { "category" : "brownies", "type" : "blondie" }, "amount" : 10 }
The following query specifies a sort on the amount
field in
descending order.
db.orders.find().sort( { amount: -1 } )
The query returns the following documents, in descending order of
amount
:
{ "_id" : 2, "item" : { "category" : "cookies", "type" : "chocolate chip" }, "amount" : 50 } { "_id" : 4, "item" : { "category" : "cake", "type" : "lemon" }, "amount" : 30 } { "_id" : 5, "item" : { "category" : "cake", "type" : "carrot" }, "amount" : 20 } { "_id" : 3, "item" : { "category" : "cookies", "type" : "chocolate chip" }, "amount" : 15 } { "_id" : 1, "item" : { "category" : "cake", "type" : "chiffon" }, "amount" : 10 } { "_id" : 6, "item" : { "category" : "brownies", "type" : "blondie" }, "amount" : 10 }
The following query specifies the sort order using the fields from an
embedded document item
. The query sorts first by the category
field
in ascending order, and then within each category
, by the type
field in ascending order.
db.orders.find().sort( { "item.category": 1, "item.type": 1 } )
The query returns the following documents, ordered first by the
category
field, and within each category, by the type
field:
{ "_id" : 6, "item" : { "category" : "brownies", "type" : "blondie" }, "amount" : 10 } { "_id" : 5, "item" : { "category" : "cake", "type" : "carrot" }, "amount" : 20 } { "_id" : 1, "item" : { "category" : "cake", "type" : "chiffon" }, "amount" : 10 } { "_id" : 4, "item" : { "category" : "cake", "type" : "lemon" }, "amount" : 30 } { "_id" : 2, "item" : { "category" : "cookies", "type" : "chocolate chip" }, "amount" : 50 } { "_id" : 3, "item" : { "category" : "cookies", "type" : "chocolate chip" }, "amount" : 15 }
Return in Natural Order
The $natural
parameter returns items according to their
natural order within the database. This ordering is an internal
implementation feature, and you should not rely on any particular
ordering of the documents.
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.