MongoDB聚合嵌套lookup实现三集合关联嵌套查询

直接对嵌套数组使用$lookup时，如果没有提前记录数组元素的原始位置索引，关联后重组数组无法保留原有顺序，就会出现groupRoles和members错位、顺序不符合预期的问题。

核心逻辑是在拆解嵌套数组前为每一层元素标记索引，关联完成后严格按照索引重组数组，100%保留原始顺序。以下是兼容MongoDB 5.0+的可直接执行聚合代码：

db.GroupRoles.aggregate([
  // 为groupRoles数组每个元素标记原始索引
  {
    $set: {
      groupRoles: {
        $map: {
          input: { $range: [0, { $size: "$groupRoles" }] },
          as: "grIdx",
          in: {
            $mergeObjects: [
              { $arrayElemAt: ["$groupRoles", "$$grIdx"] },
              { _grIndex: "$$grIdx" }
            ]
          }
        }
      }
    }
  },
  // 批量关联Roles集合，拉取所有角色信息
  {
    $lookup: {
      from: "Roles",
      localField: "groupRoles._id",
      foreignField: "_id",
      as: "_roleRes"
    }
  },
  // 合并角色信息到对应groupRoles元素，同时为members数组每个元素标记原始索引
  {
    $set: {
      groupRoles: {
        $map: {
          input: "$groupRoles",
          as: "grItem",
          in: {
            $let: {
              vars: {
                roleInfo: {
                  $first: {
                    $filter: {
                      input: "$_roleRes",
                      cond: { $eq: ["$$this._id", "$$grItem._id"] }
                    }
                  }
                }
              },
              in: {
                _id: "$$grItem._id",
                name: "$$roleInfo.name",
                type: "$$roleInfo.type",
                _grIndex: "$$grItem._grIndex",
                members: {
                  $map: {
                    input: { $range: [0, { $size: { $ifNull: ["$$grItem.members", []] } }] },
                    as: "mIdx",
                    in: {
                      _userId: { $arrayElemAt: ["$$grItem.members", "$$mIdx"] },
                      _mIndex: "$$mIdx"
                    }
                  }
                }
              }
            }
          }
        }
      }
    }
  },
  // 收集所有用户ID，批量关联User集合
  {
    $set: {
      _allUserIds: {
        $reduce: {
          input: "$groupRoles",
          initialValue: [],
          in: { $concatArrays: ["$$value", "$$this.members._userId"] }
        }
      }
    }
  },
  {
    $lookup: {
      from: "User",
      localField: "_allUserIds",
      foreignField: "_id",
      as: "_userRes"
    }
  },
  // 按原始索引重组数组，替换members为完整用户信息
  {
    $set: {
      groupRoles: {
        $map: {
          input: { $sortArray: { input: "$groupRoles", sortBy: { _grIndex: 1 } } },
          as: "grItem",
          in: {
            _id: "$$grItem._id",
            name: "$$grItem.name",
            type: "$$grItem.type",
            members: {
              $map: {
                input: { $sortArray: { input: "$$grItem.members", sortBy: { _mIndex: 1 } } },
                as: "mItem",
                in: {
                  $first: {
                    $filter: {
                      input: "$_userRes",
                      cond: { $eq: ["$$this._id", "$$mItem._userId"] }
                    }
                  }
                }
              }
            }
          }
        }
      }
    }
  },
  // 清理临时字段
  {
    $unset: ["_roleRes", "_allUserIds", "_userRes"]
  }
])

• 输出完全匹配需求：groupRoles数组顺序与原集合完全一致，每个元素携带关联得到的角色type、name字段；每个角色下的members数组顺序与原集合完全一致，元素为对应用户的完整_id、email、name信息，空成员数组会自动保留为空。
• 两次关联均采用批量拉取模式，没有使用$unwind打散文档，性能远高于逐元素拆解重组的实现，适合数据量较大的场景。
• 对空members数组做了兼容处理，不会因为字段为空抛出类型错误。
• 如果使用MongoDB 4.4及更早版本（无$sortArray操作符），可以跳过排序步骤，直接按生成的索引顺序取元素重组members数组，替换对应阶段的members逻辑即可：

// 4.4及以下版本members重组逻辑
members: {
  $map: {
    input: { $range: [0, { $size: "$$grItem.members" }] },
    as: "mIdx",
    in: {
      $let: {
        vars: {
          targetId: { $arrayElemAt: ["$$grItem.members._userId", "$$mIdx"] }
        },
        in: {
          $first: {
            $filter: {
              input: "$_userRes",
              cond: { $eq: ["$$this._id", "$$targetId"] }
            }
          }
        }
      }
    }
  }
}

内容的提问来源于stack exchange，提问作者Hoang An Nguyen