Summarize documents by day of the week

Question

For each student in a collection, I have an array of absences. I want to summarize the data by displaying the number of absences for each day of the week.

Given the following input:

{ 
    "_id" : 9373, 
    "absences" : [
        {
            "code" : "U", 
            "date" : ISODate("2021-01-17T00:00:00.000+0000"), 
            "full_day" : false, 
            "remote" : false, 
            "dayNumber" : 1, 
            "dayName" : "Sunday"
        }
    ]
}
{ 
    "_id" : 9406, 
    "absences" : [
        {
            "code" : "E", 
            "date" : ISODate("2020-12-09T00:00:00.000+0000"), 
            "full_day" : false, 
            "remote" : false, 
            "dayNumber" : 4, 
            "dayName" : "Wednesday"
        }, 
        {
            "code" : "U", 
            "date" : ISODate("2021-05-27T00:00:00.000+0000"), 
            "full_day" : false, 
            "remote" : false, 
            "dayNumber" : 5, 
            "dayName" : "Thursday"
        }
    ]
}

How can I achieve the following output:

[
  {
    "_id": 9373,
    "days": [
      {
        "dayNumber": 1,
        "dayName": "Sunday",
        "count": 1
      }
    ]
  },
  {
    "_id": 9406,
    "days": [
      {
        "dayNumber": 4,
        "dayName": "Wednesday",
        "count": 1
      },
      {
        "dayNumber": 5,
        "dayName": "Thursday",
        "count": 1
      }
    ]
  }
]

I've pushed all the required fields to this stage of the pipeline. I'm just not clear how to roll up the data in the nested absences array.

Answer 1

• $unwind deconstruct absences array
• $group by _id and dayNumber, and get count of grouped documents
• $group by _id and reconstruct days array

db.collection.aggregate([
  { $unwind: "$absences" },
  {
    $group: {
      _id: {
        _id: "$_id",
        dayNumber: "$absences.dayNumber"
      },
      dayName: { $first: "$absences.dayName" },
      count: { $sum: 1 }
    }
  },
  {
    $group: {
      _id: "$_id._id",
      days: {
        $push: {
          dayName: "$dayName",
          dayNumber: "$_id.dayNumber",
          count: "$count"
        }
      }
    }
  }
])

Playground