基于MongoDB物化路径获取指定文档及其所有子文档

需求说明 #

我采用MongoDB物化路径（materialized paths）模拟文件夹结构，需要实现：针对$in条件中的每个文档ID，获取该文档本身以及所有层级的子文档。例如输入ID fc56c12b-181a-49e3-85a2-4474552bea6f（对应名称为f3的文档），结果需包含f3本身及其所有层级的子文档。

集合数据 #

[
  {
    "_id": "f79590d8-20d2-42a4-9f3f-17c2aa30fe19",
    "name": "f1",
    "createdBy": "lubix",
    "application": "mihoho",
    "path": ""
  },
  {
    "_id": "97e94a4e-53e1-44f2-a766-6ca88898fa38",
    "name": "f11",
    "createdBy": "lubix",
    "application": "mihoho",
    "path": ",f79590d8-20d2-42a4-9f3f-17c2aa30fe19,"
  },
  {
    "_id": "b4eb3f93-a9cf-4fc8-bfa5-ee20a5be9769",
    "name": "f111",
    "createdBy": "lubix",
    "application": "mihoho",
    "path": ",f79590d8-20d2-42a4-9f3f-17c2aa30fe19,97e94a4e-53e1-44f2-a766-6ca88898fa38,"
  },
  {
    "_id": "82ae9741-c44a-4b06-8526-0debb4a54006",
    "name": "f12",
    "createdBy": "lubix",
    "application": "mihoho",
    "path": ",f79590d8-20d2-42a4-9f3f-17c2aa30fe19,"
  },
  {
    "_id": "51972fc1-cfff-4179-88a1-29adb1658f52",
    "name": "f2",
    "createdBy": "lubix",
    "application": "mihoho",
    "path": ""
  },
  {
    "_id": "d930ad8c-bde7-49bc-a31c-e63255f02ddc",
    "name": "f21",
    "createdBy": "lubix",
    "application": "mihoho",
    "path": ",51972fc1-cfff-4179-88a1-29adb1658f52,"
  },
  {
    "_id": "fc56c12b-181a-49e3-85a2-4474552bea6f",
    "name": "f3",
    "createdBy": "lubix",
    "application": "mihoho",
    "path": ""
  },
  {
    "_id": "3a6ad34a-8cab-4f7b-ad4c-0ed1367fd97e",
    "name": "f31",
    "createdBy": "lubix",
    "application": "mihoho",
    "path": ",fc56c12b-181a-49e3-85a2-4474552bea6f,"
  },
  {
    "_id": "e05fc977-7b98-40e9-ac72-a296f45872c8",
    "name": "f32",
    "createdBy": "lubix",
    "application": "mihoho",
    "path": ",fc56c12b-181a-49e3-85a2-4474552bea6f,"
  },
  {
    "_id": "107218eb-df68-46de-b176-d70d1202f373",
    "name": "f33",
    "createdBy": "lubix",
    "application": "mihoho",
    "path": ",fc56c12b-181a-49e3-85a2-4474552bea6f,"
  },
  {
    "_id": "88464a81-250f-4318-a8c8-349809e348df",
    "name": "f331",
    "createdBy": "lubix",
    "application": "mihoho",
    "path": ",fc56c12b-181a-49e3-85a2-4474552bea6f,107218eb-df68-46de-b176-d70d1202f373,"
  },
  {
    "_id": "799181dd-38bd-4142-a2f6-015a4eb8e160",
    "name": "f3311",
    "createdBy": "lubix",
    "application": "mihoho",
    "path": ",fc56c12b-181a-49e3-85a2-4474552bea6f,107218eb-df68-46de-b176-d70d1202f373,88464a81-250f-4318-a8c8-349809e348df,"
  },
  {
    "_id": "b84b918d-ac92-4ab0-8d78-8d954100afad",
    "name": "f332",
    "createdBy": "lubix",
    "application": "mihoho",
    "path": ",fc56c12b-181a-49e3-85a2-4474552bea6f,107218eb-df68-46de-b176-d70d1202f373,"
  }
]

尝试的查询语句 #

db.collection.aggregate([
  {
    $match: {
      "_id": {
        $in: [
          "fc56c12b-181a-49e3-85a2-4474552bea6f",
          "97e94a4e-53e1-44f2-a766-6ca88898fa38"
        ]
      }
    }
  },
  {
    "$addFields": {
      "panda": {
        "$cond": [
          {
            "$eq": [
              "$path",
              ""
            ]
          },
          {
            "$concat": [
              ",",
              "$_id",
              ","
            ]
          },
          {
            "$concat": [
              "$path",
              "$_id",
              ","
            ]
          }
        ]
      }
    }
  },
  {
    $addFields: {
      "rgx": {
        $concat: [
          "^",
          "$panda"
        ]
      }
    }
  },
  // {'$match': {'path':{$regex: '^,fc56c12b-181a-49e3-85a2-4474552bea6f,'}}}
  // {$addFields: {'dodo':{ $regexFind: {input: '$path', regex:'$rgx'}}}}
])

解决方案 #

以下聚合管道可实现批量获取指定节点及其所有层级子节点的需求：

db.collection.aggregate([
  // 1. 筛选$in中的目标节点，生成每个节点的子节点匹配前缀
  {
    $match: {
      "_id": {
        $in: [
          "fc56c12b-181a-49e3-85a2-4474552bea6f",
          "97e94a4e-53e1-44f2-a766-6ca88898fa38"
        ]
      }
    }
  },
  {
    $addFields: {
      "matchPrefix": {
        $cond: [
          { $eq: ["$path", ""] },
          { $concat: [",", "$_id", ","] },
          { $concat: ["$path", "$_id", ","] }
        ]
      }
    }
  },
  // 2. 收集所有匹配前缀和目标节点ID
  {
    $group: {
      _id: null,
      targetIds: { $push: "$_id" },
      prefixes: { $push: "$matchPrefix" }
    }
  },
  // 3. 关联查询目标节点及所有子节点
  {
    $lookup: {
      from: "collection", // 替换为你的集合名称
      let: { targetIds: "$targetIds", prefixes: "$prefixes" },
      pipeline: [
        {
          $match: {
            $expr: {
              $or: [
                // 匹配目标节点本身
                { $in: ["$_id", "$$targetIds"] },
                // 匹配所有层级子节点：path以任意前缀开头
                { $anyElementTrue: {
                    $map: {
                      input: "$$prefixes",
                      as: "prefix",
                      in: { $regexMatch: { input: "$path", regex: { $concat: ["^", "$$prefix"] } } }
                    }
                  }
                }
              ]
            }
          }
        }
      ],
      as: "result"
    }
  },
  // 4. 整理输出结果
  { $unwind: "$result" },
  { $replaceRoot: { newRoot: "$result" } }
])

逻辑说明 #

1. 筛选目标节点：先获取$in条件中的所有节点，为每个节点生成用于匹配子节点的路径前缀（根节点前缀为,_id,，非根节点为path+_id+,）。
2. 聚合匹配条件：将所有目标ID和前缀收集为数组，方便后续批量匹配。
3. 批量匹配节点：通过$lookup在同一集合中匹配所有目标节点，以及路径前缀匹配的所有层级子节点。
4. 清理结果格式：展开关联查询结果并替换根节点，得到最终的文档列表。

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