MongoDB sort

Filed Under: MongoDB

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:


{ $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.


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


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


>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;


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


>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;


>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;


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


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.


{ "_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


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.

Comments

  1. Sourabh Rai says:

    You rock dude

Leave a Reply

Your email address will not be published. Required fields are marked *

close
Generic selectors
Exact matches only
Search in title
Search in content
Search in posts
Search in pages