Mongodb aggregation and lookup

Issue

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.


Solution

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

Answered By – Yong Shun

Answer Checked By – David Goodson (AngularFixing Volunteer)

Leave a Reply

Your email address will not be published.