I  made the below query :  

db.employees.aggregate([
        {
            $match: {
                isActive: true,
            }
        },
        {
            $lookup: {
                from: 'shifts',
                localField: '_id',
                foreignField: 'employee',
                as: 'shifts'
            }
        },
        {
            $unwind: {
                path: '$shifts',
                preserveNullAndEmptyArrays: true,
            }
        }
])

 And now getting the bellow result:

[
    {
        "_id": "621eedae92979fd8f0e9451d",
        "name": "Pallab Koley",
        "shifts": {
            "_id": "62636b9fcbda6d2b17f5cae0",
            "month": "2022-05",
            "shift": [
                {
                    "date": "2022-05-01",
                    "shiftId": "622bb0f4b88dc92e3c2cac56"
                }
            ]
        }
    },
    {
        "_id": "621eedae92979fd8f0e9451d",
        "name": "Pallab Koley",
        "shifts": {
            "_id": "62636bb3cbda6d2b17f5cae2",
            "month": "2022-04",
            "shift": [
                {
                    "date": "2022-04-01",
                    "shiftId": "622bb0f4b88dc92e3c2cac56"
                }
            ]
        }
    },
    {
        "_id": "62626a7446ba9a911a623b37",
        "name": "Pinki Das",
        "shifts": {
            "_id": "62636ba4cbda6d2b17f5cae1",
            "month": "2022-05",
            "shift": [
                {
                    "date": "2022-05-01",
                    "shiftId": "622bb0f4b88dc92e3c2cac56"
                }
            ]
        }
    }
]

I need to filter the data with particular month I am adding

{
            $unwind: {
                path: '$shifts',
                preserveNullAndEmptyArrays: true,
            }
        },
        {
            $match: {
                "shifts.month": “2022-04”
            }
        },

I am getting the result

[
    {
        "_id": "621eedae92979fd8f0e9451d",
        "name": "Pallab Koley",
        "shifts": {
            "_id": "62636bb3cbda6d2b17f5cae2",
            "month": "2022-04",
            "shift": [
                {
                    "date": "2022-04-01",
                    "shiftId": "622bb0f4b88dc92e3c2cac56"
                }
            ]
        }
    }
]

But my requirement is that, I need the all others existing employee from employees collection even also there is no record in shifts collection. Please help to get that.


CodePudding user response：

Probably this is what you need.

1. $match stage
2. $lookup stage
3. Remove $unwind stage.
4. $project stage - With $filter operator to filter the document in shifts array which month is 2022-04.

db.collection.aggregate([
  // $match ,
  // $lookup,
  // Remove $unwind
  {
    $project: {
      id: 1,
      name: 1,
      shifts: {
        "$filter": {
          "input": "$shifts",
          "cond": {
            $eq: [
              "$$this.month",
              "2022-04"
            ]
          }
        }
      }
    }
  }
])

Sample Mongo Playground