I have following shcemas:

employeeSchema: {
     _id: ObjectId
     isActive: Boolean
}

careerSchema: {
    employeeId: ObjectId // points to the _id from employeeSchema
    isCurrentlyWorking: Boolean
    position: String // designation
}

I want to find the employees that have field isActive: true. Also, the same employee may have more than one document in the Career collection. I want to find the document with isCurrentlyWorking: true and want that document's position field to be in the employee document's career field. I have tried the following:

const result = await Employee.aggregate([
    { $match: { isActive: true } },
    {
         from: "careers",
         localField: "_id",
         foreignField: "employeeId",
         as: "career",
         pipeline: [
             { $match: { isCurrentlyWorking: true} },
             { $project: { position: 1} }
         ]
    }
])

The problem that I have faced with this is the field career is an empty array in the result. And because of that I cannot add { $unwind: "$career" } to the aggregate function. One more thing, some employees may not have any correspoding document in the Careers collection. In that case, I just want the result to have career: null.

CodePudding user response：

If I understand you correctly you want to do something like:

db.employees.aggregate([
  {$match: {isActive: true}},
  {$lookup: {
      from: "careers",
      localField: "_id",
      foreignField: "employeeId",
      as: "career",
      pipeline: [
        {$match: {isCurrentlyWorking: true}},
        {$project: {position: 1}}
      ]
  }},
  {$unwind: {
      path: "$career",
      preserveNullAndEmptyArrays: true
  }},
  {$set: {career: {$ifNull: ["$career", null]}}}
])

See how it works on the playground example