$sort (aggregation)
On this page
Definition
Compatibility
You can use $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
stage has the following prototype form:
{ $sort: { <field1>: <sort order>, <field2>: <sort order> ... } }
$sort
takes a document that specifies the field(s) to
sort by and the respective sort order. <sort order>
can have one
of the following values:
Value | Description |
---|---|
1 | Sort ascending. |
-1 | Sort descending. |
{ $meta: "textScore" } |
|
If sorting on multiple fields, sort order is evaluated from left to
right. For example, in the form above, documents are first sorted by
<field1>
. Then documents with the same <field1>
values are
further sorted by <field2>
.
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
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.
Examples
Ascending/Descending Sort
For the field or fields to sort by, set the sort order to 1
or -1
to
specify an ascending or descending sort respectively, as in the following example:
db.users.aggregate( [ { $sort : { age : -1, posts: 1 } } ] )
This operation sorts the documents in the users
collection,
in descending order according by the age
field and then in
ascending order according to the value in the posts
field.
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
For a pipeline that includes a $text
search, you can sort by
descending relevance score using the { $meta: "textScore"
}
expression. In the { <sort-key> }
document, set the
{ $meta: "textScore" }
expression to an arbitrary
field name. The field name is ignored by the query system. For example:
db.users.aggregate( [ { $match: { $text: { $search: "operating" } } }, { $sort: { score: { $meta: "textScore" }, posts: -1 } } ] )
This operation uses the $text
operator to match the documents,
and then sorts first by the "textScore"
metadata in descending
order, and then by the posts
field in descending order. The
score
field name in the sort document is ignored by the query
system. In this pipeline, the "textScore"
metadata is not included
in the projection and is not returned as part of the matching
documents. See $meta
for more information.
$sort
Operator and Memory
$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
The $sort
stage has a limit of 100 megabytes of RAM for
in-memory sorts. By default, if the stage exceeds this limit,
$sort
produces an error. To allow pipeline processing to
take up more space, use the allowDiskUse option to enable aggregation pipeline
stages to write data to temporary files.
$sort
Operator and Performance
The $sort
operator can take advantage of an index if it's
used in the first stage of a pipeline or if it's only preceeded by a
$match
stage.
When you use the $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.