MongoDB 递归查询

MongoDB可支持递归查询，需要用到$graphLookup，参见官网示例地址：https://www.mongodb.com/docs/v4.2/reference/operator/aggregation/graphLookup/。

现有表的示例数据为：

返回结果数据

{
"id" : "rockBurst",
"name" : "冲击地压",
"url" : "",
"pid" : "#",
"parentName" : "",
"hierarchy" : [
{
"id" : "groundSoundMonitoring",
"name" : "地音监测",
"url" : "",
"pid" : "rockBurst",
"parentName" : "冲击地压"
},
{
"id" : "groundSoundRealTime",
"name" : "实时监测",
"url" : "/rockBurst/groundSoundMonitoring/realTimeMonitor",
"pid" : "groundSoundMonitoring",
"parentName" : "地音监测"
},
{
"id" : "microseismicMonitoring",
"name" : "微震监测",
"pid" : "rockBurst",
"parentName" : "冲击地压"
},
{
"id" : "realTimeMonitor",
"name" : "实时监测",
"url" : "/rockBurst/index",
"pid" : "microseismicMonitoring",
"parentName" : "微震监测"
}
]
}

Mongo语句说明

{
   $graphLookup: {
      from: <collection>,
      startWith: <expression>,
      connectFromField: <string>,
      connectToField: <string>,
      as: <string>,
      maxDepth: <number>,
      depthField: <string>,
      restrictSearchWithMatch: <document>
   }
}

graphLookup示例：

db.test.test_zl.aggregate([
    {
        $graphLookup: {
            from: "test.test_zl",
            startWith: "$id",
            connectFromField: "id",
            connectToField: "pid",
            as: "hierarchy"
        }
    }])

如果想通过条件查询，并递归查询所有数据，并以记录的形式返回（需求：我想从表中找到根结点是rockBurst的所有二级及三级数据，并复制到别一个表中），这个时候可以结合 $match、$concatArrays、$unwind、$replaceRoot等pipeLine操作。

以上相应函数说明

(1){ $replaceRoot: { newRoot: <replacementDocument> } }：将子对象转成根对象
eg:db.students.insertMany([
   {
      "_id" : 1,
      "grades" : [
         { "test": 1, "grade" : 80, "mean" : 75, "std" : 6 },
         { "test": 2, "grade" : 85, "mean" : 90, "std" : 4 },
         { "test": 3, "grade" : 95, "mean" : 85, "std" : 6 }
      ]
   },
   {
      "_id" : 2,
      "grades" : [
         { "test": 1, "grade" : 90, "mean" : 75, "std" : 6 },
         { "test": 2, "grade" : 87, "mean" : 90, "std" : 3 },
         { "test": 3, "grade" : 91, "mean" : 85, "std" : 4 }
      ]
   }
    ])
   db.students.aggregate( [
   { $unwind: "$grades" },
   { $match: { "grades.grade" : { $gte: 90 } } },
   { $replaceRoot: { newRoot: "$grades" } }
    ] )
   result:{ "test" : 3, "grade" : 95, "mean" : 85, "std" : 6 }
          { "test" : 1, "grade" : 90, "mean" : 75, "std" : 6 }
          { "test" : 3, "grade" : 91, "mean" : 85, "std" : 4 }
(2){ $unwind: <field path> }：将数组中的N个对象转成N条对象
eg:db.inventory.insertOne({ "_id" : 1, "item" : "ABC1", sizes: [ "S", "M", "L"] })
   db.inventory.aggregate( [ { $unwind : "$sizes" } ] )
   result:{ "_id" : 1, "item" : "ABC1", "sizes" : "S" }
          { "_id" : 1, "item" : "ABC1", "sizes" : "M" }
          { "_id" : 1, "item" : "ABC1", "sizes" : "L" }

(3){ $concatArrays: [ <array1>, <array2>, ... ] }:多个数组合并在同一数组里
eg:{ $concatArrays: [[ "hello", " "], [ ["world"],"again" ]] }
   result:[ "hello", " ", [ "world" ], "again" ]

本例应用

db.test.test_zl.aggregate([
    {
        $graphLookup: {
            from: "test.test_zl",
            startWith: "$id",
            connectFromField: "id",
            connectToField: "pid",
            as: "hierarchy"
        }
    },
    {
        $match: { id: 'rockBurst' }
    },
    {
        $project: {
            result: {
                $concatArrays: ["$hierarchy", [{ _id: "$_id", id: "$id", name: "$name", url: "", pid: "#", moduleName: "$moduleName", icon: "$icon", order: "$order", parentName: "", checked: "$checked" }]]
            }
        }
    },
    {
        $unwind: "$result"
    },
    {
        $replaceRoot: {
            newRoot: "$result"
        }
    },
    {
        $sort: {
            _id: 1
        }
    }
])

即可返回结果如上表示例数据所示