I am trying to group the following data in mongodb first by dates then by Task_type and get total duration. I am trying to achieve it by using $group aggregation and $sum, but I am only getting sum of duration of particular date. I need it one step further by separating Task_type

{
    "name" : "Employee1",
    "Mail" : "[email protected]",
    "Contact" : 1111111111,
    "Department" : "Operations",
    "Joining_Date" : "2022-02-02",
    "Password" : "Employee1",
    "Work" : 
    [
        {
            "date" : "2022-10-07",
            "Tasks" : {"Task_description" : "Worked on project","Task_type" : "Work","Start_time" : "09:20:00","duration" : 20}
                       
        },
        {
            "date" : "2022-10-07",
            "Tasks" : {"Task_description" : "Attended daily meeting","Task_type" : "Meeting","Start_time" : "10:30:00","duration" : 60}
        },
        {
            "date" : "2022-10-06",
            "Tasks" : {"Task_description" : "Lunch break","Task_type" : "Break","Start_time" : "13:00:00","duration" : 50}
        },
        {
            "date" : "2022-10-06",
            "Tasks" : {"Task_description" : "Tea Break","Task_type" : "Break","Start_time" : "17:30:00","duration" : 10}
        },
        {
            "date" : "2022-10-08",
            "Tasks" : {"Task_description" : "Meeting with partners","Task_type" : "Meeting","Start_time" : "13:00:00","duration" : 50}
        }
        
    ]
    
}

My desired output is:

{_id:'2022-10-07', WorkDuration:20, MeetingDuration:60, BreakDuration:0},
{_id:'2022-10-06', WorkDuration:0, MeetingDuration:0, BreakDuration:60},
{_id:'2022-10-08', WorkDuration:0, MeetingDuration:50, BreakDuration:0}

CodePudding user response：

One option is to $group twice. First by both Task_type and date, and then only by date, summing up the duration according to each Task_type:

db.collection.aggregate([
  {$unwind: "$Work"},
  {$project: {
      _id: 0,
      date: "$Work.date",
      Task_type: "$Work.Tasks.Task_type",
      duration: "$Work.Tasks.duration"
  }},
  {$group: {
      _id: {Task_type: "$Task_type", "date": "$date"},
      duration: {$sum: "$duration"}
  }},
  {$group: {
      _id: "$_id.date",
      WorkDuration: {$sum: {$cond: [{$eq: ["$_id.Task_type", "Work"]}, "$duration", 0]}},
      MeetingDuration: {$sum: {$cond: [{$eq: ["$_id.Task_type", "Meeting"]}, "$duration", 0]}},
      BreakDuration: {$sum: {$cond: [{$eq: ["$_id.Task_type", "Break"]}, "$duration", 0]}}
  }},
  {$sort: {_id: 1}}
])

See how it works on the playground example