MongoDB复杂对象数组$lookup查询实现求助

针对你的需求，我们可以通过MongoDB聚合管道逐步处理嵌套数组的关联，以下是完整的聚合查询：

db.Groups.aggregate([
  // 展开外层groups数组
  { $unwind: "$groups" },
  // 展开每个group下的departments数组
  { $unwind: "$groups.departments" },
  // 关联Departments集合，匹配department_id和_id
  {
    $lookup: {
      from: "Departments",
      localField: "groups.departments.department_id",
      foreignField: "_id",
      as: "tempDepartment"
    }
  },
  // 将关联到的部门信息合并到原department对象中
  {
    $replaceRoot: {
      newRoot: {
        $mergeObjects: [
          "$groups.departments",
          { $arrayElemAt: ["$tempDepartment", 0] }
        ]
      }
    }
  },
  // 按group_id重新分组，恢复departments数组
  {
    $group: {
      _id: "$groups.group_id",
      departments: { $push: "$$ROOT" },
      originalId: { $first: "$_id" }
    }
  },
  // 按原文档_id分组，恢复groups数组
  {
    $group: {
      _id: "$originalId",
      groups: {
        $push: {
          group_id: "$_id",
          departments: "$departments"
        }
      }
    }
  },
  // 调整字段，确保和预期结构一致
  {
    $project: {
      _id: 1,
      groups: 1
    }
  }
])

各阶段说明： #

• $unwind：两次展开嵌套数组，把多层嵌套结构拆平，让每个department能单独和Departments集合做关联。
• $lookup：根据department_id匹配Departments集合的_id，把匹配结果存入临时字段tempDepartment。
• $replaceRoot + $mergeObjects：将原department信息和关联到的部门名称合并成完整对象。
• 两次$group：先按group_id把同组department重新组成数组，再按原文档_id把groups恢复成数组，还原初始嵌套结构。
• $project：筛选保留需要的字段，确保输出结构和预期一致。

执行上述查询后，就能得到你想要的结果：

{
  "_id": "any_id",
  "groups": [
    {
      "group_id": "group_id_1",
      "departments": [
        {
          "department_id": "id_1",
          "name": "name 1"
        },
        {
          "department_id": "id_2",
          "name": "name 2"
        }
      ]
    }
  ]
}

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