How to join two Mongo DB Collections together, with one being an Array of Objects inside the Other-CodePudding

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",
status: "Client"
}

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
{
_id: ObjectId('2341908342'),
companyName: "Meta",
address: "123 Facebook Lane",
projects: [ [Project1], [Project2],
}

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.

UPDATE*

I am seemingly so close I feel like... This is what I have currently, and it is returning everything but Projects is still an empty array.

 try {
const allClients = await companyCollection
  .aggregate([
    {
      $match: {
        orgId: {
          $in: [new ObjectId(req.user.orgId)],
        },
        status: {
          $in: ["Client"],
        },
      },
    },
    {
      $lookup: {
        from: "projects",
        let: {
          companyId: {
            $toString: [req.user.companyId],
          },
        },
        pipeline: [
          {
            $match: {
              $expr: {
                $eq: ["$companyId", "$$companyId"],
              },
            },
          },
        ],
        as: "projects",
      },
    },
  ])
  .toArray()

All of my company information is being returned correctly for multiple companies, but that projects Array is still []. Any help would be appreciated, and I will still be troubleshooting this.

CodePudding user response：

One option is using a $lookup with a pipeline:

db.company.aggregate([
  {
    $match: {
      _id: {
        $in: [
          ObjectId("5a934e000102030405000000")
        ],
        
      },
      status: {
        $in: [
          "Client"
        ]
      },
      
    },
    
  },
  {
    $lookup: {
      from: "Projects",
      let: {
        companyId: {
          $toString: "$_id"
        }
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $eq: [
                "$companyId",
                "$$companyId"
              ]
            }
          }
        }
      ],
      as: "projects"
    }
  }
])

See how it works on the playground example

CodePudding user response：

Final answer for my question:

try {
const allClientsAndProjects = await companyCollection
  .aggregate([
    {
      $match: {
        orgId: {
          $in: [new ObjectId(req.user.orgId)],
        },
        status: {
          $in: ["Client"],
        },
      },
    },
    {
      $lookup: {
        from: "projects",
        let: {
          companyId: {
            $toString: "$_id",
          },
        },
        pipeline: [
          {
            $match: {
              $expr: {
                $eq: ["$companyId", "$$companyId"],
              },
            },
          },
        ],
        as: "projects",
      },
    },
  ])
  .toArray()