[
{
_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: []
},
]
}
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
}
}
])