MongoDB $lookup on 2 level nested document without using $unwind

Question

I have the following documents

loanRequest (Writing just the keys that I want to project)

{
  "_id": "5f2bf26783f65d33026ea592",
  "lendingpartner": { 
     /* some keys here */
  },
  "loans": [
    {
      "loanid": 43809,
      "loanamount": 761256,
      "jewels": [
        "5f2bf26783f65d33026ea593",
        "5f2bf26783f65d33026ea594"
        "5f2bf26783f65d33026ea595"
      ],
    }
  ]
}

pledgedJewel

{
  "_id": "5f2bf26783f65d33026ea593",
  "netweight": 8.52,
  "purity": 19,
}

---

What I want to achieve is

{
  "_id": "5f2bf2b583f65d33026ea603",
  "lendingpartner": { 
     /* some keys here */
  },
  "loans": [
    {
      "loanid": 40010,
      "loanamount": 100000,
      "jewels": [
        {
          "_id": "5f2bf26783f65d33026ea593",
          "netweight": 8.52,
          "purity": 19,
        },
        {
          "_id": "5f2bf26783f65d33026ea594",
          "netweight": 5.2,
          "purity": 40,
        },
        {
          "_id": "5f2bf26783f65d33026ea595",
          "netweight": 4.52,
          "purity": 39,
        }
      ]
    }
  ]
}

Since I want the jewel details to be populated inside the jewels array of each loan, $unwind would not help me. (I tried experimenting with it)

I thought I could run a $map on loans array and then run $lookup for each jewel of the loan(double map?), but could not come up with a workable solution. That didn't seem to be the right approach anyway.

---

This is the best I could come up with (Far from my desired result). I'm using map to selectively pick keys from loans object.

const loanrequests = await db.collection('loanrequest').aggregate([
  { $match: { requester: ObjectID(user.id) } },
  {
    $project: {
      lendingpartner: {
        name: 1,
        branchname: '$branch.branchname',
      },
      loans: {
        $map: {
          input: '$loans',
          as: 'loan',
          in: {
            loanid: '$$loan.loanid',
            loanamount: '$$loan.amount',
            jewels: '$$loan.jewels',
          },
        },
      },
    },
  },
  /*
  * I experimented with unwind here. Tried adding 
  * { $unwind: '$loans' },
  * { $unwind: '$loans.jewels' }
  * but it does not give me the result I need (as already said before)
  */
]).toArray();

I figure, I need to do the $lookup before the projection, but I'm it hard to write a workable solution due to 2 level nested structure of the document (First, the loans array and then loans.jewels)

I started working with mongodb aggregators today and while looking for answers, I stumbled upon a similar Question but it seemed more complex and hence harder for me to understand.

Thanks!

Answer 1

If there are not other things you are trying to achieve with aggregate you can use .populate in mongoose.

LoanReqests
  .find(
    {requester: user.id},
    {name: 1, branch: 1, loans: 1} // Projection
  )
  .populate('loans.jewels');

If you have to use aggregate to do something not in your example, then $unwind is really your best bet, but then $grouping after the $lookup to get the output you desire. If this doesn't work for you, can you expand on what the issue with $unwind is? I am guessing it is to do with fields not listed in your question.

https://mongoplayground.net/p/O5pxWNy99J4

db.loanRequests.aggregate([
  {
    $project: {
      name: 1,
      loans: 1,
      branch: "$branch.name"
    }
  },
  {
    $unwind: "$loans"
  },
  {
    $lookup: {
      localField: "loans.jewels",
      foreignField: "_id",
      from: "jewels",
      as: "loans.jewels"
    }
  },
  {
    $group: {
      _id: "$_id",
      name: {
        $first: "$name"
      },
      branch: {
        $first: "$branch"
      },
      loans: {
        $push: "$loans"
      }
    }
  }
])

Answer 2

As mentioned by @GitGitBoom in the previous answer, $unwind followed by $group should have been the approach.

Ofcourse, prior to grouping (I think of it as "unspreading" the outcome of running unwind), I needed to run $lookup in order to populate loans.jewels

Here is the entire solution build on top of the previous answer.

const loanRequests = await db.collection('loanRequest').aggregate([
  { $match: { requester: ObjectID(user.id) } },
  {
    $project: {
      lender: '$lendingpartner.name',
      branch: '$lendingpartner.branch.branchname',
      loans: 1,
    },
  },
  { $unwind: '$loans' },
  {
    $lookup: {
      localField: 'loans.jewels',
      from: 'pledgedJewel',
      foreignField: '_id',
      as: 'loans.jewels',
    },
  },
  {
    $group: {
      _id: '$_id',
      branch: { $first: '$branch' },
      lender: { $first: '$lender' },
      loans: { $push: '$loans' },
    },
  },
  {
    $project: {
      _id: 1,
      branch: 1,
      lender: 1,
      loans: 1,
    },
  },
]).toArray();

---

Issue with mismatch of types

Another issue was, my $lookup was not working due to mismatch of types. In loanRequest collection, on which I'm running the aggregate, the ids inside loans.jewels are of type string whereas the foreign field _id in pledgedJewel is an ObjectId

This can be solved by using $toObjectId or $toString (only supported in mongodb version >= 4.0)

{ $project: { jewelObjId: { $toObjectId: '$loans.jewels' } } },   // for mongodb >= 4.0
{
  $lookup: {
    localField: 'jewelObjId',  // for mongodb >= 4.0
    from: 'pledgedjewel',
    foreignField: '_id',
    as: 'loans.jewels',
  },
},

But, I was running on a lower version of mongodb, hence these aggregations were not working for me. Only solution to this was to change the type of loans.jewels to ObjectId instead of keeping it as string which I did.

More on type mismatch

Need a workaround for lookup of a string to objectID foreignField

Mongodb Join on _id field from String to ObjectId