MongoDB nested lookup with 3 levels

by

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

More Related Contents:

  1. Mongodb Join on _id field from String to ObjectId
  2. $project in $lookup mongodb
  3. Retrieve only the queried element in an object array in MongoDB collection
  4. Update MongoDB field using value of another field
  5. Does MongoDB’s $in clause guarantee order
  6. MongoDB: Combine data from multiple collections into one..how?
  7. Multiple join conditions using the $lookup operator
  8. Conditional $sum in MongoDB
  9. Return only matched sub-document elements within a nested array
  10. Converting string to date in mongodb
  11. exclude fields in $lookup aggregation
  12. MongoDB aggregation comparison: group(), $group and MapReduce
  13. Add some kind of row number to a mongodb aggregate command / pipeline
  14. MongoDB group by array inner-elements
  15. Multiple Counts with single query in mongodb
  16. Mongo sort on a calculated condition
  17. MongoDB sort documents by array elements
  18. How to return array of string with mongodb aggregation
  19. How to return just the nested documents of an array from all documents
  20. MongoDB: Calculate dwell time between every status value change
  21. MongoDB: how to parse date in 3.6 mongoDb version?
  22. Using stored JavaScript functions in the Aggregation pipeline, MapReduce or runCommand
  23. MongoDB – The argument to $size must be an Array, but was of type: EOO / missing
  24. convert iso date to timestamp in mongo query
  25. Remove Duplicates from MongoDB
  26. Using $slice operator to get last element of array
  27. How to access overall document count during arithmetic aggregation expression
  28. Mongodb aggregation pipeline how to limit a group push
  29. Return actual type of a field in MongoDB
  30. Return the last “true” value for each group

Leave a Comment