MongoDB nested lookup with 3 levels
Solution 1
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).
To prevent removing of parties without addresses you should set
preserveNullAndEmptyArrays
option of$unwind
stage totrue
.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] } }
}
}
}]);
Solution 2
With the mongodb 3.6 and above $lookup
syntax it is quite simple to join nested fields without using $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" }
])
Yuriy
Updated on July 05, 2022Comments
-
Yuriy almost 2 years
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.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": "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 MongoDB but I feel it can do what I need from it.