MongoDB: Sort in combination with Aggregation group

Question

I have a collection called transaction with below documents,

/* 0 */
{
    "_id" : ObjectId("5603fad216e90d53d6795131"),
    "statusId" : "65c719e6727d",
    "relatedWith" : "65c719e67267",
    "status" : "A",
    "userId" : "100",
    "createdTs" : ISODate("2015-09-24T13:15:36.609Z")
}

/* 1 */
{
    "_id" : ObjectId("5603fad216e90d53d6795134"),
    "statusId" : "65c719e6727d",
    "relatedWith" : "65c719e6726d",
    "status" : "B",
    "userId" : "100",
    "createdTs" : ISODate("2015-09-24T13:14:31.609Z")
}

/* 2 */
{
    "_id" : ObjectId("5603fad216e90d53d679512e"),
    "statusId" : "65c719e6727d",
    "relatedWith" : "65c719e6726d",
    "status" : "C",
    "userId" : "100",
    "createdTs" : ISODate("2015-09-24T13:13:36.609Z")
}

/* 3 */
{
    "_id" : ObjectId("5603fad216e90d53d6795132"),
    "statusId" : "65c719e6727d",
    "relatedWith" : "65c719e6726d",
    "status" : "D",
    "userId" : "100",
    "createdTs" : ISODate("2015-09-24T13:16:36.609Z")
}

When I run the below Aggregation query without $group,

db.transaction.aggregate([
    {
        "$match": {
            "userId": "100",
            "statusId": "65c719e6727d"
        }
    },
    {
        "$sort": {
            "createdTs": -1
        }
    }
])

I get the result in expected sorting order. i.e Sort createdTs in descending order (Minimal result)

/* 0 */
{
    "result" : [ 
        {
            "_id" : ObjectId("5603fad216e90d53d6795132"),
            "createdTs" : ISODate("2015-09-24T13:16:36.609Z")
        }, 
        {
            "_id" : ObjectId("5603fad216e90d53d6795131"),
            "createdTs" : ISODate("2015-09-24T13:15:36.609Z")
        }, 
        {
            "_id" : ObjectId("5603fad216e90d53d6795134"),
            "createdTs" : ISODate("2015-09-24T13:14:31.609Z")
        }, 
        {
            "_id" : ObjectId("5603fad216e90d53d679512e"),
            "createdTs" : ISODate("2015-09-24T13:13:36.609Z")
        }
    ],
    "ok" : 1
}

If I apply the below aggregation with $group, the resultant is inversely sorted(i.e Ascending sort)

db.transaction.aggregate([
    {
        "$match": {
            "userId": "100",
            "statusId": "65c719e6727d"
        }
    },
    {
        "$sort": {
            "createdTs": -1
        }
    },
    {
        $group: {
                "_id": {
                    "statusId": "$statusId",
                    "relatedWith": "$relatedWith",
                    "status": "$status"
                },
                "status": {$first: "$status"}, 
                "statusId": {$first: "$statusId"},
                "relatedWith": {$first: "$relatedWith"},
                "createdTs": {$first: "$createdTs"}
        }
    }
]);

I get the result in inverse Order i.e. ** Sort createdTs in Ascending order**

/* 0 */
{
    "result" : [ 
        {
            "_id" : ObjectId("5603fad216e90d53d679512e"),
            "createdTs" : ISODate("2015-09-24T13:13:36.609Z")
        }, 
        {
            "_id" : ObjectId("5603fad216e90d53d6795134"),
            "createdTs" : ISODate("2015-09-24T13:14:31.609Z")
        }, 
        {
            "_id" : ObjectId("5603fad216e90d53d6795131"),
            "createdTs" : ISODate("2015-09-24T13:15:36.609Z")
        }, 
        {
            "_id" : ObjectId("5603fad216e90d53d6795132"),
            "createdTs" : ISODate("2015-09-24T13:16:36.609Z")
        }
    ],
    "ok" : 1
}

Where am I wrong ?

Answer 1

You are not doing anything wrong here, Its a $group behavior in Mongodb

Lets have a look in this example

Suppose you have following doc in collection

{ "_id" : 1, "item" : "abc", "price" : 10, "quantity" : 2, "date" : ISODate("2014-01-01T08:00:00Z") }
{ "_id" : 2, "item" : "jkl", "price" : 20, "quantity" : 1, "date" : ISODate("2014-02-03T09:00:00Z") }
{ "_id" : 3, "item" : "xyz", "price" : 5, "quantity" : 5, "date" : ISODate("2014-02-03T09:05:00Z") }
{ "_id" : 4, "item" : "abc", "price" : 10, "quantity" : 10, "date" : ISODate("2014-02-15T08:00:00Z") }
{ "_id" : 5, "item" : "xyz", "price" : 5, "quantity" : 10, "date" : ISODate("2014-02-15T09:05:00Z") }
{ "_id" : 6, "item" : "xyz", "price" : 5, "quantity" : 5, "date" : ISODate("2014-02-15T12:05:10Z") }
{ "_id" : 7, "item" : "xyz", "price" : 5, "quantity" : 10, "date" : ISODate("2014-02-15T14:12:12Z") }

Now if you run this

db.collection.aggregate([{ $sort: { item: 1,date:1}} ] )

the output will be in ascending order of item and date.

Now if you add group stage in aggregation pipeline it will reverse the order.

db.collection.aggregate([{ $sort: { item: 1,date:1}},{$group:{_id:"$item"}} ] ) 

Output will be

{ "_id" : "xyz" }
{ "_id" : "jkl" }
{ "_id" : "abc" }

Now the solution for your problem

change "createdTs": -1 to "createdTs": 1 for group

Answer 2

The $group stage doesn't insure the ordering of the results. See here the first paragraph. If you want the results to be sorted after a $group, you need to add a $sort after the $group stage.

In your case, you should move the $sort after the $group and before you ask the question : No, the $sort won't be able to use an index after the $group like it does before the $group :-).

The internal algorithm of $group seems to keep some sort of ordering (reversed apparently), but I would not count on that and add a $sort.