Subscribe via RSS Feed

Using sort method in mongodb

July 15, 2014 1 Comment

In each database we maintain data in many ways. Regarding those ways we also have to consider the possibilities of sorting data in many ways regarding of many criteria. In this article we are going to understand different ways for sorting.

Using sort method:

sort() method accepts a document containing list of fields along with their sorting order. To specify sorting order 1 and -1 are used. 1 is used for ascending order while -1 is used for descending order.
Let us view a basic syntax:

db.COLLECTION_NAME.find().sort({KEY:1})

Using ensureIndex():

Going along with the sort method we can also organize data with ensureIndex() method. Without index method mongodb has to scan the whole document every time, which could be strenuous and time consuming in case of BLOB data. It helps in efficient resolution of queries. Indexes are special data structures, that store a small portion of the data set in an easy to traverse form. The index stores the value of a specific field or set of fields, ordered by the value of the field as specified in index.
Below is a basic syntax:

db.COLLECTION_NAME.ensureIndex({KEY:1})

ensureIndex() method also accepts list of options:

  • background
  • unique
  • name
  • droDups
  • sparse
  • expireAfterSeconds
  • v
  • weights
  • default_language
  • language_override

Sort With a Subset of Compound Index

If the sort document contains a subset of the compound index fields, the subset can determine whether MongoDB can use the index efficiently to both retrieve and sort the query results. If MongoDB can efficiently use the index to both retrieve and sort the query results, the output from the explain() will display scan And Order as false or 0. If MongoDB can only use the index for retrieving documents that meet the query criteria, MongoDB must manually sort the resulting documents without the use of the index. For in-memory sort operations, explain() will display scan And Order as true or 1.

Sort Subset Starts at the Index Beginning

If the sort document is a subset of a compound index and starts from the beginning of the index, MongoDB can use the index to both retrieve and sort the query results.
For example, the collection collection has the following index:


{ a: 1, b: 1, c: 1, d: 1 }

The following operations include a sort with a subset of the index. Because the sort subset starts at beginning of the index, the operations can use the index for both the query retrieval and sort:


db.collection.find().sort( { a:1 } )
db.collection.find().sort( { a:1, b:1 } )
db.collection.find().sort( { a:1, b:1, c:1 } )

Using cursor.sort():

So, aside from the above we have another type of sorting mechanism in mongodb. Which is called cursor.sort(). It specifies the order in which the query returns matching documents.
When comparing values of different BSON types, MongoDB uses the following comparison order:

  • MinKey
  • Null
  • Numbers
  • Symbol, String
  • Object
  • Array
  • BinData
  • ObjectId
  • Boolean
  • Date, Timestamp
  • Regular Expression
  • MaxKey

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.
A basic example is like:

cursor.sort(sort)

In the above query we can see that sort is acting as a parameter in cursor.sort(). Whereas sort has a field value relation just like

{field:value}

Metadata Sort

Specify in the sort parameter a new field name for the computed metadata and specify the $meta expression as its value.
The following sample document specifies a descending sort by the “textScore” metadata:

{ score: { $meta: "textScore" } }

The specified metadata determines the sort order. For example, the “textScore” metadata sorts in descending order. See $meta for details.

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)

Return in Storage Order

The $natural parameter returns items according to their storage order within the collection level extents.
Typically, the storage 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.
The following query returns the documents in the storage order:


db.collection_name.find().sort( { $natural: 1 } )

Using db.collection.find()

Now let us talk about the db.collection.find() method. The find() method selects documents in a collection and returns a cursor to the selected documents.
The find() method takes the following parameters.
Parameters:
query (document) – Optional. Specifies the selection criteria using query operators. Omit the query parameter or pass an empty document (e.g. {}) to return all documents in the collection.
projection–
The projection argument will resemble the following prototype:


{ field1: boolean, field2: boolean ... }

The boolean can take the following include or exclude values:

  • 1 or true to include. The find() method always includes the _id field even if the field is not explicitly stated to return in the projection parameter.
  • 0 or false to exclude.
  • The projection cannot contain both include and exclude specifications except for the exclusion of the _id field.
  • Omit the projection parameter to return all the fields in the matching documents.

Returns:
A cursor to the documents that match the query criteria. If the projection argument is specified, the matching documents contain only the projection fields, and the _id field if you do not explicitly exclude the _id field.
Consider the following examples of the find() method:


db.databse_name.find()

we can use find() in many ways for sorting data more effectively. We are going to discuss about these in the following:

Find All Documents in a Collection

The find() method with no parameters returns all documents from a collection and returns all fields for the documents. For example, the following operation returns all documents in the bios collection:


db.bios.find()

Find Documents that Match Query Criteria

To find documents that match a set of selection criteria, call find() with the parameter. The following operation returns all the documents from the collection products where qty is greater than 25:


db.products.find( { qty: { $gt: 25 } } )

Query for Equality

The following operation returns documents in the bios collection where _id equals 5:


db.bios.find( { _id: 5 } )

Query Using Operators

The following operation returns documents in the bios collection where _id equals either 5 or ObjectId(“507″):


db.bios.find(
   {
      _id: { $in: [ 5,  ObjectId("507") ] }

Query for Ranges

Combine comparison operators to specify ranges. The following operation returns documents with field between value1 and value2:


db.collection.find( { field: { $gt: value1, $lt: value2 } } );

Query a Field that Contains an Array

If a field contains an array and your query has multiple conditional operators, the field as a whole will match if either a single array element meets the conditions or a combination of array elements meet the conditions.
The following query:


db.students.find( { score: { $gt: 0, $lt: 2 } } )

There are indeed many other options we can use to sort data in different manner. We will discuss about them as we further progress on this road.

Related Links:

1> How to Get Started with MongoDB Database?
2> How to Get Started with MongoDB?
3> How to Import and Export Through Mongodb?
4> How to Use Projection in MongoDB?
5> Map-Reduce in MongoDB
6> Introduction to Replication in MongoDB
7> Deploying a Replica Set in MongoDB
8> Discussing Replication Lag in MongoDB
9> Replica Set Members in Mongodb
10> Working with Sharding in MongoDB
11> Working with Index in MongoDB
12> Working with Aggregation in MongoDB
13> How to Work with Aggregation Framework in MongoDB?
14> Working with Pipeline Concept in MongoDB
15> Discussing about Pipeline Expression in MongoDB

If you find this article helpful, you can connect us in Google+ and Twitter.