Dec 3, 2015

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