如何在Node.js中用MongoDB聚合管道关联校院系书籍数据

我在MongoDB中有Schools、departments、books三个集合，需要在Node.js中使用aggregate函数查询学校文档，同时获取每所学校对应的院系详情，以及对应院系的书籍信息。

集合结构详情 #

1. Schools集合 #

{
  _id: "643fa7c76fd3bb2e2990b6c2",
  schools: [
    {
      id: "d8908bf22",
      name: "ab",
      url: "http:ab.com"
    },
    {
      id: "b6bcf",
      name: "cd",
      url: "http:cd.com"
    }
  ]
}

2. departments集合 #

[
  {
    _id: "643fb22e7c5ea747261d8400",
    name: "ab",
    value: {
      school: {
        name: "ab",
        id: "a6952a86-97ae-4de7-ad46-f7703681cb4a"
      },
      departments: [
        {
          id: "1faa82d4-a618-4985-9840-03020b05a429",
          name: "ab1/ab1:dummy.json"
        },
        {
          id: "2faaq2d4-a618-4985-9840-03020b05a429",
          name: "ab2/ab2:dummy.json"
        }
      ]
    }
  },
  {
    _id: "643fb22e7c5ea747261d8401",
    name: "cd",
    value: {
      school: {
        name: "cd",
        id: "c6952a86-97ae-4de7-ad46-f7703681cb4a"
      },
      departments: [
        {
          id: "cfaa82d4-a618-4985-9840-03020b05a429",
          name: "cd1/cd1:dummy.json"
        },
        {
          id: "cfaaq2d4-a618-4985-9840-03020b05a429",
          name: "cd2/cd2:dummy.json"
        }
      ]
    }
  }
]

3. books集合 #

[
  {
    departmentId: "1faa82d4-a618-4985-9840-03020b05a429",
    value: {
      booksInfo: [
        {
          "book": {
            "name": "abcd",
            "bookInfo": {
              "available": "true"
            }
          }
        },
        {
          "book": {
            "name": "abcd2",
            "bookInfo": {
              "available": "false"
            }
          }
        }
      ]
    }
  },
  {
    departmentId: "2faaq2d4-a618-4985-9840-03020b05a429",
    value: {
      booksInfo: [
        {
          "book": {
            "name": "xyz",
            "bookInfo": {
              "available": "true"
            }
          }
        },
        {
          "book": {
            "name": "abcd",
            "bookInfo": {
              "available": "false"
            }
          }
        }
      ]
    }
  },
  {
    departmentId: "2faaq2d4-a618-4985-9840-03020b05a429",
    value: {
      booksInfo: [
        {
          "book": {
            "name": "xyz1",
            "bookInfo": {
              "available": "true"
            }
          }
        },
        {
          "book": {
            "name": "abcd1",
            "bookInfo": {
              "available": "false"
            }
          }
        }
      ]
    }
  },
  {
    departmentId: "cfaaq2d4-a618-4985-9840-03020b05a429",
    value: {
      booksInfo: [
        {
          "book": {
            "name": "xyz1",
            "bookInfo": {
              "available": "true"
            }
          }
        },
        {
          "book": {
            "name": "abcd1",
            "bookInfo": {
              "available": "false"
            }
          }
        }
      ]
    }
  }
]

当前尝试及问题 #

我尝试了以下代码，能正确获取每所学校的院系详情，但无法获取对应院系的书籍信息，需要实现每个学校的每个院系都关联对应书籍信息：

const pipeline = [
  {
    $unwind: "$value.schools"
  },
  {
    $lookup: {
      from: "departments",
      localField: "value.schools.name",
      foreignField: "name",
      as: "departmentDetails"
    }
  },
  {
    $unwind: "$departmentDetails.value.departments"// 这行无法正常工作
  }  
]

期望输出 #

[
  {
    school: {
      name:"ab",
      url:"http:ab.com",
      departments:[
        {
          "departmentId": "1faa82d4-a618-4985-9840-03020b05a429",
          "books":[
            {
              bookName:"abcd",
              available:"true"
            },
            {
              bookName:"abcd2",
              available:"false"
            }
          ]
        },
        {
          "departmentId": "2faaq2d4-a618-4985-9840-03020b05a429",
          "books":[
            {
              bookName:"xyz",
              available:"true"
            },
            {
              bookName:"abcd",
              available:"false"
            },
            {
              bookName:"xyz1",
              available:"true"
            },
            {
              bookName:"abcd1",
              available:"false"
            }
          ]
        }
      ]
    }
  },
  {
    // 另一所学校的类似结构
    school: {
      name:"cd",
      url:"http:cd.com",
      departments:[
        // 对应院系及书籍
      ]
    }
  }
]

解决方案：完整聚合管道 #

要实现学校-院系-书籍的多层关联，需要嵌套使用$lookup和$unwind，同时用$project整理输出结构：

const pipeline = [
  // 展开Schools集合中的schools数组，将每个学校转为单独文档
  { $unwind: "$schools" },
  // 关联departments集合，根据学校名称匹配
  {
    $lookup: {
      from: "departments",
      localField: "schools.name",
      foreignField: "name",
      as: "departmentData"
    }
  },
  // 展开关联后的departmentData数组
  { $unwind: "$departmentData" },
  // 展开院系数组，将每个院系转为单独文档
  { $unwind: "$departmentData.value.departments" },
  // 关联books集合，根据院系ID匹配
  {
    $lookup: {
      from: "books",
      localField: "departmentData.value.departments.id",
      foreignField: "departmentId",
      as: "booksData"
    }
  },
  // 合并同一院系的多本书籍数据，展开booksInfo数组
  {
    $addFields: {
      "allBooks": {
        $reduce: {
          input: "$booksData",
          initialValue: [],
          in: { $concatArrays: ["$$value", "$$this.value.booksInfo"] }
        }
      }
    }
  },
  // 整理书籍格式，提取需要的字段
  {
    $addFields: {
      "formattedBooks": {
        $map: {
          input: "$allBooks",
          as: "bookItem",
          in: {
            bookName: "$$bookItem.book.name",
            available: "$$bookItem.book.bookInfo.available"
          }
        }
      }
    }
  },
  // 按学校名称分组，聚合院系及书籍信息
  {
    $group: {
      _id: "$schools.name",
      schoolUrl: { $first: "$schools.url" },
      departments: {
        $push: {
          departmentId: "$departmentData.value.departments.id",
          books: "$formattedBooks"
        }
      }
    }
  },
  // 整理最终输出结构
  {
    $project: {
      _id: 0,
      school: {
        name: "$_id",
        url: "$schoolUrl",
        departments: "$departments"
      }
    }
  }
];

// 在Node.js中执行聚合查询
const result = await db.collection("Schools").aggregate(pipeline).toArray();

代码说明 #

1. $unwind "$schools"：将Schools集合中的数组展开，每个学校成为独立文档。
2. 第一层$lookup：根据学校名称关联departments集合，获取对应学校的院系数据。
3. 两次$unwind：分别展开关联后的departmentData数组和院系数组，让每个院系成为独立文档。
4. 第二层$lookup：根据院系ID关联books集合，获取对应院系的所有书籍数据。
5. $reduce + $concatArrays：合并同一院系分散在多个books文档中的booksInfo数组。
6. $map：将书籍数据格式化为期望的结构，提取bookName和available字段。
7. $group：按学校名称分组，聚合该学校下的所有院系及对应书籍。
8. $project：整理输出为符合期望的结构。

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