MongoDB双层嵌套数组字段的lookup联表查询实现方法

问题背景 #

现有两个集合：

• department：存储院系、下属讲师及讲师授课分组ID信息，示例结构：

{
  "_id":99,
  "name":"Erick Kalewe",
  "faculty":"Zazio",
  "lecturers":[
    {
      "lecturerID":31,
      "name":"Granny Kinton",
      "email":"gkintonu@answers.com",
      "imparts":[
        {
          "groupID":70,
          "codCourse":99
        }
      ]
    },
    {
      "lecturerID":36,
      "name":"Michale Dahmel",
      "email":"mdahmelz@artisteer.com",
      "imparts":[
        {
          "groupID":100,
          "codCourse":60
        }
      ]
    }
  ]
}

• group：存储授课分组、选课学生、上课安排信息，示例结构：

{
  "_id":100,
  "codCourse":11,
  "language":"Romanian",
  "max_students":196,
  "students":[
    {
      "studentID":1
    }
  ],
  "classes":[
    {
      "date":datetime.datetime(2022, 5, 10, 4, 24, 19),
      "cod_classroom":100
    }
  ]
}

需要将lecturers.imparts中的groupID与group集合的_id关联，替换为完整的分组文档，最终统计各院系下属教授授课的学生总数。

实现代码 #

由于关联字段位于两层嵌套数组中，无法直接使用基础$lookup完成嵌套替换，需要先打平数组、关联后再重组结构，完整聚合语句如下（兼容MongoDB 3.6+版本）：

db.department.aggregate([
  // 打平第一层嵌套：把每个讲师从lecturers数组拆成单独文档
  { $unwind: "$lecturers" },
  // 打平第二层嵌套：把每个授课分组从讲师的imparts数组拆成单独条目
  { $unwind: "$lecturers.imparts" },
  // 关联group集合，匹配分组ID
  {
    $lookup: {
      from: "group",
      localField: "lecturers.imparts.groupID",
      foreignField: "_id",
      as: "matchedGroup"
    }
  },
  // 用匹配到的完整分组文档替换原有imparts条目，未匹配到则保留原内容
  {
    $set: {
      "lecturers.imparts": {
        $cond: [
          { $gt: [{ $size: "$matchedGroup" }, 0] },
          { $first: "$matchedGroup" },
          "$lecturers.imparts"
        ]
      }
    }
  },
  // 删除临时关联字段
  { $unset: "matchedGroup" },
  // 第一次重组：按院系+讲师维度归组，还原每个讲师的imparts数组
  {
    $group: {
      _id: {
        deptId: "$_id",
        lecturerId: "$lecturers.lecturerID"
      },
      deptName: { $first: "$name" },
      faculty: { $first: "$faculty" },
      lecturerName: { $first: "$lecturers.name" },
      lecturerEmail: { $first: "$lecturers.email" },
      imparts: { $push: "$lecturers.imparts" }
    }
  },
  // 第二次重组：按院系维度归组，还原完整的department结构
  {
    $group: {
      _id: "$_id.deptId",
      name: { $first: "$deptName" },
      faculty: { $first: "$faculty" },
      lecturers: {
        $push: {
          lecturerID: "$_id.lecturerId",
          name: "$lecturerName",
          email: "$lecturerEmail",
          imparts: "$imparts"
        }
      }
    }
  },
  // 新增统计字段：计算该院系所有讲师授课的学生总数
  {
    $set: {
      totalStudentCount: {
        $sum: {
          $map: {
            input: "$lecturers",
            as: "lecturer",
            in: {
              $sum: {
                $map: {
                  input: "$$lecturer.imparts",
                  as: "courseGroup",
                  in: { $size: { $ifNull: ["$$courseGroup.students", []] } }
                }
              }
            }
          }
        }
      }
    }
  }
])

性能优化建议 #

如果业务目标仅为生成统计报表，不需要还原完整的嵌套文档结构，可以跳过结构重组步骤，直接在关联后聚合计算，查询效率会高很多，简化版统计查询如下：

db.department.aggregate([
  { $unwind: "$lecturers" },
  { $unwind: "$lecturers.imparts" },
  {
    $lookup: {
      from: "group",
      localField: "lecturers.imparts.groupID",
      foreignField: "_id",
      as: "matchedGroup"
    }
  },
  // 拆分关联结果，未匹配到分组的条目保留为空，避免统计丢失
  { $unwind: { path: "$matchedGroup", preserveNullAndEmptyArrays: true } },
  // 按讲师维度统计授课学生数
  {
    $group: {
      _id: {
        faculty: "$faculty",
        deptId: "$_id",
        deptName: "$name",
        lecturerId: "$lecturers.lecturerID",
        lecturerName: "$lecturers.name"
      },
      lecturerStudentCount: { $sum: { $size: { $ifNull: ["$matchedGroup.students", []] } } }
    }
  },
  // 按院系维度归组，输出统计报表所需结构
  {
    $group: {
      _id: {
        faculty: "$_id.faculty",
        deptId: "$_id.deptId",
        deptName: "$_id.deptName"
      },
      deptTotalStudents: { $sum: "$lecturerStudentCount" },
      lecturerList: {
        $push: {
          lecturerID: "$_id.lecturerId",
          name: "$_id.lecturerName",
          teachStudentCount: "$lecturerStudentCount"
        }
      }
    }
  }
])

注意：group是MongoDB的保留关键字，生产环境建议将集合名修改为非关键字名称（比如course_group），避免后续查询出现语法兼容问题。

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