如何用MongoDB聚合管道关联两集合获取每本书所属分组

集合结构 #

Books集合 #

[
  {
    "BOOK_ID": "100",
    "BOOK_NAME": "Book 1",
    "BOOK_DESC": "abcd"
  },
  {
    "BOOK_ID": "101",
    "BOOK_NAME": "Book 2",
    "BOOK_DESC": "efgh"
  },
  {
    "BOOK_ID": "102",
    "BOOK_NAME": "Book 3",
    "BOOK_DESC": "ijkl"
  }
]

BookGroup集合 #

[
  {
    "GROUP_ID": "100",
    "GROUP_NAME": "G1",
    "GROUPS": [
      {
        "BOOK_ID": "100",
        "BOOK_NAME": "Book 1"
      },
      {
        "BOOK_ID": "101",
        "BOOK_NAME": "Book 2"
      }
    ]
  },
  {
    "GROUP_ID": "101",
    "GROUP_NAME": "G2",
    "GROUPS": [
      {
        "BOOK_ID": "101",
        "BOOK_NAME": "Book 2"
      }
    ]
  },
  {
    "GROUP_ID": "102",
    "GROUP_NAME": "G3",
    "GROUPS": [
      {
        "BOOK_ID": "100",
        "BOOK_NAME": "Book 1"
      },
      {
        "BOOK_ID": "102",
        "BOOK_NAME": "Book 3"
      }
    ]
  }
]

已实现的单本书查询 #

针对单本书（如Book 2）获取所属分组的聚合查询：

db.BookGroup.aggregate([
  { "$unwind": "$GROUPS" },
  { "$match": { "GROUPS.BOOK_NAME": "Book 2" } },
  {
    "$group": {
      "_id": null,
      "group_name": { "$push": "$GROUP_NAME" }
    }
  },
  { "$project": { "_id": false, "group_name": true } }
])

返回结果：

[
  {
    "group_name": ["G1", "G2"]
  }
]

解决方案：获取每本书的所属分组 #

要实现返回所有书籍及其对应分组的需求，可以使用以下聚合管道：

db.Books.aggregate([
  {
    "$lookup": {
      "from": "BookGroup",
      "let": { "bookId": "$BOOK_ID" },
      "pipeline": [
        { "$unwind": "$GROUPS" },
        { "$match": { "$expr": { "$eq": ["$GROUPS.BOOK_ID", "$$bookId"] } } },
        { "$group": { "_id": null, "groups": { "$push": "$GROUP_NAME" } } },
        { "$project": { "_id": 0, "group_name": "$groups" } }
      ],
      "as": "group_info"
    }
  },
  { "$unwind": { "path": "$group_info", "preserveNullAndEmptyArrays": true } },
  {
    "$project": {
      "BOOK_ID": 1,
      "BOOK_NAME": 1,
      "BOOK_DESC": 1,
      "group_name": { "$ifNull": ["$group_info.group_name", []] }
    }
  }
])

执行逻辑说明 #

1. $lookup阶段：从Books集合出发关联BookGroup集合，通过let定义当前书籍的BOOK_ID，子管道内：
   • 用$unwind拆分GROUPS数组，将每个书籍项单独成文档；
   • 用$match筛选出与当前书籍BOOK_ID匹配的分组项；
   • 用$group将匹配到的分组名称收集为数组；
   • 用$project整理输出格式，只保留group_name字段。
2. $unwind阶段：展开关联得到的group_info数组，preserveNullAndEmptyArrays确保无分组的书籍也能保留。
3. $project阶段：整理最终输出结构，保留书籍原有字段，将group_name设置为关联得到的数组（无分组则返回空数组）。

期望输出结果 #

[
  {
    "BOOK_ID": "100",
    "BOOK_NAME": "Book 1",
    "BOOK_DESC": "abcd",
    "group_name": ["G1", "G3"]
  },
  {
    "BOOK_ID": "101",
    "BOOK_NAME": "Book 2",
    "BOOK_DESC": "efgh",
    "group_name": ["G1", "G2"]
  },
  {
    "BOOK_ID": "102",
    "BOOK_NAME": "Book 3",
    "BOOK_DESC": "ijkl",
    "group_name": ["G3"]
  }
]

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