MongoDB三层嵌套查找

Question

MongoDB三层嵌套查找

mongodbmongodb-queryaggregation-frameworklookup

81

我需要以JSON格式从数据库中检索整个单一对象层次结构。实际上，对于任何其他解决此问题的方案提议都将不胜感激。我决定使用带有$lookup支持的MongoDB。

因此，我有三个集合：

party

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

地址

{ "_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" }

地址评论

{ "_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" }

我需要检索所有方当事人的对应地址以及地址注释，作为记录的一部分。我的聚合操作：

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"
    }
}])

结果相当奇怪。有些记录没问题。但是 _id: 4 的 Party 缺失了（没有地址）。此外，结果集中有两个 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"
        }]
    }
}

请帮我解决这个问题。我对MongoDB不是很熟悉，但我觉得它可以满足我的需求。

- Yuriy

这对未来的开发人员也可能有所帮助：https://www.mongodb.com/docs/manual/reference/operator/aggregation/lookup/#perform-multiple-joins-and-a-correlated-subquery-with--lookup - Gabriel Arghire

2个回答

98

使用mongodb3.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" }
])

- Ashh

如果需要访问内部管道中的partyId，你会如何进行访问？ - Paulo

@Paulo 通过使用 $$ 符号 - Ashh

1

@Paulo，请参考Mongo文档中的此链接以获得更清晰的解释。 - Alvaro Carvalho

嗨@Ashh，你能在https://dev59.com/0az2oIgBc1ULPQZFfp4c检查一下我的问题吗？ - Nazarudin

@Paulo 在这个例子中，它在lookup中做了同样的事情。$field总是指本地对象字段。因此，您可以在内部查找中访问partyid：{"partyId":"$party_id"} - Данияр

显示剩余2条评论

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Shad · Accepted Answer

您的“问题”出现在第二个聚合阶段-{ $unwind: "$address" }。它会删除_id: 4的一方记录（因为其地址数组为空，正如您所提到的），并为带有两个地址的_id：1和_id: 5的派对产生两条记录。

要防止删除没有地址的派对，您应该将$unwind阶段的preserveNullAndEmptyArrays选项设置为true。

要防止为不同地址的同一派对重复，请向管道添加$group聚合阶段。此外，请使用具有$filter运算符的$project阶段来排除输出中的空地址记录。

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] } }
    } 
  }
}]);