Skip to main content

$sort (aggregation) in MongoDB


$sort is responsible to sorting input document and returns it to pipeline. If you aware with relational Database like SQL server $sort work same as ORDER BY clause.

Syntax:


{ $sort: { <field1>: <sort order>, <field2>: <sort order> ... } }


$sort use to sort result in ascending or descending order according particular field(s). For ascending and descending we used following values.

  • 1 for the ascending.
  • -1 for descending

Example:

Suppose we have a collection named “myCollection “as below.


{ "uid" : 1, " Book  Title": "book1", "price" : 20, "quantity" : 1, "date" : ISODate("2016-08-05T07:00:00Z") }
{ "uid" : 2, " Book Title " : " book2", "price" : 10, "quantity" : 2, "date" : ISODate("2016-08-05T08:00:00Z") }
{ "uid" : 3, " Book Title " : " book3", "price" : 30, "quantity" : 4, "date" : ISODate("2016-08-17T10:00:00Z") }
{ "uid" : 4, " Book Title " : " book4", "price" : 10, "quantity" : 2, "date" : ISODate("2014-09-01T11:20:39.736Z") }
{ "uid" : 5, " Book Title " : " book5", "price" : 20, "quantity" : 6, "date" : ISODate("2014-09-04T20:23:13.331Z") }


Execute following query:


db.myCollection.aggregate(
   [
     { $sort : { quantity : -1 } }
   ]
)


Result: Above query returns documents as sorted by quantity descending. 


/* 1 */
{
    "_id" : ObjectId("5767b6e21130fb71878298b2"),
    "uid" : 5,
    " Book Title " : " book5",
    "price" : 20,
    "quantity" : 6,
    "date" : ISODate("2014-09-04T20:23:13.331Z")
}

/* 2 */
{
    "_id" : ObjectId("5767b6e21130fb71878298b0"),
    "uid" : 3,
    " Book Title " : " book3",
    "price" : 30,
    "quantity" : 4,
    "date" : ISODate("2016-08-17T10:00:00.000Z")
}

/* 3 */
{
    "_id" : ObjectId("5767b6e21130fb71878298af"),
    "uid" : 2,
    " Book Title " : " book2",
    "price" : 10,
    "quantity" : 2,
    "date" : ISODate("2016-08-05T08:00:00.000Z")
}

/* 4 */
{
    "_id" : ObjectId("5767b6e21130fb71878298b1"),
    "uid" : 4,
    " Book Title " : " book4",
    "price" : 10,
    "quantity" : 2,
    "date" : ISODate("2014-09-01T11:20:39.736Z")
}

/* 5 */
{
    "_id" : ObjectId("5767b6e21130fb71878298ae"),
    "uid" : 1,
    " Book  Title" : "book1",
    "price" : 20,
    "quantity" : 1,
    "date" : ISODate("2016-08-05T07:00:00.000Z")
}


Same like above query we can apply ascending for the result.


db.myCollection.aggregate(
   [
     { $sort : { quantity : 1 } }
   ]
)



Result:


/* 1 */
{
    "_id" : ObjectId("5767b6e21130fb71878298ae"),
    "uid" : 1,
    " Book  Title" : "book1",
    "price" : 20,
    "quantity" : 1,
    "date" : ISODate("2016-08-05T07:00:00.000Z")
}

/* 2 */
{
    "_id" : ObjectId("5767b6e21130fb71878298af"),
    "uid" : 2,
    " Book Title " : " book2",
    "price" : 10,
    "quantity" : 2,
    "date" : ISODate("2016-08-05T08:00:00.000Z")
}

/* 3 */
{
    "_id" : ObjectId("5767b6e21130fb71878298b1"),
    "uid" : 4,
    " Book Title " : " book4",
    "price" : 10,
    "quantity" : 2,
    "date" : ISODate("2014-09-01T11:20:39.736Z")
}

/* 4 */
{
    "_id" : ObjectId("5767b6e21130fb71878298b0"),
    "uid" : 3,
    " Book Title " : " book3",
    "price" : 30,
    "quantity" : 4,
    "date" : ISODate("2016-08-17T10:00:00.000Z")
}

/* 5 */
{
    "_id" : ObjectId("5767b6e21130fb71878298b2"),
    "uid" : 5,
    " Book Title " : " book5",
    "price" : 20,
    "quantity" : 6,
    "date" : ISODate("2014-09-04T20:23:13.331Z")
}


Note: The $sort stage has a limit of 100 megabytes of RAM.

Popular posts from this blog

Remove special character from string in MongoDB

Problem: Suppose wehave a collection and one field is type string contains some special character (like !@#$%) and we don’t want these special character.
Solution: We can easily remove the special character from field using script “replace(/[^a-zA-Z 0-9 ]/g, '')” in our query.  How can we remove special character from string using this script please see following example.
Example: Suppose we have a collection “EduSurvey “where we are collecting information from institutions.

{Name:"JB institute”, About:"This is good one collage for MBA", Information:"This $%%institute ##has good faculty etc$$"}
{Name:"MK institute”, About:"This is good one collage for MCA", Information:"This$$%# is the dummy text12"}
{Name:"MG institute”, About:"This is good one collage for B,Tech", Information:"This# institute@ has&* good infrastructure"}

Did you notice Information fields contains some special character so we…

Add day to ISODate in MongoDB

We can use $add operator to add days in ISODate in mongodb, $add is the Arithmetic Aggregation Operator which adds number and date in mongodb.
Syntax:

{ $add: [ <expression1>, <expression2>, ... ] }

Note:  If one of the argument is date $add operator treats to other arguments as milliseconds to add to the date.
Example: Suppose we have a Test collection as below.

{"Title" : "Add day to ISODate in MongoBD","CreatedDate" : ISODate("2016-07-07T08:00:00.000Z")}

Query to add 2 days in CreatedDate

db.Test.aggregate([      { $project: { Title: 1, AddedDate: { $add: [ "$CreatedDate", 2*24*60*60000 ] } } }    ])

Result:

{ "_id" : ObjectId("579a1567ac1b3f3732483de0"), "Title" : "Add day to ISODate in MongoBD", "AddedDate" : ISODate("2016-07-09T08:00:00.000Z") }

Note: As mentioned in above note we have to convert days in millisecond because $add operator treat to other arg…