Find all the tasks assigned to a member inside assignedTo array

user_14359130 0
Viewed 77
[
 {
  _id: 1,
  title: "Task 1",
  assignedTo: ["userId1", "userId2", "userId3"]
  status: "To do"
 },
 {
  _id: 2,
  title: "Task 2",
  assignedTo: ["userId1", "userId2"],
  status: "In progress"
 },
 {
  _id: 3,
  title: "Task 3",
  assignedTo: ["userId3"],
  status: "Completed"
 }
]

I want to use MongoDB aggregate to return all the tasks assigned to a user and group them by status. For example, the input is: userId: "userId1" , and the expected output should be something like this:

{
  results: [
    {
      status: "To do",
      tasks: [
          {
            _id: 1,
            title: "Task 1",
            assignedTo: ["userId1", "userId2", "userId3"]
           }
         ]
    }, 
    {
      status: "In progress",
      tasks: [
          {
            _id: 2,
            title: "Task 2",
            assignedTo: ["userId1", "userId2"]
           }
         ]
    }, 
    {
      status: "Completed",
      tasks: []
    }, 
  ]
}
node.js mongodb aggregate
1 Answers

you can group by status and push items if it matches the userId1

you can test it here mongodb playground

db.collection.aggregate([
  {
    "$group": {
      "_id": {
        status: "$status"
      },
      tasks: {
        "$push": {
          $cond: [
            {
              "$in": [
                "userId1",
                "$assignedTo"
              ]
            },
            {
              _id: "$_id",
              title: "$title",
              assignedTo: "$assignedTo"
            },
            "$$REMOVE"
          ]
        }
      }
    }
  },
  {
    "$project": {
      status: "$_id.status",
      tasks: 1,
      _id: 0
    }
  }
])
user_76289370
Related