已知用户ID，如何用PyMongo关联MongoDB多集合查询指定数据？

集合结构 #

user : { "id": 1}
group: { "gid": 13, "name": "group1"}
subgroup: {"sgid": 22, "gid": 13, "name": "sgroup1"}
maps : {"userId": 1, "id": 10, "mapId": 13}
data: {"sgid": 22, "somedata": "somedata"}

查询需求 #

已知当前user的id，需完成以下关联查询：

• 通过userId从maps集合获取mapId
• 使用mapId匹配subgroup的gid，获取sgid和subgroup名称
• 通过sgid获取data集合的数据
• 同时获取对应group的名称

聚合查询实现（MongoDB为例） #

利用聚合管道的$lookup阶段完成多集合关联，示例代码如下：

db.user.aggregate([
  // 匹配目标用户
  { $match: { id: 1 } },
  // 关联maps集合，获取mapId
  {
    $lookup: {
      from: "maps",
      localField: "id",
      foreignField: "userId",
      as: "map_info"
    }
  },
  { $unwind: "$map_info" },
  // 关联subgroup集合，获取sgid和子组名称
  {
    $lookup: {
      from: "subgroup",
      localField: "map_info.mapId",
      foreignField: "gid",
      as: "subgroup_info"
    }
  },
  { $unwind: "$subgroup_info" },
  // 关联group集合，获取组名称
  {
    $lookup: {
      from: "group",
      localField: "subgroup_info.gid",
      foreignField: "gid",
      as: "group_info"
    }
  },
  { $unwind: "$group_info" },
  // 关联data集合，获取目标数据
  {
    $lookup: {
      from: "data",
      localField: "subgroup_info.sgid",
      foreignField: "sgid",
      as: "data_info"
    }
  },
  { $unwind: "$data_info" },
  // 整理输出字段
  {
    $project: {
      _id: 0,
      user_id: "$id",
      group_name: "$group_info.name",
      subgroup_name: "$subgroup_info.name",
      target_data: "$data_info.somedata"
    }
  }
])

查询结果 #

执行上述聚合后，将返回如下结构化结果：

{
  "user_id": 1,
  "group_name": "group1",
  "subgroup_name": "sgroup1",
  "target_data": "somedata"
}

内容的提问来源于stack exchange，提问作者Anil Kumar H P