MongoDB sort method sorts the document in either ascending or descending order as specified by the user in the input argument.
MongoDB sort
The syntax for MongoDB sort method is:
|
1 2 3 |
{ $sort:{<field1>:<sort order>........}} |
sort order can take the following values:
1: specifies that the field should be sorted in ascending order
-1: specifies that the field should be sorted in descending order
MongoDB sort example
Lets see an example for sorting the fields. Note that you can create the example data using MongoDB insert.
|
1 2 3 4 5 6 7 |
>db.car.find().sort({speed:-1}) { "_id" : ObjectId("5471d72e0ce70a7026ebefee"), "name" : "Esteem", "color" : "JetRed", "cno" : "H414", "mfdcountry" : "Italy", "speed" : 65 } { "_id" : 3, "name" : "Alto", "color" : "Silver", "cno" : "H413", "mfdcountry" : "India", "speed" : 55 } { "_id" : 2, "name" : "Volkswagen", "color" : "Blue", "cno" : "H412", "mfdcountry" : "Japan", "speed" : 52 } { "_id" : 1, "name" : "Polo", "color" : "White", "cno" : "H411", "mfdcountry" : "Germany", "speed" : 45 } |
Sorts the car collection documents in descending order for the speed field.
|
1 2 3 4 5 6 7 |
>db.car.find().sort({name:1}) { "_id" : 3, "name" : "Alto", "color" : "Silver", "cno" : "H413", "mfdcountry" : "India", "speed" : 55 } { "_id" : ObjectId("5471d72e0ce70a7026ebefee"), "name" : "Esteem", "color" : "JetRed", "cno" : "H414", "mfdcountry" : "Italy", "speed" : 65 } { "_id" : 1, "name" : "Polo", "color" : "White", "cno" : "H411", "mfdcountry" : "Germany", "speed" : 45 } { "_id" : 2, "name" : "Volkswagen", "color" : "Blue", "cno" : "H412", "mfdcountry" : "Japan", "speed" : 52 } |
As you can notice, this sorts the car collection documents in ascending order on the name field.
|
1 2 3 4 5 6 7 8 9 |
>db.car.find().sort({speed:-1,name:1}) { "_id" : ObjectId("54729a20ab36ed23e31c68f1"), "name" : "Audi", "color" : "Grey", "cno" : "H415", "mfdcountry" : "Rome", "speed" : 65 } { "_id" : ObjectId("5471d72e0ce70a7026ebefee"), "name" : "Esteem", "color" : "JetRed", "cno" : "H414", "mfdcountry" : "Italy", "speed" : 65 } { "_id" : ObjectId("5472988cab36ed23e31c68f0"), "name" : "skoda", "color" : "JetRed", "cno" : "H415", "mfdcountry" : "Chez", "speed" : 65 } { "_id" : 3, "name" : "Alto", "color" : "Silver", "cno" : "H413", "mfdcountry" : "India", "speed" : 55 } { "_id" : 2, "name" : "Volkswagen", "color" : "Blue", "cno" : "H412", "mfdcountry" : "Japan", "speed" : 52 } { "_id" : 1, "name" : "Polo", "color" : "White", "cno" : "H411", "mfdcountry" : "Germany", "speed" : 45 } |
Above example sorts the car collection in descending order based on speed and then sorts on name in ascending order for the cars having same speed (65).
Limit the results of MongoDB sort operation
If the sort operation exceeds more than 32 megabytes, MongoDB returns an error. To get rid of this error, indexing can be used in conjunction with limit method. Limit results the number of documents to be returned within 32 megabytes.
For example;
|
1 2 3 |
>db.car.find().sort( {speed:-1,name:1 }).limit(10) |
This MongoDB sort operation limits the number of documents returned to 10 and ensures that it is within 32 megabytes limit.
Indexes can be created as shown below.
|
1 2 3 4 5 6 7 8 9 |
>db.car.ensureIndex({ name:1,speed:-1}) { "createdCollectionAutomatically" : false, "numIndexesBefore" : 2, "numIndexesAfter" : 3, "ok" : 1 } |
This ensures that the index is created for the car collection. We can also use Mongo shell createIndex() method for this.
Specifying Projection fields
When user specifies the fields to be projected and sorted the MongoDB engine sorts the resultant documents first.
For example;
|
1 2 3 4 5 6 7 |
>db.car.find({speed: { $gt:14}},{name:1,speed:1}).sort({"speed":-1}) { "_id" : 11, "name" : "Polo", "speed" : 45 } { "_id" : 10, "name" : "Volkswagen", "speed" : 44 } { "_id" : 9, "name" : "Skoda", "speed" : 43 } { "_id" : 12, "name" : "Ecosport", "speed" : 15 } |
This operation sorts the car by its speed in descending order first and then displays only id, name and speed fields in the resultant document.
Natural Ordering of MongoDB sort
The $natural parameter returns all the documents in the order they exist in the database. This ordering basically depicts the order in which the records are inserted except in the conditions where the documents relocate due to update or remove operations.
For example;
|
1 2 3 4 5 6 7 |
>db.car.find().sort( { $natural: 1 }) { "_id" : 9, "name" : "Skoda", "color" : "Red", "cno" : "H622", "mfdcountry" : "Chez", "speed" : 43 } { "_id" : 10, "name" : "Volkswagen", "color" : "Blue", "cno" : "H623", "mfdcountry" : "Germany", "speed" : 44 } { "_id" : 11, "name" : "Polo", "color" : "White", "cno" : "H624", "mfdcountry" : "Japan", "speed" : 45 } { "_id" : 12, "name" : "Ecosport", "color" : "NavyBlue", "cno" : "H625", "mfdcountry" : "Japan", "speed" : 15 } |
The documents are retrieved in the order in which they are inserted into the database.
MongoDB sort Java Program
In this section we will look into a java program to perform sort operation in ascending and descending orders.
Below is the MongoDB sort program for java driver versions 2.x.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 |
package com.journaldev.mongodb; import java.net.UnknownHostException; import java.util.List; import com.mongodb.BasicDBObject; import com.mongodb.DB; import com.mongodb.DBCollection; import com.mongodb.DBCursor; import com.mongodb.DBObject; import com.mongodb.MongoClient; public class MongoDBSort { // method for sorting in ascending order based on name public static void sortAscending() throws UnknownHostException { // Get a new connection to the db assuming that it is running MongoClient m1 = new MongoClient("localhost"); // use test as a datbase,use your database here DB d1 = m1.getDB("test"); // fetch the collection object ,car is used here,use your own DBCollection coll = d1.getCollection("car"); // find method is called and result stored in cursor DBCursor car = coll.find(); // sorting the cursor based in ascending order based on name field car.sort(new BasicDBObject("name", 1)); // iterating throug cursor and printing all the documents stored in // cursor try { while (car.hasNext()) { System.out.println(car.next()); } } finally { car.close(); } } // method for sorting in descending order based on speed public static void sortDescending() throws UnknownHostException { MongoClient m1 = new MongoClient("localhost"); DB d1 = m1.getDB("test"); DBCollection coll = d1.getCollection("car"); DBCursor car = coll.find(); // sorting the cursor based in descending order based on speed field car.sort(new BasicDBObject("speed", -1)); System.out .println("Sorts in Descending order-------------------------------------------"); try { while (car.hasNext()) { System.out.println(car.next()); } } finally { car.close(); } } // method for sorting in descending order based on speed and ascending order // based on name public static void sortDescendingAscending() throws UnknownHostException { MongoClient m1 = new MongoClient("localhost"); DB d1 = m1.getDB("test"); DBCollection coll = d1.getCollection("car"); DBCursor car = coll.find(); // sort speed in descending order then name in ascending order car.sort(new BasicDBObject("speed", -1).append("name", 1)); System.out .println("Combining two fields to sort in ascending and descending orders-----------------"); try { while (car.hasNext()) { System.out.println(car.next()); } } finally { car.close(); } } public static void sortlimit() throws UnknownHostException { MongoClient m1 = new MongoClient("localhost"); DB d1 = m1.getDB("test"); DBCollection coll = d1.getCollection("car"); DBObject q1 = new BasicDBObject("speed", new BasicDBObject("$gt", 15)); BasicDBObject fields = new BasicDBObject("name", 1).append("speed", 1); // find method is called and result stored //fetch the collection object // ,car is used here,use your ownin cursor DBCursor car = coll.find(q1, fields); // sorting the cursor based in ascending order based on name field car.sort(new BasicDBObject("name", -1)).limit(2); System.out.println("limit--------------------------"); // iterating throug cursor and printing all the documents stored in // cursor try { while (car.hasNext()) { System.out.println(car.next()); } } finally { car.close(); } } public static void sortProjectionfields() throws UnknownHostException { MongoClient m1 = new MongoClient("localhost"); DB db = m1.getDB("test"); DBCollection col = db.getCollection("car"); DBObject query = new BasicDBObject("speed", new BasicDBObject("$gt", 40)); // fields with name and speed field is specified and only these fields // are displayed BasicDBObject fields = new BasicDBObject("name", 1).append("speed", 1); DBCursor carCursor1 = col.find(query, fields); System.out .println("------------------------------------------------------"); try { while (carCursor1.hasNext()) { System.out.println(carCursor1.next()); } } finally { carCursor1.close(); } } public static void sortnaturalordering() throws UnknownHostException { MongoClient m1 = new MongoClient("localhost"); DB d1 = m1.getDB("test"); DBCollection coll = d1.getCollection("car"); DBObject q1 = new BasicDBObject("speed", new BasicDBObject("$gt", 15)); BasicDBObject fields = new BasicDBObject("name", 1).append("speed", 1); // find method is called and result stored // fetch the collection object ,car is used here,use your own cursor DBCursor car = coll.find(q1, fields); // sorting the cursor based in ascending order based on name field car.sort(new BasicDBObject("$natural", -1)); System.out.println("natural ordering---------------"); // iterating through cursor and printing all the documents stored in // cursor try { while (car.hasNext()) { System.out.println(car.next()); } } finally { car.close(); } } public static void createIndex(String on, int type) throws UnknownHostException { MongoClient m1 = new MongoClient("localhost"); DB d1 = m1.getDB("test"); DBCollection coll = d1.getCollection("car"); coll.createIndex(new BasicDBObject(on, type)); System.out.println("created index---------------------"); List<DBObject> list = coll.getIndexInfo(); for (DBObject o : list) { System.out.println(o); } } public static void main(String[] args) throws UnknownHostException { // invoking methods for performing sorting sortAscending(); sortDescending(); sortDescendingAscending(); sortlimit(); sortProjectionfields(); sortnaturalordering(); createIndex("name", 1); } } |
Above program results in following output.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
{ "_id" : 3.0 , "name" : "Alto" , "color" : "Silver" , "cno" : "H413" , "mfdcountry" : "India" , "speed" : 55.0} { "_id" : { "$oid" : "5471d72e0ce70a7026ebefee"} , "name" : "Esteem" , "color" : "JetRed" , "cno" : "H414" , "mfdcountry" : "Italy" , "speed" : 65.0} { "_id" : 1.0 , "name" : "Polo" , "color" : "White" , "cno" : "H411" , "mfdcountry" : "Germany" , "speed" : 45.0} { "_id" : 2.0 , "name" : "Volkswagen" , "color" : "Blue" , "cno" : "H412" , "mfdcountry" : "Japan" , "speed" : 52.0} Sorting in Descending order------------------------------------------- { "_id" : { "$oid" : "5471d72e0ce70a7026ebefee"} , "name" : "Esteem" , "color" : "JetRed" , "cno" : "H414" , "mfdcountry" : "Italy" , "speed" : 65.0} { "_id" : 3.0 , "name" : "Alto" , "color" : "Silver" , "cno" : "H413" , "mfdcountry" : "India" , "speed" : 55.0} { "_id" : 2.0 , "name" : "Volkswagen" , "color" : "Blue" , "cno" : "H412" , "mfdcountry" : "Japan" , "speed" : 52.0} { "_id" : 1.0 , "name" : "Polo" , "color" : "White" , "cno" : "H411" , "mfdcountry" : "Germany" , "speed" : 45.0} sorting ascending descending----------------- { "_id" : { "$oid" : "54729a20ab36ed23e31c68f1"} , "name" : "Audi" , "color" : "Grey" , "cno" : "H415" , "mfdcountry" : "Rome" , "speed" : 65.0} { "_id" : { "$oid" : "5471d72e0ce70a7026ebefee"} , "name" : "Esteem" , "color" : "JetRed" , "cno" : "H414" , "mfdcountry" : "Italy" , "speed" : 65.0} { "_id" : { "$oid" : "5472988cab36ed23e31c68f0"} , "name" : "skoda" , "color" : "JetRed" , "cno" : "H415" , "mfdcountry" : "Chez" , "speed" : 65.0} { "_id" : 3.0 , "name" : "Alto" , "color" : "Silver" , "cno" : "H413" , "mfdcountry" : "India" , "speed" : 55.0} { "_id" : 2.0 , "name" : "Volkswagen" , "color" : "Blue" , "cno" : "H412" , "mfdcountry" : "Japan" , "speed" : 52.0} { "_id" : 1.0 , "name" : "Polo" , "color" : "White" , "cno" : "H411" , "mfdcountry" : "Germany" , "speed" : 45.0} limit-------------------------- { "_id" : 10.0 , "name" : "Volkswagen" , "speed" : 44.0} { "_id" : 9.0 , "name" : "Skoda" , "speed" : 43.0} ------------------------------------------------------ { "_id" : 11.0 , "name" : "Polo" , "speed" : 45.0} { "_id" : 10.0 , "name" : "Volkswagen" , "speed" : 44.0} { "_id" : 9.0 , "name" : "Skoda" , "speed" : 43.0} natural ordering--------------- { "_id" : 11.0 , "name" : "Polo" , "speed" : 45.0} { "_id" : 10.0 , "name" : "Volkswagen" , "speed" : 44.0} { "_id" : 9.0 , "name" : "Skoda" , "speed" : 43.0} created index--------------------- { "v" : 1 , "key" : { "_id" : 1} , "name" : "_id_" , "ns" : "test.car"} { "v" : 1 , "key" : { "speed" : -1.0 , "name" : 1.0} , "name" : "speed_-1_name_1" , "ns" : "test.car"} { "v" : 1 , "key" : { "name" : 1.0 , "speed" : -1.0} , "name" : "name_1_speed_-1" , "ns" : "test.car"} { "v" : 1 , "key" : { "name" : 1} , "name" : "name_1" , "ns" : "test.car"} |
If you are using MongoDB java driver version 3.x, below code should work. It’s tested with version 3.5.0
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 |
package com.journaldev.mongodb.main; import java.net.UnknownHostException; import java.util.List; import org.bson.Document; import com.mongodb.BasicDBObject; import com.mongodb.DB; import com.mongodb.DBCollection; import com.mongodb.DBCursor; import com.mongodb.DBObject; import com.mongodb.MongoClient; import com.mongodb.client.FindIterable; import com.mongodb.client.MongoCollection; import com.mongodb.client.MongoCursor; import com.mongodb.client.MongoDatabase; public class MongoDBSort { // method for sorting in ascending order based on name public static void sortAscending() throws UnknownHostException { // Get a new connection to the db assuming that it is running MongoClient mc = new MongoClient("localhost"); // use test as a datbase,use your database here MongoDatabase db = mc.getDatabase("journaldev"); // fetch the collection object ,car is used here,use your own MongoCollection<Document> coll = db.getCollection("car"); // find method is called and result stored in cursor FindIterable<Document> car = coll.find(); // sorting the cursor based in ascending order based on name field car.sort(new BasicDBObject("name", 1)); // iterating through cursor and printing all the documents stored in cursor MongoCursor<Document> iterator = car.iterator(); try { while (iterator.hasNext()) { System.out.println(iterator.next()); } } finally { iterator.close(); mc.close(); } } // method for sorting in descending order based on speed public static void sortDescending() throws UnknownHostException { MongoClient mc = new MongoClient("localhost"); MongoDatabase db = mc.getDatabase("test"); MongoCollection<Document> coll = db.getCollection("car"); FindIterable<Document> car = coll.find(); // sorting the cursor based in descending order based on speed field car.sort(new BasicDBObject("speed", -1)); System.out.println("Sorts in Descending order"); MongoCursor<Document> iterator = car.iterator(); try { while (iterator.hasNext()) { System.out.println(iterator.next()); } } finally { iterator.close(); mc.close(); } } // method for sorting in descending order based on speed and ascending order // based on name public static void sortDescendingAscending() throws UnknownHostException { MongoClient mc = new MongoClient("localhost"); MongoDatabase db = mc.getDatabase("test"); MongoCollection<Document> coll = db.getCollection("car"); FindIterable<Document> car = coll.find(); // sort speed in descending order then name in ascending order car.sort(new BasicDBObject("speed", -1).append("name", 1)); System.out.println("Combining two fields to sort in ascending and descending orders"); MongoCursor<Document> iterator = car.iterator(); try { while (iterator.hasNext()) { System.out.println(iterator.next()); } } finally { iterator.close(); mc.close(); } } public static void sortlimit() throws UnknownHostException { MongoClient mc = new MongoClient("localhost"); MongoDatabase db = mc.getDatabase("test"); MongoCollection<Document> coll = db.getCollection("car"); BasicDBObject q1 = new BasicDBObject("speed", new BasicDBObject("$gt", 15)); // find method is called and result stored //fetch the collection object // ,car is used here,use your ownin cursor FindIterable<Document> car = coll.find(q1); // sorting the cursor based in ascending order based on name field car.sort(new BasicDBObject("name", -1)).limit(2); System.out.println("limit example"); // iterating through cursor and printing all the documents stored in // cursor MongoCursor<Document> iterator = car.iterator(); try { while (iterator.hasNext()) { System.out.println(iterator.next()); } } finally { iterator.close(); mc.close(); } } public static void sortProjectionfields() throws UnknownHostException { MongoClient mc = new MongoClient("localhost"); MongoDatabase db = mc.getDatabase("test"); MongoCollection<Document> col = db.getCollection("car"); BasicDBObject query = new BasicDBObject("speed", new BasicDBObject("$gt", 40)); FindIterable<Document> carCursor1 = col.find(query); System.out.println("------------------------------------------------------"); MongoCursor<Document> iterator = carCursor1.iterator(); try { while (iterator.hasNext()) { System.out.println(iterator.next()); } } finally { iterator.close(); mc.close(); } } public static void sortnaturalordering() throws UnknownHostException { MongoClient mc = new MongoClient("localhost"); MongoDatabase db = mc.getDatabase("test"); MongoCollection<Document> coll = db.getCollection("car"); BasicDBObject q1 = new BasicDBObject("speed", new BasicDBObject("$gt", 15)); BasicDBObject fields = new BasicDBObject("name", 1).append("speed", 1); // find method is called and result stored // fetch the collection object ,car is used here,use your own cursor FindIterable<Document> car = coll.find(q1); // sorting the cursor based in ascending order based on name field car.sort(new BasicDBObject("$natural", -1)); System.out.println("natural ordering---------------"); // iterating through cursor and printing all the documents stored in // cursor MongoCursor<Document> iterator = car.iterator(); try { while (iterator.hasNext()) { System.out.println(iterator.next()); } } finally { iterator.close(); mc.close(); } } public static void createIndex(String on, int type) throws UnknownHostException { MongoClient mc = new MongoClient("localhost"); MongoDatabase db = mc.getDatabase("test"); MongoCollection<Document> coll = db.getCollection("car"); String indexName = coll.createIndex(new BasicDBObject(on, type)); System.out.println("created index name="+indexName); MongoCursor<Document> iterator = coll.listIndexes().iterator(); try { while (iterator.hasNext()) { System.out.println(iterator.next()); } } finally { iterator.close(); mc.close(); } } public static void main(String[] args) throws UnknownHostException { // invoking methods for performing sorting sortAscending(); sortDescending(); sortDescendingAscending(); sortlimit(); sortProjectionfields(); sortnaturalordering(); createIndex("name", 1); } } |
That’s all for sorting documents in MongoDB, we will look into more of MongoDB features in coming posts.