Query to get latest three users, sort by date - Mongoose Node.js

Question

This is the data from Mongodb (Task.json)

    {
        "username": "john",
        "taskId": "001",
        "date": "2020-02-18T20:14:19.000Z",
    },
    {
        "username": "david",
        "taskId": "001",
        "date": "2020-02-18T21:48:19.000Z",
    },
    {
        "username": "john",
        "taskId": "002",
        "date": "2020-02-15T20:20:32.000Z",
    }
    ... many more

What I am trying to acheive

• I am trying a write a query that returns taskId with a list of the latest users who executed the task (sorted by date - descending).
• I only want the last three users who executed the task to show up on the list, thus the user array should not contain more the 3 users per task

Here is an example I created of how I want the response to be:

{
  "tasks": [
    {
      "taskid": "001",
      "users": [
        {
          "username": "david",
          "date": "2020-02-18T21:48:19.000Z"
        },
        {
          "username": "john",
          "date": "2020-02-18T20:14:19.000Z"
        }
      ]
    },
    {
      "taskid": "002",
      "users": [
        {
          "username": "john",
          "date": "2020-02-15T20:20:32.000Z"
        }
      ]
    }
  ]
}

My progress so far:

router.route("/latest-tasks").get((req, res) => {
    Task.find()
        .sort({ date: "desc" })
        .then(doc =>
            res.status(200).json({
                taskId: doc.taskId,
                list: doc.map(doc => {
                    return {
                        username: doc.username,
                        date: doc.date
                    };
                })
            })
        )
        .catch(err => res.status(400).json("Error: " + err));
});

Answer 1

A bit of multi-query scenario here, I suggest you use MongoDB aggregation to do this. This query should work:

Todo.aggregate([
  {
    $sort: { taskId: 1, date: 1 }
  },
  {
    $group: {
      _id: "$taskId",
      users: {
        $push: { username: "$username", date: "$date" }
      }
    }
  },
  {
    $project: {
      _id: 0,
      taskid: "$_id",
      users: {
          $filter: {
            input: [
              { $arrayElemAt: ["$users", 0] },
              { $arrayElemAt: ["$users", 1] },
              { $arrayElemAt: ["$users", 2] }
            ],
            as: "user",
            cond: { $ne: ["$$user", null] }
          }
        },
    }
  }
]).exec()
.then(doc => { console.log(doc); })
.catch(err => { console.log(err); });

Aggregation Pipeline Explanation:

• $sort: This sorts the tasks using the taskId and date field in an ascending order
• $group: This group the tasks by taskId. This is how we get all users associated with a task
• $project: This helps extract just the first 3 users associated with a task. The $filter operator used inside this pipeline stage helps to remove null values in case a task does not have up to three users associated with it.

Links: Aggregation Pipeline, Pipeline Stages, Pipeline Operators