Consider I have a Tasks collection like this (each task can be assigned to many users and has a status):

[
{
_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: ["userId2", "userId3"],
status: "In progress"
}
]

I want to use MongoDB aggregate to analyze how many tasks a user is assigned based on status. For example, the expected output should be something like this:

[
{
  _id: "userId1",
  tasks: [
    {
      status: "To do",
      numTasks: 1
    }, 
    {
      status: "In progress",
      numTasks: 1
    }, 
  ]
},
{
  _id: "userId2",
  tasks: [
    {
      status: "To do",
      numTasks: 1
    }, 
    {
      status: "In progress",
      numTasks: 2
    }, 
  ]
},
{
  _id: "userId",
  tasks: [
    {
      status: "To do",
      numTasks: 1
    }, 
    {
      status: "In progress",
      numTasks: 1
    }, 
  ]
}
]

CodePudding user response：

1. $unwind - Deconstruct assignedTo array to multiple documents.
2. $group - Group by assignedTo and status for calculating numTasks.
3. $group - Group by assignedTo and add tasks field with the result of (2).

db.collection.aggregate([
  {
    "$unwind": "$assignedTo"
  },
  {
    "$group": {
      "_id": {
        assignedTo: "$assignedTo",
        status: "$status"
      },
      "numTasks": {
        "$sum": 1
      }
    }
  },
  {
    "$group": {
      "_id": "$_id.assignedTo",
      "tasks": {
        "$push": {
          status: "$_id.status",
          "numTasks": "$numTasks"
        }
      }
    }
  }
])

Sample Mongo Playground