Web.QHMIT.com

MongoDB - Sort the Results of a Query

In MongoDB, you can sort the results of a query by using the limit() method.

In MongoDB, when you query a collection using the db.collection.find() method, you can append the sort() method to specify how the results should be sorted. The sort() method specifies a sort order for the cursor.

When using the sort() method, you must provide the sort order as a parameter. This must be a JSON document that defines the sort order. It will typically contain one or more fields, each followed by either 1 or -1, depending on whether the sort should be in ascending or descending order.

Example

First let's do a query without using sort() (so we can see the natural sort order):

Without sort()

Result:

{ "_id" : 1, "name" : "Ian Gillan", "instrument" : "Vocals" }
{ "_id" : 2, "name" : "Ian Paice", "instrument" : "Drums", "born" : 1948 }
{ "_id" : 3, "name" : "Roger Glover", "instrument" : "Bass", "born" : 1945 }
{ "_id" : 4, "name" : "Steve Morse", "instrument" : "Guitar", "born" : 1954 }
{ "_id" : 5, "name" : "Don Airey", "instrument" : "Keyboards", "born" : 1948 }
{ "_id" : 6, "name" : "Jeff Martin", "instrument" : "Vocals", "born" : 1969 }
{ "_id" : 7, "name" : "Jeff Burrows", "instrument" : "Drums", "born" : 1968 }
{ "_id" : 8, "name" : "Stuart Chatwood", "instrument" : "Bass", "born" : 1969 }

With sort() in Ascending Order

A value of 1 next to a field name specifies that the documents should be sorted by the specified field in ascending order.

Here we sort the results by name in ascending order:

Result:

{ "_id" : 5, "name" : "Don Airey", "instrument" : "Keyboards", "born" : 1948 }
{ "_id" : 1, "name" : "Ian Gillan", "instrument" : "Vocals" }
{ "_id" : 2, "name" : "Ian Paice", "instrument" : "Drums", "born" : 1948 }
{ "_id" : 7, "name" : "Jeff Burrows", "instrument" : "Drums", "born" : 1968 }
{ "_id" : 6, "name" : "Jeff Martin", "instrument" : "Vocals", "born" : 1969 }
{ "_id" : 3, "name" : "Roger Glover", "instrument" : "Bass", "born" : 1945 }
{ "_id" : 4, "name" : "Steve Morse", "instrument" : "Guitar", "born" : 1954 }
{ "_id" : 8, "name" : "Stuart Chatwood", "instrument" : "Bass", "born" : 1969 }

With sort() in Descending Order

A value of -1 next to a field name specifies descending order.

Here we sort the results by name in descending order:

Result:

{ "_id" : 8, "name" : "Stuart Chatwood", "instrument" : "Bass", "born" : 1969 }
{ "_id" : 4, "name" : "Steve Morse", "instrument" : "Guitar", "born" : 1954 }
{ "_id" : 3, "name" : "Roger Glover", "instrument" : "Bass", "born" : 1945 }
{ "_id" : 6, "name" : "Jeff Martin", "instrument" : "Vocals", "born" : 1969 }
{ "_id" : 7, "name" : "Jeff Burrows", "instrument" : "Drums", "born" : 1968 }
{ "_id" : 2, "name" : "Ian Paice", "instrument" : "Drums", "born" : 1948 }
{ "_id" : 1, "name" : "Ian Gillan", "instrument" : "Vocals" }
{ "_id" : 5, "name" : "Don Airey", "instrument" : "Keyboards", "born" : 1948 }

Multiple Fields

You can sort by multiple fields. Just separate each field by a comma. When you do this, the documents will be sorted by the first field specified, then the next, and so on.

Here, we sort by the instrument field, followed by the born field. If two or more musicians play the same instrument, the born field determines how they are sorted relative to each other.

Result:

{ "_id" : 3, "name" : "Roger Glover", "instrument" : "Bass", "born" : 1945 }
{ "_id" : 8, "name" : "Stuart Chatwood", "instrument" : "Bass", "born" : 1969 }
{ "_id" : 2, "name" : "Ian Paice", "instrument" : "Drums", "born" : 1948 }
{ "_id" : 7, "name" : "Jeff Burrows", "instrument" : "Drums", "born" : 1968 }
{ "_id" : 4, "name" : "Steve Morse", "instrument" : "Guitar", "born" : 1954 }
{ "_id" : 5, "name" : "Don Airey", "instrument" : "Keyboards", "born" : 1948 }
{ "_id" : 1, "name" : "Ian Gillan", "instrument" : "Vocals" }
{ "_id" : 6, "name" : "Jeff Martin", "instrument" : "Vocals", "born" : 1969 }

And just to see how the second field can affect the order, switch it to a negative value (descending):

Result:

{ "_id" : 8, "name" : "Stuart Chatwood", "instrument" : "Bass", "born" : 1969 }
{ "_id" : 3, "name" : "Roger Glover", "instrument" : "Bass", "born" : 1945 }
{ "_id" : 7, "name" : "Jeff Burrows", "instrument" : "Drums", "born" : 1968 }
{ "_id" : 2, "name" : "Ian Paice", "instrument" : "Drums", "born" : 1948 }
{ "_id" : 4, "name" : "Steve Morse", "instrument" : "Guitar", "born" : 1954 }
{ "_id" : 5, "name" : "Don Airey", "instrument" : "Keyboards", "born" : 1948 }
{ "_id" : 6, "name" : "Jeff Martin", "instrument" : "Vocals", "born" : 1969 }
{ "_id" : 1, "name" : "Ian Gillan", "instrument" : "Vocals" }

Sort with Limit

You can use sort() with limit() to sort the results of the limited result set.

Here we limit the results to 3 documents:

Result:

{ "_id" : 5, "name" : "Don Airey", "instrument" : "Keyboards", "born" : 1948 }
{ "_id" : 1, "name" : "Ian Gillan", "instrument" : "Vocals" }
{ "_id" : 2, "name" : "Ian Paice", "instrument" : "Drums", "born" : 1948 }

If we switch name to descending order:

Result:

{ "_id" : 8, "name" : "Stuart Chatwood", "instrument" : "Bass", "born" : 1969 }
{ "_id" : 4, "name" : "Steve Morse", "instrument" : "Guitar", "born" : 1954 }
{ "_id" : 3, "name" : "Roger Glover", "instrument" : "Bass", "born" : 1945 }

Note that in this case, born has no effect, as there is never more than one result for a given name.

Comparing Different Types

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
  11. Timestamp
  12. Regular Expression
  13. MaxKey (internal type)

Note that non-existent fields are treated the same as a null value (or empty BSON object).