具有 3 个级别的 MongoDB 嵌套查找

MongoDB nested lookup with 3 levels

提问by Yuriy

I need to retrieve the entire single object hierarchy from the database as a JSON. Actually the proposal about any other solution to achive this result would be highly appriciated. I decided to use MongoDB with its $lookup support.

我需要从数据库中检索整个单个对象层次结构作为 JSON。实际上，有关实现这一结果的任何其他解决方案的提议将受到高度评价。我决定使用具有 $lookup 支持的 MongoDB。

So I have three collections:

所以我有三个集合：

party

派对

{ "_id" : "2", "name" : "party2" }
{ "_id" : "5", "name" : "party5" }
{ "_id" : "4", "name" : "party4" }
{ "_id" : "1", "name" : "party1" }
{ "_id" : "3", "name" : "party3" }    

address

地址

{ "_id" : "a3", "street" : "Address3", "party_id" : "2" }
{ "_id" : "a6", "street" : "Address6", "party_id" : "5" }
{ "_id" : "a1", "street" : "Address1", "party_id" : "1" }
{ "_id" : "a5", "street" : "Address5", "party_id" : "5" }
{ "_id" : "a2", "street" : "Address2", "party_id" : "1" }
{ "_id" : "a4", "street" : "Address4", "party_id" : "3" }

addressComment

地址评论

{ "_id" : "ac2", "address_id" : "a1", "comment" : "Comment2" }
{ "_id" : "ac1", "address_id" : "a1", "comment" : "Comment1" }
{ "_id" : "ac5", "address_id" : "a5", "comment" : "Comment6" }
{ "_id" : "ac4", "address_id" : "a3", "comment" : "Comment4" }
{ "_id" : "ac3", "address_id" : "a2", "comment" : "Comment3" }

I need to retrieve all parties with all corresponding addresses and address comments as part of the record. My aggregation:

我需要检索所有具有相应地址和地址注释的所有各方作为记录的一部分。我的聚合：

db.party.aggregate([{
    $lookup: {
        from: "address",
        localField: "_id",
        foreignField: "party_id",
        as: "address"
    }
},
{
    $unwind: "$address"
},
{
    $lookup: {
        from: "addressComment",
        localField: "address._id",
        foreignField: "address_id",
        as: "address.addressComment"
    }
}])

The result is pretty weird. Some records are ok. But Party with _id 4 is missing (there is no address for it). Also there are two Party _id 1 in the result set (but with different addresses):

结果很奇怪。有些记录没问题。但是缺少_id 4 的派对（没有地址）。结果集中还有两个 Party _id 1（但地址不同）：

{
    "_id": "1",
    "name": "party1",
    "address": {
        "_id": "2",
        "street": "Address2",
        "party_id": "1",
        "addressComment": [{
            "_id": "3",
            "address_id": "2",
            "comment": "Comment3"
        }]
    }
}{
    "_id": "1",
    "name": "party1",
    "address": {
        "_id": "1",
        "street": "Address1",
        "party_id": "1",
        "addressComment": [{
            "_id": "1",
            "address_id": "1",
            "comment": "Comment1"
        },
        {
            "_id": "2",
            "address_id": "1",
            "comment": "Comment2"
        }]
    }
}{
    "_id": "3",
    "name": "party3",
    "address": {
        "_id": "4",
        "street": "Address4",
        "party_id": "3",
        "addressComment": []
    }
}{
    "_id": "5",
    "name": "party5",
    "address": {
        "_id": "5",
        "street": "Address5",
        "party_id": "5",
        "addressComment": [{
            "_id": "5",
            "address_id": "5",
            "comment": "Comment5"
        }]
    }
}{
    "_id": "2",
    "name": "party2",
    "address": {
        "_id": "3",
        "street": "Address3",
        "party_id": "2",
        "addressComment": [{
            "_id": "4",
            "address_id": "3",
            "comment": "Comment4"
        }]
    }
}

Please help me with this. I'm pretty new to the MongoDB but I feel it can do what I need from it.

请帮我解决一下这个。我对 MongoDB 还很陌生，但我觉得它可以满足我的需求。

回答by Shad

The cause of your 'troubles' is the second aggregation stage - { $unwind: "$address" }. It removes record for party with _id: 4(because its address array is empty, as you mention) and produces two records for parties _id: 1and _id: 5(because each of them has two addresses).

您“麻烦”的原因是第二个聚合阶段 - { $unwind: "$address" }。它删除了party with的记录_id: 4（因为它的地址数组是空的，正如你所提到的）并为party _id: 1and产生两个记录_id: 5（因为他们每个人都有两个地址）。

• To prevent removing of parties without addresses you should set preserveNullAndEmptyArraysoption of $unwindstage to true.

• To prevent duplicating of parties for its different addresses you should add $groupaggregation stage to your pipeline. Also, use $projectstage with $filteroperator to exclude empty address records in output.

• 为防止删除没有地址的参与方，您应该将阶段preserveNullAndEmptyArrays选项设置$unwind为true。

• 为了防止不同地址的参与方重复，您应该$group在管道中添加聚合阶段。此外，使用$projectstage with $filteroperator 来排除输出中的空地址记录。

db.party.aggregate([{
  $lookup: {
    from: "address",
    localField: "_id",
    foreignField: "party_id",
    as: "address"
  }
}, {
  $unwind: {
    path: "$address",
    preserveNullAndEmptyArrays: true
  }
}, {
  $lookup: {
    from: "addressComment",
    localField: "address._id",
    foreignField: "address_id",
    as: "address.addressComment",
  }
}, {
  $group: {
    _id : "$_id",
    name: { $first: "$name" },
    address: { $push: "$address" }
  }
}, {
  $project: {
    _id: 1,
    name: 1,
    address: {
      $filter: { input: "$address", as: "a", cond: { $ifNull: ["$$a._id", false] } }
    } 
  }
}]);

回答by Ashh

With the mongodb 3.6and above $lookupsyntax it is quite simple to join nested fields without using $unwind.

使用 mongodb 3.6及更高版本的$lookup语法，无需使用$unwind.

db.party.aggregate([
  { "$lookup": {
    "from": "address",
    "let": { "partyId": "$_id" },
    "pipeline": [
      { "$match": { "$expr": { "$eq": ["$party_id", "$$partyId"] }}},
      { "$lookup": {
        "from": "addressComment",
        "let": { "addressId": "$_id" },
        "pipeline": [
          { "$match": { "$expr": { "$eq": ["$address_id", "$$addressId"] }}}
        ],
        "as": "address"
      }}
    ],
    "as": "address"
  }},
  { "$unwind": "$address" }
])