MongoDB树结构查询及同层级节点按sort字段升序排序

我需要在MongoDB中查询完整的树结构，使用了一段基于$graphLookup的聚合管道，但原代码未实现同层级节点按sort字段升序的需求。sort字段在Java中是BigDecimal类型，存储到MongoDB中为字符串类型。

原聚合管道代码如下：

[
  {
    $match: { pid: 0 }
  },
  {
    $graphLookup: {
      from: "administrative_divisions",
      startWith: "$_id",
      connectFromField: "_id",
      connectToField: "pid",
      depthField: "level",
      as: "children"
    }
  },
  {
    $unwind: {
      path: "$children",
      preserveNullAndEmptyArrays: true
    }
  }, 
  {
    $sort: {
      "children.level": -1
    }
  },
  {
    $group: {
      _id: "$_id",
      name: {
        $first: "$name"
      },
      path: {
        $first: "$path"
      },
      pid: {
        $first: "$pid"
      },
      children: {
        $push: "$children"
      }
    }
  },  
  {
    $addFields: {
      children: {
        $reduce: {
          input: "$children",
          initialValue: {
            level: -1,
            presentChild: [],
            prevChild: []
          },
          in: {
            $let: {
              vars: {
                prev: {
                  $cond: [
                    {
                      $eq: [
                        "$$value.level",
                        "$$this.level"
                      ]
                    },
                    "$$value.prevChild",
                    "$$value.presentChild"
                  ]
                },
                current: {
                  $cond: [
                    {
                      $eq: [
                        "$$value.level",
                        "$$this.level"
                      ]
                    },
                    "$$value.presentChild",
                    []
                  ]
                }
              },
              in: {
                level: "$$this.level",
                prevChild: "$$prev",
                presentChild: {
                  $concatArrays: [
                    "$$current",
                    [
                      {
                        $mergeObjects: [
                          "$$this",
                          {
                            children: {
                              $filter: {
                                input: "$$prev",
                                as: "e",
                                cond: {
                                  $eq: [
                                    "$$e.pid",
                                    "$$this._id"
                                  ]
                                }
                              }
                            }
                          }
                        ]
                      }
                    ]
                  ]
                }
              }
            }
          }
        }
      }
    }
  },
  {
    $addFields: {
      children: "$children.presentChild"
    }
  }  
]

示例数据：

[
  {
    "_id": 1,
    "name": "New York State",
    "pid": 0,
    "level": 1,
    "sort": "2.00223000",
    "leaf": false,
    "path": "1"
  },
  {
    "_id": 2,
    "name": "New York City",
    "pid": 1,
    "level": 2,
    "sort": "2.0000000",
    "leaf": false,
    "path": "1, 2"
  },
  {
    "_id": 4,
    "name": "The Bronx",
    "pid": 2,
    "level": 3,
    "sort": "1.0000000",
    "leaf": true,
    "path": "1, 2, 4"
  },
  {
    "_id": 5,
    "name": "Brooklyn",
    "pid": 2,
    "level": 3,
    "sort": "4.0000000",
    "leaf": true,
    "path": "1, 2, 5"
  },
  {
    "_id": 6,
    "name": "Manhattan",
    "pid": 2,
    "level": 3,
    "sort": "5.0000000",
    "leaf": true,
    "path": "1, 2, 6"
  },
  {
    "_id": 7,
    "name": "Queens",
    "pid": 2,
    "level": 3,
    "sort": "6.0000000",
    "leaf": true,
    "path": "1, 2, 7"
  },
  {
    "_id": 8,
    "name": "Staten Island",
    "pid": 2,
    "level": 3,
    "sort": "12.0002220",
    "leaf": true,
    "path": "1, 2, 8"
  },
  {
    "_id": 3,
    "name": "Albany",
    "pid": 1,
    "level": 2,
    "sort": "1.0023400",
    "leaf": true,
    "path": "1, 3"
  }
]

需求是同层级节点按sort字段升序排列，预期结果中父节点的children数组需按sort从小到大排序，子节点同理。

修改后的聚合管道 #

[
  {
    $match: { pid: 0 }
  },
  {
    $graphLookup: {
      from: "administrative_divisions",
      startWith: "$_id",
      connectFromField: "_id",
      connectToField: "pid",
      depthField: "level",
      as: "children"
    }
  },
  {
    $unwind: {
      path: "$children",
      preserveNullAndEmptyArrays: true
    }
  }, 
  {
    $sort: {
      "children.level": -1,
      $expr: { $asc: { $toDecimal: "$children.sort" } }
    }
  },
  {
    $group: {
      _id: "$_id",
      name: { $first: "$name" },
      path: { $first: "$path" },
      pid: { $first: "$pid" },
      children: { $push: "$children" }
    }
  },  
  {
    $addFields: {
      children: {
        $reduce: {
          input: "$children",
          initialValue: {
            level: -1,
            presentChild: [],
            prevChild: []
          },
          in: {
            $let: {
              vars: {
                prev: {
                  $cond: [
                    { $eq: ["$$value.level", "$$this.level"] },
                    "$$value.prevChild",
                    "$$value.presentChild"
                  ]
                },
                current: {
                  $cond: [
                    { $eq: ["$$value.level", "$$this.level"] },
                    "$$value.presentChild",
                    []
                  ]
                }
              },
              in: {
                level: "$$this.level",
                prevChild: "$$prev",
                presentChild: {
                  $concatArrays: [
                    "$$current",
                    [
                      {
                        $mergeObjects: [
                          "$$this",
                          {
                            children: {
                              $filter: {
                                input: "$$prev",
                                as: "e",
                                cond: { $eq: ["$$e.pid", "$$this._id"] }
                              }
                            }
                          }
                        ]
                      }
                    ]
                  ]
                }
              }
            }
          }
        }
      }
    }
  },
  {
    $addFields: {
      children: "$children.presentChild"
    }
  }
]

关键改动说明 #

1. 调整$sort阶段逻辑：
   原管道仅按children.level降序排序，现在修改为先按level降序，再按转成Decimal类型的sort升序。这样既保留了原管道从最深层节点开始构建树的逻辑，又保证了同层级节点按sort字段的数值大小升序排列。

   • 使用$toDecimal将字符串类型的sort转换为数值类型，避免字符串排序的逻辑错误（比如"12"字符串会排在"2"之前，但数值12大于2）。
   • $expr允许在$sort中使用表达式进行排序，确保按数值而非字符串比较。

2. 为什么之前的尝试无效：

   • 直接替换为{ $sort: { "children.sort": 1 } }会破坏原管道从深层到浅层的构建顺序，导致树结构拼接错误。
   • 在管道末尾添加排序仅能对根节点的children数组排序，无法处理嵌套层级的children排序，因为嵌套结构是在$reduce阶段动态构建的，末尾排序无法影响内部数组。

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