Sort on nested column with aggregation

Question

Sort on nested column with aggregation

I have following query using aggregation framework in Mongoose:

Comment.aggregate([{
   $match: {
     isActive: true
   }
 }, {
   $group: {
     _id: {
       year: {
         $year: "$creationDate"
       },
       month: {
         $month: "$creationDate"
       },
       day: {
         $dayOfMonth: "$creationDate"
       }
     },
     comments: {
       $push: {
         comment: "$comment",
         username: "$username",
         creationDate: "$creationDate",
       }
     }
   }
 }, {
   $sort: {
     'comments.creationDate': -1
   }
 }, {
   $limit: 40
 }], function (err, comments) {
   //...
 });

Finally, I want to sort the records using creationDate inside comments array. I've used comments.creationDate but it doesn't work!

What is the correct approach to sort items using aggregation framework?

node.js
mongodb
mongoose

Answer 1

You need to move your $sort on creationDate above the $group so that it affects the order the comments array is built using $push. As you have it now, you're sorting the overall set of docs, not the array.

Comment.aggregate([{
   $match: {
     isActive: true
   }
 }, {
   $sort: {
     creationDate: -1
   }
 }, {
   $group: {
     _id: {
       year: {
         $year: "$creationDate"
       },
       month: {
         $month: "$creationDate"
       },
       day: {
         $dayOfMonth: "$creationDate"
       }
     },
     comments: {
       $push: {
         comment: "$comment",
         username: "$username",
         creationDate: "$creationDate",
       }
     }
   }
 }, {
   $limit: 40
 }], function (err, comments) {
   //...
 });