MongoDB Aggregate: Incorrect sorting result

Question

I am working on a collection of following format:

{_id:"blah"
"rating" : {
        "star" : "3.0",
        "comment" : "",
        "last_updated" : ISODate("2015-06-13T17:57:23.176Z")
    }
}

I wrote a query to calculate the average ratings. Along with this my query should also returns ratings field(latest rating should appear first).

db.collection('reviews').aggregate(
[{
                    $match: {
                        $and: [{
                            "res_id": resID
                        }, {
                            "rating.star": {
                                $gt: 1
                            }
                        }]
                    }
                }, {
                    $sort: {
                        "rating.last_updated": -1
                    }
                }, {
                    "$group": {
                        "_id": null,
                        avg_rating: {
                            "$avg": "$rating.star"
                        },
                        "reviews": {
                            "$addToSet": {
                                rating: "$rating",
                                uid: "$uid",
                                items: "$items"
                            }
                        },
                        "uids": {
                            "$addToSet": "$uid"
                        }
                    }
                }, {
                    "$project": {
                        "reviews": 1,
                        "avg_rating": 1,
                        "uids": 1
                    }
                }]
);

The average returned in correct, however the reviews info are not in descending order of time.

"avg_rating" : 3.8888888888888888,
"reviews" : [ 
{
    "rating" : {
        "star" : 3.5000000000000000,
        "comment" : "",
        "last_updated" : ISODate("2015-06-13T20:44:05.475Z")
    },
    "uid" : "977124278982354"

}, 
{
    "rating" : {
        "star" : 5,
        "comment" : "",
        "last_updated" : ISODate("2015-06-13T20:44:30.109Z")
    },
    "uid" : "977124278982354"
}, 

I think group reorder the information, so i tried adding sort criteria after $group clause, but in vain.

{
  $group:....
},{ 
$sort: {
    "rating.last_updated": -1
}
},{
"$project": {
    "reviews": 1,
    "av

Answer 1

The issue seems to be that you're trying to do a sort on an array of documents, where $sort only actually works on the root documents.

This might be a good case for the $unwind operator, with that you would end up with a lot of root documents that each have the format:

{
    avg_rating: 3.8888888888888888,
    reviews: {
        "rating" : {
            "star" : 3.5000000000000000,
            "comment" : "",
            "last_updated" : ISODate("2015-06-13T20:44:05.475Z")
        },
        "uid" : "977124278982354"
    }
},
{
    avg_rating: 3.8888888888888888,
    reviews: {
        "rating" : {
            "star" : 5,
            "comment" : "",
            "last_updated" : ISODate("2015-06-13T20:44:30.109Z")
        },
        "uid" : "977124278982354"
    }
},
...

Then you should be able to do a $sort on reviews.rating.last_updated