求助：MongoDB中关联两个嵌套集合的查询实现

Hey Javier, let's fix that query you're struggling with! From your example context, I can see you're working with a companies collection that has an array of user references (each entry includes userId and roleId), and you need to join data from both users and roles collections to get full, populated details for each company.

你的集合结构（基于场景示例） #

• companies集合：

{
  "_id": ObjectId("615a1e2f9c3e7b3b5f4c2a10"),
  "name": "Tech Corp",
  "users": [
    { "userId": ObjectId("615a1e2f9c3e7b3b5f4c2a11"), "roleId": ObjectId("615a1e2f9c3e7b3b5f4c2a13") },
    { "userId": ObjectId("615a1e2f9c3e7b3b5f4c2a12"), "roleId": ObjectId("615a1e2f9c3e7b3b5f4c2a14") }
  ]
}

• users集合：

{ "_id": ObjectId("615a1e2f9c3e7b3b5f4c2a11"), "name": "Alice" },
{ "_id": ObjectId("615a1e2f9c3e7b3b5f4c2a12"), "name": "Bob" }

• roles集合：

{ "_id": ObjectId("615a1e2f9c3e7b3b5f4c2a13"), "title": "Admin" },
{ "_id": ObjectId("615a1e2f9c3e7b3b5f4c2a14"), "title": "Editor" }

你的现有查询问题 #

Your current attempt is likely struggling with joining both nested references inside the users array—we need to first break down the array, join each collection individually, then reassemble the data back into the original structure.

正确的聚合查询方案 #

Here's the query that will fetch all company documents, and populate both user details and role details for each entry in the users array:

db.companies.aggregate([
  // 先展开companies里的users数组，方便逐个处理关联
  { $unwind: "$users" },
  // 关联users集合，获取用户详情
  {
    $lookup: {
      from: "users",
      localField: "users.userId",
      foreignField: "_id",
      as: "users.userDetails"
    }
  },
  // 关联roles集合，获取角色详情
  {
    $lookup: {
      from: "roles",
      localField: "users.roleId",
      foreignField: "_id",
      as: "users.roleDetails"
    }
  },
  // 将userDetails和roleDetails数组转为单个对象（每个ID对应唯一文档）
  { $unwind: "$users.userDetails" },
  { $unwind: "$users.roleDetails" },
  // 重新按company_id分组，把展开的users项合并回数组
  {
    $group: {
      _id: "$_id",
      name: { $first: "$name" },
      users: { $push: "$users" }
    }
  }
])

结果说明 #

This query will return each company with its users array fully populated:

{
  "_id": ObjectId("615a1e2f9c3e7b3b5f4c2a10"),
  "name": "Tech Corp",
  "users": [
    {
      "userId": ObjectId("615a1e2f9c3e7b3b5f4c2a11"),
      "roleId": ObjectId("615a1e2f9c3e7b3b5f4c2a13"),
      "userDetails": { "_id": ObjectId("615a1e2f9c3e7b3b5f4c2a11"), "name": "Alice" },
      "roleDetails": { "_id": ObjectId("615a1e2f9c3e7b3b5f4c2a13"), "title": "Admin" }
    },
    {
      "userId": ObjectId("615a1e2f9c3e7b3b5f4c2a12"),
      "roleId": ObjectId("615a1e2f9c3e7b3b5f4c2a14"),
      "userDetails": { "_id": ObjectId("615a1e2f9c3e7b3b5f4c2a12"), "name": "Bob" },
      "roleDetails": { "_id": ObjectId("615a1e2f9c3e7b3b5f4c2a14"), "title": "Editor" }
    }
  ]
}

关键要点 #

• $unwind breaks down the users array into individual documents, making it easy to join each reference separately
• Two distinct $lookup stages handle joining the users and roles collections respectively
• $group reassembles the documents back into the original company structure with fully populated user data

内容的提问来源于stack exchange，提问作者Javier del Hoyo