How to Merge Two Collections with one being an Array of Objects in the Other

Working with Data
1

Hi All,

I have two collections, one being Companies and the others being Projects. I am trying to write an aggregation function that first grabs all Companies with the status of “Client”, then from there write a pipeline that will return all filtered Companies where the company._id === project.companyId, as an Array of Objects. An example of the shortened Collections are below:

Companies
{
_id: ObjectId(‘2341908342’),
companyName: “Meta”,
address: “123 Facebook Lane”,
}

Projects
{
_id: ObjectId(‘234123840’),
companyId: ‘2341908342’,
name: “Test Project”,
price: 97450,
}
{
_id: ObjectId(‘23413456’),
companyId: ‘2341908342’,
name: “Test Project 2”,
price: 100000,
}

My desired outcome after the Aggregation:

Companies and Projects
{
_id: ObjectId(‘2341908342’),
companyName: “Meta”,
address: “123 Facebook Lane”,
projects: [ [Object1], [Object2],
}

The projects field does not currently exist on the Companies collection, so I imagine we would have to add it. I also begun writing a $match function to filter by clients, but I am not sure if this is correct. I am trying to use $lookup for this but can not figure out the pipeline. Can anyone help me?

Where I’m currently stuck:

try {
const allClientsWithProjects = await companyCollection
.aggregate([
{
$match: {
orgId: {
$in: [new ObjectId(req.user.orgId)],
},
status: { $in: [“Client”] },
},
},
{
$addFields: {
projects: [{}],
},
},
{
$lookup: { from: “projects”, (I am stuck here) },
},
])
.toArray()

Thank you for any help anyone can provide.

3

Please read Formatting code and log snippets in posts and update your documents and code accordingly.

When formatted correctly we can cut-n-paste into our systems to investigate.