MongoDB nested lookup with 3 levels

Question

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.

Answer 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 to true.

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

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