带有引用集合的Mongodb聚合

Question

我想得到我的第二级会员用户的列表，我设计的集合如下

用户

{ 
    "_id" : ObjectId("5a1b9df7bfdbfef2d4f1e9f3"), 
    "name" : "name 1",
    "affKey" : "H1g-CfFxG", 
}
{ 
    "_id" : ObjectId("5a1bce5e9a2918f71a9ac4fb"), 
    "name" : "name 2",
    "affKey" : "K1gKJfFxG", 
}

粘粒

{  
    "affKey" : "H1g-CfFxG", 
    "affUsers" : [ 
        ObjectId("5a1bce5e9a2918f71a9ac4fb")
    ], 
}

{  
    "affKey" : "K1gKJfFxG", 
    "affUsers" : [
        ObjectId("5a1b9e43bfdbfef2d4f1e9f8"),
        ObjectId("5a1b9e43bfdbfef2d4f1e911"),
    ], 
}

在这里，我将在affiliate.affKey中保存一套新的基于Users.affKey的集合

现在我想得到我的第一级和第二级附属公司的列表，即5a1b9e43bfdbfef2d4f1e9f6和5a1bce5e9a2918f71a9ac4fb以及一级附属机构的子公司列表。

期待这样的结果

{
  first: [first level affiliates] // 1 result 
  second: [second level affiliates] // 2 results
}

Answer 1

对于这种情况，您必须采取一些步骤才能得到预期的结果。可以跟随下面的步骤

1. $lookup用于affKey的affiliets集合
2. 然后，您必须对$lookup集合进行users以获取引用用户信息。
3. 之后，您必须再次$lookup第二级用户affKey的affiliets集合。

所以查询可以像下面这样

db.users.aggregate([
  {
    $lookup: {
      from: "affilites",
      localField: "affKey",
      foreignField: "affKey",
      as: "affUsers"
    }
  },
  {
    $project: {
      name: 1,
      affKey: 1,
      first: {$arrayElemAt: ["$affUsers.affUsers", 0]},
      secondLevelUserId: {$arrayElemAt: ["$affUsers.affUsers", 0]}
    }
  },
  { $unwind: { path: "$secondLevelUserId", "preserveNullAndEmptyArrays": true }},
  {
    $lookup: {
      from: "users",
      localField: "secondLevelUserId",
      foreignField: "_id",
      as: "secondLevelUser"
    }
  },
  {
    $project: {
      name: 1,
      affKey: 1,
      first: 1,
      secondLevelUser: {$arrayElemAt: ["$secondLevelUser", 0]}
    }
  },
  {
    $lookup: {
      from: "affilites",
      localField: "secondLevelUser.affKey",
      foreignField: "affKey",
      as: "secondLevelUser"
    }
  },
  {
    $project: {
      name: 1,
      affKey: 1,
      first: 1,
      second: {$arrayElemAt: ["$secondLevelUser.affUsers", 0]}
    }
  },
  {
    $unwind: {
      "path": "$second",
      "preserveNullAndEmptyArrays": true
    }
  },
  {
    $group: {
      _id: "$_id",
      name: {$first: "$name"},
      affKey: {$first: "$affKey"},
      first: {$first: "$first"},
      second: {$addToSet: "$second"}
    }
  }
]);

执行查询后，您将得到如下结果

第一份文件：

{
    "_id" : ObjectId("5a1b9df7bfdbfef2d4f1e9f3"),
    "name" : "name 1",
    "affKey" : "H1g-CfFxG",
    "first" : [ 
        ObjectId("5a1bce5e9a2918f71a9ac4fb")
    ],
    "second" : [ 
        ObjectId("5a1b9e43bfdbfef2d4f1e911"), 
        ObjectId("5a1b9e43bfdbfef2d4f1e9f8")
    ]
}

第二份文件

{
    "_id" : ObjectId("5a1bce5e9a2918f71a9ac4fb"),
    "name" : "name 2",
    "affKey" : "K1gKJfFxG",
    "first" : [ 
        ObjectId("5a1b9e43bfdbfef2d4f1e9f8"), 
        ObjectId("5a1b9e43bfdbfef2d4f1e911")
    ],
    "second" : []
}