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

本文介绍了具有 3 个级别的 MongoDB 嵌套查找的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我需要从数据库中以 JSON 形式检索整个单个对象层次结构.实际上，关于实现这一结果的任何其他解决方案的建议将受到高度赞赏.我决定使用具有 $lookup 支持的 MongoDB.

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

所以我有三个集合:

派对

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

我需要检索所有相关方的所有对应地址和地址注释作为记录的一部分.我的聚合:

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

结果很奇怪.有些记录没问题.但是缺少 _id: 4 的派对(没有地址).另外，结果集中有两个Party _id: 1(但地址不同):

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": "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 还很陌生，但我觉得它可以满足我的需求.

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

推荐答案

你麻烦"的原因是第二个聚合阶段 - { $unwind: "$address" }.它删除具有 _id: 4 的当事人的记录(因为它的地址数组是空的，正如你所提到的)并为当事人 _id: 1 和 _id 生成两条记录:5(因为每个都有两个地址).

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: 1 and _id: 5 (because each of them has two addresses).

• 为了防止删除没有地址的参与方，您应该设置 preserveNullAndEmptyArrays 选项/" rel="noreferrer">$unwind 阶段到 true.

• To prevent removing of parties without addresses you should set preserveNullAndEmptyArrays option of $unwind stage to true.

为了防止不同地址的参与者重复，您应该添加 $group 聚合阶段到您的管道.此外，使用 $project 阶段$filter 操作符排除空地址输出中的记录.

To prevent duplicating of parties for its different addresses you should add $group aggregation stage to your pipeline. Also, use $project stage with $filter operator to exclude empty address records in output.

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