cursor.sort()¶

On this page

Definition
Behaviors
Examples
Return in Natural Order

Definition¶

cursor.sort(sort)¶

Specifies the order in which the query returns matching documents. You must apply sort() to the cursor before retrieving any documents from the database.

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 computed metadata.

Behaviors¶

Result Ordering¶

Unless you specify the sort() method or use the $near operator, MongoDB does not guarantee the order of query results.

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 sample document specifies a descending sort by the age field and then an ascending sort by the posts field:

{ age : -1, posts: 1 }

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

MinKey (internal type)
Null
Numbers (ints, longs, doubles)
Symbol, String
Object
Array
BinData
ObjectId
Boolean
Date
Timestamp
Regular Expression
MaxKey (internal type)

MongoDB treats some types as equivalent for comparison purposes. For instance, numeric types undergo conversion before comparison.

Changed in version 3.0.0: Date objects sort before Timestamp objects. Previously Date and Timestamp objects sorted together.

The comparison treats a non-existent field as it would an empty BSON Object. As such, a sort on the a field in documents { } and { a: null } would treat the documents as equivalent in sort order.

With arrays, a less-than comparison or an ascending sort compares the smallest element of arrays, and a greater-than comparison or a descending sort compares the largest element of the arrays. As such, when comparing a field whose value is a single-element array (e.g. [ 1 ]) with non-array fields (e.g. 2), the comparison is between 1 and 2. A comparison of an empty array (e.g. [ ]) treats the empty array as less than null or a missing field.

MongoDB sorts BinData in the following order:

First, the length or size of the data.
Then, by the BSON one-byte subtype.
Finally, by the data, performing a byte-by-byte comparison.

Metadata Sort¶

Specify in the sort parameter a new field name for the computed metadata and specify the $meta expression as its value.

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

{ score: { $meta: "textScore" } }

The specified metadata determines the sort order. For example, the "textScore" metadata sorts in descending order. See $meta for details.

Restrictions¶

When unable to obtain the sort order from an index, MongoDB will sort the results in memory, which requires that the result set being sorted is less than 32 megabytes.

When the sort operation consumes more than 32 megabytes, MongoDB returns an error. To avoid this error, either create an index supporting the sort operation (see Sort and Index Use) or use sort() in conjunction with limit() (see Limit Results).

Sort and Index Use¶

The sort can sometimes be satisfied by scanning an index in order. If the query plan uses an index to provide the requested sort order, MongoDB does not perform an in-memory sorting of the result set. For more information, see Use Indexes to Sort Query Results.

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 32 megabytes, the query will fail.

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 structure within it.

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.

MMAPv1¶

Typically, the natural order reflects insertion order with the following exception for the MMAPv1 storage engine. For the MMAPv1 storage engine, the natural order does not reflect insertion order if the documents relocate because of document growth or remove operations free up space which are then taken up by newly inserted documents.

Consider to following example which uses the MMAPv1 storage engine.

The following sequence of operations inserts documents into the trees collection:

db.trees.insert( { _id: 1, common_name: "oak", genus: "quercus" } )
db.trees.insert( { _id: 2, common_name: "chestnut", genus: "castanea" } )
db.trees.insert( { _id: 3, common_name: "maple", genus: "aceraceae" } )
db.trees.insert( { _id: 4, common_name: "birch", genus: "betula" } )

The following query returns the documents in the natural order:

db.trees.find().sort( { $natural: 1 } )

The documents return in the following order:

{ "_id" : 1, "common_name" : "oak", "genus" : "quercus" }
{ "_id" : 2, "common_name" : "chestnut", "genus" : "castanea" }
{ "_id" : 3, "common_name" : "maple", "genus" : "aceraceae" }
{ "_id" : 4, "common_name" : "birch", "genus" : "betula" }

Update a document such that the document outgrows its current allotted space:

db.trees.update(
   { _id: 1 },
   { $set: { famous_oaks: [ "Emancipation Oak", "Goethe Oak" ] } }
)

Rerun the query to returns the documents in natural order:

db.trees.find().sort( { $natural: 1 } )

For MongoDB instances using MMAPv1, the documents return in the following natural order, which no longer reflects the insertion order:

{ "_id" : 2, "common_name" : "chestnut", "genus" : "castanea" }
{ "_id" : 3, "common_name" : "maple", "genus" : "aceraceae" }
{ "_id" : 4, "common_name" : "birch", "genus" : "betula" }
{ "_id" : 1, "common_name" : "oak", "genus" : "quercus", "famous_oaks" : [ "Emancipation Oak", "Goethe Oak" ] }