MongoDB聚合查询：如何将多对象数组合并为单个对象数组

需求 #

作为MongoDB新手，需通过聚合查询将多个对象数组合并为单个对象数组，以下是相关信息及期望结果：

数据模型 #

db={
  "teacher": [
    {
      "rating": 4,
      "teacher_id": "123",
      "course_count": 1
    },
    {
      "rating": 5,
      "teacher_id": "456",
      "course_count": 4
    },
    {
      "rating": 5,
      "teacher_id": "no-exists",
      "course_count": 4
    }
  ],
  "course": [
    {
      "teacher_id": "123",
      "cid": "1",
      "advanced": true
    },
    {
      "teacher_id": "456",
      "cid": "1",
      "advanced": true
    }
  ]
}

当前聚合查询 #

db.course.aggregate([
  {
    "$match": {
      "cid": "1"
    }
  },
  {
    "$lookup": {
      "localField": "teacher_id",
      "from": "teacher",
      "foreignField": "teacher_id",
      "as": "teacher"
    }
  },
  {
    $unwind: {
      path: "$teacher",
      preserveNullAndEmptyArrays: true
    }
  },
  {
    $project: {
      _id: 0,
      cid: 1,
      teacher: 1
    }
  }
])

当前输出 #

[
  {
    "cid": "1",
    "teacher": {
      "_id": ObjectId("5a934e000102030405000002"),
      "course_count": 1,
      "rating": 4,
      "teacher_id": "123"
    }
  },
  {
    "cid": "1",
    "teacher": {
      "_id": ObjectId("5a934e000102030405000003"),
      "course_count": 4,
      "rating": 5,
      "teacher_id": "456"
    }
  }
]

期望输出 #

{
  "cid": "1",
  "advanced": true,
  "teacher": [
    {
      "_id": ObjectId("5a934e000102030405000002"),
      "course_count": 1,
      "rating": 4,
      "teacher_id": "123"
    },
    {
      "_id": ObjectId("5a934e000102030405000003"),
      "course_count": 4,
      "rating": 5,
      "teacher_id": "456"
    }
  ]
}

解决方案 #

调整聚合查询，通过$group阶段将相同cid的文档合并，把每个文档关联到的老师信息收集到一个数组中：

db.course.aggregate([
  // 筛选cid为1的课程
  { "$match": { "cid": "1" } },
  // 关联teacher表，获取对应老师信息（返回数组）
  {
    "$lookup": {
      "localField": "teacher_id",
      "from": "teacher",
      "foreignField": "teacher_id",
      "as": "teacher_info"
    }
  },
  // 按cid和advanced分组，合并老师数组
  {
    "$group": {
      "_id": { "cid": "$cid", "advanced": "$advanced" },
      "teacher": { "$push": { "$arrayElemAt": ["$teacher_info", 0] } }
    }
  },
  // 整理输出字段，去掉_id，提取cid和advanced
  {
    "$project": {
      "_id": 0,
      "cid": "$_id.cid",
      "advanced": "$_id.advanced",
      "teacher": 1
    }
  }
])

步骤说明 #

1. $match：筛选出cid为"1"的课程文档，减少后续处理的数据量。
2. $lookup：根据teacher_id关联teacher集合，将匹配到的老师信息存入teacher_info数组（每个课程文档对应一个老师，所以数组长度为1）。
3. $group：以cid和advanced为分组键，用$push将每个分组内的老师信息（通过$arrayElemAt提取数组中的唯一元素）合并成一个数组。
4. $project：重新组织输出结构，去掉自动生成的_id，提取分组键中的cid和advanced，保留合并后的teacher数组。

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