OPTIONS

cursor.sort()

Definition

cursor.sort(sort)

Controls the order that the query returns matching documents. For each field in the sort document, if the field’s corresponding value is positive, then sort() returns query results in ascending order for that attribute. If the field’s corresponding value is negative, then sort() returns query results in descending order.

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 }
  • field is the field by which to sort documents.
  • value is either 1 for ascending or -1 for descending.

Note

You must apply sort() to the cursor before retrieving any documents from the database.

Behavior

Sort Order

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

  1. MinKey (internal type)
  2. Null
  3. Numbers (ints, longs, doubles)
  4. Symbol, String
  5. Object
  6. Array
  7. BinData
  8. ObjectId
  9. Boolean
  10. Date, Timestamp
  11. Regular Expression
  12. MaxKey (internal type)

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

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.

Limit Results

The sort operation requires that the entire sort be able to complete within 32 megabytes.

When the sort operation consumes more than 32 megabytes, MongoDB returns an error. To avoid this error, either create an index to support the sort operation or use sort() in conjunction with limit(). The specified limit must result in a number of documents that fall within the 32 megabyte limit.

For example, if the following sort operation stocks_quotes exceeds the 32 megabyte limit:

db.stocks.find().sort( { ticker: 1, date: -1 } )

Either create an index to support the sort operation:

db.stocks.ensureIndex( { ticker: 1, date: -1 } )

Or use sort() in conjunction with limit():

db.stocks.find().sort( { ticker: 1, date: -1 } ).limit(100)

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 a sub-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.

Typically, the natural order reflects insertion order, except when documents relocate because of document growth due to updates or remove operations free up space which are then taken up by newly inserted documents.

Consider the sequence of insert operations to 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 } )

The documents return in the following natural 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" ] }

See also

$natural