如何将MongoDB聚合返回的对象数组按日期、标题和用户名分组？

原始聚合结果 #

[
  {
    "_id": "17000691",
    "latestAnswer": [
      {
        "title": "Test",
        "date": "2022-11-16",
        "position": "Foreman",
        "department": "Production",
        "username": "17000691"
      }
    ]
  },
  {
    "_id": "MT220047",
    "latestAnswer": [
      {
        "title": "Test",
        "date": "2022-11-15",
        "position": "Management Trainee",
        "department": "Logistic",
        "username": "MT220047"
      },
      {
        "title": "Test",
        "date": "2022-11-16",
        "position": "Management Trainee",
        "department": "Logistic",
        "username": "MT220047"
      },
      {
        "title": "Test",
        "date": "2022-11-16",
        "position": "Management Trainee",
        "department": "Logistic",
        "username": "MT220047"
      },
      {
        "title": "Untitled Form1",
        "date": "2022-11-16",
        "position": "Management Trainee",
        "department": "Logistic",
        "username": "MT220047"
      }
    ]
  }
]

目标输出结构 #

[
  {
    "username": "MT220047",
    "title": "Test",
    "position": "Management Trainee",
    "department": "Logistic",
    "2022-11-15": 1,
    "2022-11-16": 2
  },
  {
    "username": "MT220047",
    "title": "Untitled Form1",
    "position": "Management Trainee",
    "department": "Logistic",
    "2022-11-16": 1
  },
  {
    "username": "17000691",
    "title": "Test",
    "position": "Foreman",
    "department": "Production",
    "2022-11-16": 1
  }
]

聚合管道解决方案 #

通过以下4个聚合阶段即可实现需求：

1. $unwind：展开latestAnswer数组，将每个数组元素转为独立文档，便于后续分组统计
2. 第一层$group：按username、title、position、department、date分组，统计每个日期对应的记录数
3. 第二层$group：按username、title、position、department分组，把日期和对应计数整理成{k: 日期, v: 计数}的数组格式
4. $replaceRoot：用$arrayToObject将日期计数数组转为键值对，再和基础字段合并成最终文档

完整聚合管道代码：

db.collection.aggregate([
  // 展开latestAnswer数组
  { $unwind: "$latestAnswer" },
  // 按多维度分组统计日期记录数
  {
    $group: {
      _id: {
        username: "$latestAnswer.username",
        title: "$latestAnswer.title",
        position: "$latestAnswer.position",
        department: "$latestAnswer.department",
        date: "$latestAnswer.date"
      },
      count: { $sum: 1 }
    }
  },
  // 按用户、标题等维度合并日期计数
  {
    $group: {
      _id: {
        username: "$_id.username",
        title: "$_id.title",
        position: "$_id.position",
        department: "$_id.department"
      },
      dateCounts: { $push: { k: "$_id.date", v: "$count" } }
    }
  },
  // 合并基础字段与日期计数字段，生成最终文档
  {
    $replaceRoot: {
      newRoot: {
        $mergeObjects: [
          {
            username: "$_id.username",
            title: "$_id.title",
            position: "$_id.position",
            department: "$_id.department"
          },
          { $arrayToObject: "$dateCounts" }
        ]
      }
    }
  }
])

内容的提问来源于stack exchange，提问作者JS24