Mongodb aggregation lookup 2 level, created new entry of the result instead of just populating the column that have the objectId

Question

I have already asked this question, but for some reason someone marked it as duplicate to this $lookup multiple levels without $unwind? and i don't understand why, my expected output is to directly populate the "tenant" field, and in the pointed post, it creates a new entry for the aggregate result, not populating the column directly.

However here it goes again:

--room collection

_id: ObjectId(********)
bedspaces: Array
0: ObjectId("5c98d89c6bd5fc26a4c2851b")
1: ObjectId("5c98d89f6bd5fc26a4c28522")
***
***

---beds collection

_id: ObjectId(*********);
number: 1
decks: Array
 0: Object 
     _id: ObjectId("5c98d89c6bd5fc26a4c28520")
     number: 1,
     tenant: ObjectId("5c964ae7f5097e3020d1926c")
     ****
 1: Object 
     _id: ObjectId("5c98d89c6bd5fc26a4c2851e")
     number: 1,
     tenant: ObjectId("5c964b2531bc162fdce64f15")
     ****

--tenants collection

 _id: ObjectId("5c964ae7f5097e3020d1926c")
 name: "john doe"
 age: 11,

 _id: ObjectId("5c964b2531bc162fdce64f15")
 name: "jane doe"
 age: 12,

Here is my aggregation:

{
  from: 'beds',
  let: { bedspaces: "$bedspaces"},
  pipeline: [
    {
      $match: {
        $expr: {
          $in: ["$_id", "$$bedspaces"]
        }
      }
    },
    {
      $lookup: {
        from: 'tenants',
        let: {tenant: "$decks.tenant"},
        pipeline: [
         {
          $match: {
            $expr: {
              $in: ["$_id", "$$tenant"]
            }
          }
         }
        ],
        as: 'tenant',
      }
    }
  ],
  as: 'bedspaces'
}

output: enter image description here

what i want is to populate the tenant field, with its matching _id in tenants collection.

P.S is this even possible?

Ashh · Accepted Answer · 2019-03-28T16:44:40.737

If you would have read that answer then you might get the reason for making duplicate it.

Meanwhile you can use below aggregation but better if you still go through that answer and it's explanation.

Room.aggregate([
  { "$lookup": {
    "from": "beds",
    "let": { "bedspaces": "$bedspaces" },
    "pipeline": [
      { "$match": { "$expr": { "$in": [ "$_id", "$$bedspaces" ] } } },
      { "$unwind": "$decs" },
      { "$lookup": {
        "from": "tenants",
        "let": { "tenant": "$decks.tenant" },
        "pipeline": [
          { "$match": { "$expr": { "$eq": [ "$_id", "$$tenant" ] } } }           
        ],
        "as": "decks.tenant"
      }},
      { "$unwind": "$decks.tenant" },
      { "$group": {
        "_id": "$_id",
        "decks": { "$push": "$decs" },
        "number": { "$first": "$number" }
      }}
    ],
    "as": "bedspaces"
  }}
])

Mongodb aggregation lookup 2 level, created new entry of the result instead of just populating the column that have the objectId

1 Answers1