I am learning MongoDB Aggregation and have been going back and forth with the problem below. I have two collections, modeled as the following:

Contacts
{ 
_id: ObjectId('2341908342'),
fName: Test,
lName: TestTwo,
companyId: "61f4147b8415abfc04d09207",
},
{ 
_id: ObjectId('2341908342'),
fName: John,
lName: Doe,
companyId: "62847a16ee5c5047820415d2",
}

Companies
{
_id: ObjectId('61f4147b8415abfc04d09207'),
companyName: "Facebook",
domain: "www.facebook.com",
}
{
_id: ObjectId('62847a16ee5c5047820415d2'),
companyName: "Google",
domain: "www.Google.com",
}

I am trying to write an aggregation function that will grab all of the Contacts, then match the companyId between the Contacts and the Companies, and project the data as such:

Contacts 
{
_id: ObjectId('2341908342'),
fName: Test,
lName: TestTwo,
companyId: "61f4147b8415abfc04d09207",
companyName: "Facebook",
domain: "www.facebook.com",
}

As of right now, I am doing this as so in Typescript:

   await allContactsCursor.forEach((contact: any) => {
      if (contact.companyId) {
        const company = allCompanies.find(
          (co) => co._id.toString() === contact.companyId,
        )
        if (company) {
          allContactsCursor.push({
            ...contact,
            companyName: company.companyName,
            domain: company.domain,
          })
        } else {
          allContactsCursor.push(contact)
        }
      } else {
        allContactsCursor.push(contact)
      }
    })

But would like to do this through Aggregation. I am still learning Aggregation, but here is what I have so far:

try {
    const allContactsCursor = await contactsCollection
      .aggregate([
        {
          $match: {
            orgId: {
              $in: [new ObjectId(req.user.orgId)],
            },
          },
        },
        {
          $lookup: {
            from: "companies",
            let: {
              companyId: {
                $toString: "$_id",
              },
            },
            pipeline: [
              {
                $match: {
                  $expr: {
                    $eq: ["$companyId", "$$companyId"],
                  },
                },
              },
            ],
            as: "companyName",
          },
        },
      ])
      .toArray()

I know this is incorrect, but I imagine I need to compare Company ID's, then write a $project after this to get my companyName and domain fields added. However, I am not sure, or even sure if this is possible. Thank you for any help you can provide.

CodePudding user response：

Just use $unwind to handle the $lookup result array and use $mergeObjects and $replaceRoot to wrangle the result to your expected form.

db.Contacts.aggregate([
  {
    "$lookup": {
      "from": "Companies",
      "let": {
        companyId: "$companyId"
      },
      "pipeline": [
        {
          $match: {
            $expr: {
              $eq: [
                "$$companyId",
                {
                  $toString: "$_id"
                }
              ]
            }
          }
        }
      ],
      "as": "companyLookup"
    }
  },
  {
    $unwind: "$companyLookup"
  },
  {
    "$replaceRoot": {
      "newRoot": {
        "$mergeObjects": [
          "$$ROOT",
          {
            companyId: "$companyLookup.companyId",
            companyName: "$companyLookup.companyName"
          }
        ]
      }
    }
  },
  {
    $unset: "companyLookup"
  }
])

Mongo Playground

A side note: consider refactor your schema to keep the data type consistent. i.e. companyId should be either ObjectId or string in both collections.