This is my third MongoDB post, in the first post we look at how we can install MongoDB as a Windows Service, In the second post we looked at how we could do UPSERTs with MongoDB, today we will look at how to sort results in MongoDB
Connect to MongoDB and insert the following data
db.SortTest.insert( { name : "Denis", age : 20 } )
db.SortTest.insert( { name : "Abe", age : 30 } )
db.SortTest.insert( { name : "John", age : 40 } )
db.SortTest.insert( { name : "Xavier", age : 10 } )
db.SortTest.insert( { name : "Zen", age : 50 } )
Now run this command
db.SortTest.find()
You should get back something like the following, the ObjectId will be different for you
{ “_id” : ObjectId(“50f6811c03141917bce6459f”), “name” : “Denis”, “age” : 20 }
{ “_id” : ObjectId(“50f6811d03141917bce645a0”), “name” : “Abe”, “age” : 30 }
{ “_id” : ObjectId(“50f6811d03141917bce645a1”), “name” : “John”, “age” : 40 }
{ “_id” : ObjectId(“50f6811d03141917bce645a2”), “name” : “Xavier”, “age” : 10 }
{ “_id” : ObjectId(“50f6811e03141917bce645a3”), “name” : “Zen”, “age” : 50 }
Ordering in MongoDB is pretty easy, you add sort, then you specify the fields you want to sort on, use 1 for ascending and use -1 for descending
If we want to sort by name descending, we can use the following
db.SortTest.find().sort({name: -1})
Here are the results, as you can see they are sorted by name descending
{ “_id” : ObjectId(“50f6811e03141917bce645a3”), “name” : “Zen”, “age” : 50 }
{ “_id” : ObjectId(“50f6811d03141917bce645a2”), “name” : “Xavier”, “age” : 10 }
{ “_id” : ObjectId(“50f6811d03141917bce645a1”), “name” : “John”, “age” : 40 }
{ “_id” : ObjectId(“50f6811c03141917bce6459f”), “name” : “Denis”, “age” : 20 }
{ “_id” : ObjectId(“50f6811d03141917bce645a0”), “name” : “Abe”, “age” : 30 }
To sort by name ascending, you just have to change -1 to 1
db.SortTest.find().sort({name: 1})
Here are the results and the names are now ascending
{ “_id” : ObjectId(“50f6811d03141917bce645a0”), “name” : “Abe”, “age” : 30 }
{ “_id” : ObjectId(“50f6811c03141917bce6459f”), “name” : “Denis”, “age” : 20 }
{ “_id” : ObjectId(“50f6811d03141917bce645a1”), “name” : “John”, “age” : 40 }
{ “_id” : ObjectId(“50f6811d03141917bce645a2”), “name” : “Xavier”, “age” : 10 }
{ “_id” : ObjectId(“50f6811e03141917bce645a3”), “name” : “Zen”, “age” : 50 }
Here is age ascending
db.SortTest.find().sort( { age: -1 } );
Results
{ “_id” : ObjectId(“50f6811e03141917bce645a3”), “name” : “Zen”, “age” : 50 }
{ “_id” : ObjectId(“50f6811d03141917bce645a1”), “name” : “John”, “age” : 40 }
{ “_id” : ObjectId(“50f6811d03141917bce645a0”), “name” : “Abe”, “age” : 30 }
{ “_id” : ObjectId(“50f6811c03141917bce6459f”), “name” : “Denis”, “age” : 20 }
{ “_id” : ObjectId(“50f6811d03141917bce645a2”), “name” : “Xavier”, “age” : 10 }
Here is age descending
db.SortTest.find().sort( { age: 1 } );
{ “_id” : ObjectId(“50f6811d03141917bce645a2”), “name” : “Xavier”, “age” : 10 }
{ “_id” : ObjectId(“50f6811c03141917bce6459f”), “name” : “Denis”, “age” : 20 }
{ “_id” : ObjectId(“50f6811d03141917bce645a0”), “name” : “Abe”, “age” : 30 }
{ “_id” : ObjectId(“50f6811d03141917bce645a1”), “name” : “John”, “age” : 40 }
{ “_id” : ObjectId(“50f6811e03141917bce645a3”), “name” : “Zen”, “age” : 50 }
Let’s do another insert, this one has the same name but a different age
db.SortTest.insert( { name : "Abe", age : 50 } )
Sorting by name again
db.SortTest.find().sort({name: -1})
As you can see Abe is there twice
{ “_id” : ObjectId(“50f6811e03141917bce645a3”), “name” : “Zen”, “age” : 50 }
{ “_id” : ObjectId(“50f6811d03141917bce645a2”), “name” : “Xavier”, “age” : 10 }
{ “_id” : ObjectId(“50f6811d03141917bce645a1”), “name” : “John”, “age” : 40 }
{ “_id” : ObjectId(“50f6811c03141917bce6459f”), “name” : “Denis”, “age” : 20 }
{ “_id” : ObjectId(“50f6811d03141917bce645a0”), “name” : “Abe”, “age” : 30 }
{ “_id” : ObjectId(“50f6818103141917bce645a4”), “name” : “Abe”, “age” : 50 }
Here is how you do a multi-field sort, it is pretty much the same as in SQL, you just add the other field and specify 1 or -1
So let’s sort by age descending and name ascending
db.SortTest.find().sort( { age: -1 , name: 1} );
Here are the results
{ “_id” : ObjectId(“50f6818103141917bce645a4”), “name” : “Abe”, “age” : 50 }
{ “_id” : ObjectId(“50f6811e03141917bce645a3”), “name” : “Zen”, “age” : 50 }
{ “_id” : ObjectId(“50f6811d03141917bce645a1”), “name” : “John”, “age” : 40 }
{ “_id” : ObjectId(“50f6811d03141917bce645a0”), “name” : “Abe”, “age” : 30 }
{ “_id” : ObjectId(“50f6811c03141917bce6459f”), “name” : “Denis”, “age” : 20 }
{ “_id” : ObjectId(“50f6811d03141917bce645a2”), “name” : “Xavier”, “age” : 10 }
Now, we are going to sort by age ascending and name descending
db.SortTest.find().sort( { age: 1 , name: -1} );
{ “_id” : ObjectId(“50f6811d03141917bce645a2”), “name” : “Xavier”, “age” : 10 }
{ “_id” : ObjectId(“50f6811c03141917bce6459f”), “name” : “Denis”, “age” : 20 }
{ “_id” : ObjectId(“50f6811d03141917bce645a0”), “name” : “Abe”, “age” : 30 }
{ “_id” : ObjectId(“50f6811d03141917bce645a1”), “name” : “John”, “age” : 40 }
{ “_id” : ObjectId(“50f6811e03141917bce645a3”), “name” : “Zen”, “age” : 50 }
{ “_id” : ObjectId(“50f6818103141917bce645a4”), “name” : “Abe”, “age” : 50 }
As you can see sorting is very easy to do in MongoDB. One thing to keep in mind
The sort function requires that the entire sort be able to complete within 32 megabytes. When the sort option consumes more than 32 megabytes, MongoDB will return an error. Use cursor.limit(), or create an index on the field that you’re sorting to avoid this error.
We are going to look at how to create indexes in the next post: Indexes in MongoDB: A quick overview
For more MongoDB posts, take a look at these
Install MongoDB as a Windows Service
How to sort results in MongoDB
Indexes in MongoDB: A quick overview
Multidocument updates with MongoDB
MongoDB: How to include and exclude the fields you want in results
MongoDB: How to limit results and how to page through results
MongoDB: How to backup and restore databases
MongoDB: How to restore collections
MongoDB: How to backup all the databases with one command
MongoDB: Exporting data into files
MongoDB: How to drop databases and collections
MongoDB: Creating capped collections
MongoDB: Returning documents where fields are null or not existing
Denis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.
