I have 2 collections

1st users and 2nd shifts

When I am writing a query like below with lookup and unwind expressions.

results.users = await model.aggregate([
  {
    $match: filter,
  },
  {
    $lookup: {
      from: "shifts",
      localField: "_id",
      foreignField: "employeeId",
      as: "shifts",
    },
  },
  {
    $unwind: { path: "$shifts", preserveNullAndEmptyArrays: true },
  },       
]);

The response I get, as you see below I am getting the last user multiple times because it has multiple shifts data of multiple dates and in the first two data, I am not getting any shift data because data is not present for that user, which is correct.

[
  {
    _id: "60dd781c4524e6c116e2336d",
    workerFirstName: "MADASWAMY",
    workerSurname: "KARUPPASWAMY",
    workerId: "1002",
  },
  {
    _id: "60dd781d4524e6c116e234d4",
    workerFirstName: "AMIT",
    workerSurname: "SHAH",
    workerId: "1001",
  },
  {
    _id: "60dd781d4524e6c116e23642",
    workerFirstName: "DEVELOPER",
    workerSurname: "DEVELOPER",
    workerId: "7738",
    shifts: {
      _id: "634d8d3ce596dd34c9532d7d",
      month: "October",
      workerId: "7738",
      date: "2022-10-01T00:00:00.000Z",
    },
  },
  {
    _id: "60dd781d4524e6c116e23642",
    workerFirstName: "DEVELOPER",
    workerSurname: "DEVELOPER",
    workerId: "7738",
    shifts: {
      _id: "634d8d3ce596dd34c9532d6d",
      month: "October",
      workerId: "7738",
      date: "2022-10-02T00:00:00.000Z",
    },
  },
  {
    _id: "60dd781d4524e6c116e23642",
    workerFirstName: "DEVELOPER",
    workerSurname: "DEVELOPER",
    workerId: "7738",
    shifts: {
      _id: "634d8d3ce596dd34c9532d5c",
      month: "October",
      workerId: "7738",
      date: "2022-10-31T00:00:00.000Z",
    },
  },
]

When I am writing the below query, with match expression after lookup and unwind.

results.users = await model.aggregate([
  {
    $match: filter,
  },
  {
    $lookup: {
      from: "shifts",
      localField: "_id",
      foreignField: "employeeId",
      as: "shifts",
    },
  },
  {
    $unwind: { path: "$shifts", preserveNullAndEmptyArrays: true },
  },
  {
    $match: {
      "shifts.date": new Date(formatTimeToIso(new Date())),
    },
  },
]);

The response I am getting is only 1 data which is also correct because the match expression works that way.

[
  {
    _id: "60dd781d4524e6c116e23642",
    workerFirstName: "DEVELOPER",
    workerSurname: "DEVELOPER",
    workerId: "7738",
    shifts: {
      _id: "634d8d3ce596dd34c9532d5d",
      month: "October",
      workerId: "7738",
      date: "2022-10-31T00:00:00.000Z",
    },
  },
]

But the response I want should be like the one below, so anything to add or remove from the expression so I can get the data in a format I want

[
  {
    _id: "60dd781c4524e6c116e2336d",
    workerFirstName: "MADASWAMY",
    workerSurname: "KARUPPASWAMY",
    workerId: "1002",
    shifts:{}
  },
  {
    _id: "60dd781d4524e6c116e234d4",
    workerFirstName: "AMIT",
    workerSurname: "SHAH",
    workerId: "1001",
    shifts:{}
  },
  {
    _id: "60dd781d4524e6c116e23642",
    workerFirstName: "DEVELOPER",
    workerSurname: "DEVELOPER",
    workerId: "7738",
    shifts: {
      _id: "634d8d3ce596dd34c9532d5d",
      month: "October",
      workerId: "7738",
      date: "2022-10-31T00:00:00.000Z",
    },
  },
]

CodePudding user response：

Assume that after the $lookup stage you will get this result:

[
  {
    _id: "60dd781c4524e6c116e2336d",
    workerFirstName: "MADASWAMY",
    workerSurname: "KARUPPASWAMY",
    workerId: "1002",
    
  },
  {
    _id: "60dd781d4524e6c116e234d4",
    workerFirstName: "AMIT",
    workerSurname: "SHAH",
    workerId: "1001",
    
  },
  {
    _id: "60dd781d4524e6c116e23642",
    workerFirstName: "DEVELOPER",
    workerSurname: "DEVELOPER",
    workerId: "7738",
    shifts: [
      {
        _id: "634d8d3ce596dd34c9532d7d",
        month: "October",
        workerId: "7738",
        date: "2022-10-01T00:00:00.000Z",
        
      },
      {
        _id: "634d8d3ce596dd34c9532d6d",
        month: "October",
        workerId: "7738",
        date: "2022-10-02T00:00:00.000Z",
        
      },
      {
        _id: "634d8d3ce596dd34c9532d5c",
        month: "October",
        workerId: "7738",
        date: "2022-10-31T00:00:00.000Z",
        
      }
    ]
  }
]

After the $lookup stage:

1. $set - Set shifts array by filtering the document via $filter.

2. $unwind - Deconstruct the shifts array.

results.users = await model.aggregate([
  // $match stage,
  // $lookup stage
  {
    $set: {
      shifts: {
        $filter: {
          input: "$shifts",
          cond: {
            $eq: [
              "$$this.date",
              new Date(formatTimeToIso(new Date()))
            ]
          }
        }
      }
    }
  },
  {
    $unwind: {
      path: "$shifts",
      preserveNullAndEmptyArrays: true
    }
  }
])