I have 3 collections User, Attendance, Detection

User collection Document

{
      "_id": "60dd781d4524e6c116e234d2",
      "workerFirstName": "AMIT",
      "workerSurname": "SHAH",
      "workerId": "1001",
      "locationName": "HEAD OFFICE",
      "workerDesignation": "IT",
      "workerDepartment": "IT",
    },

Attendance Document

{
        "_id": "61307cee85b5055a15cf01b7",
        "employeeId":"60dd781d4524e6c116e234d2",
        "Date": "2022-11-01T00:00:00.000Z",
        "duration": null,
        "createdAs": "FULL-DAY",
        "detections": [
          "636095dc00d9abc953d8fd57",
          "636132e6bf6fe52c582853b3"
        ]
      }

Detection Document

{
  "_id": "636095dc00d9abc953d8fd57",
  "AttendanceId": "61307cee85b5055a15cf01b7"
},
{
  "_id": "636132e6bf6fe52c582853b3",
  "AttendanceId": "61307cee85b5055a15cf01b7"
}

Getting all User $lookup to attendance and getting all the attendance related to a user.

And also there is a detection array inside the attendance object. I want to also populate that detection array which is inside the attendance object.

The query I Tried

    const dailyAttendance = await User.aggregate([
      { $sort: { workerId: 1 } },
      {
        $match: {
          lastLocationId: Mongoose.Types.ObjectId(locationId),
          workerType: workerType,
          isActive: true,
          workerId: {
            $nin: [
              "8080",
              "9999",
              "9998",
              "9997",
              "9996",
              "9995",
              "9994",
            ],
          },
        },
      },
      {
        $project: {
          _id: 1,
          workerId: 1,
          workerFirstName: 1,
          workerSurname: 1,
          workerDepartment: 1,
          workerDesignation: 1,
          locationName: 1,
        },
      },
      {
        $lookup: {
          from: "attendances",
          localField: "_id",
          foreignField: "employeeId",
          pipeline: [
            {
              $match: {
                Date: new Date(date),
              },
            },
            {
              $project: typesOfData,
            },
          ],
          as: "attendances",
        },
      },
      {
        $unwind: {
          path: "$attendances",
          preserveNullAndEmptyArrays: true,
        },
      },
    ])

Output I got

{
  "dailyAttendance": [
    {
      "_id": "60dd781d4524e6c116e234d2",
      "workerFirstName": "AMIT",
      "workerSurname": "SHAH",
      "workerId": "1001",
      "locationName": "HEAD OFFICE",
      "workerDesignation": "IT",
      "workerDepartment": "IT",
      "attendances": {
        "_id": "61307cee85b5055a15cf01b7",
        "Date": "2022-11-01T00:00:00.000Z",
        "duration": null,
        "createdAs": "FULL-DAY",
        "detections": [
          "636095dc00d9abc953d8fd57",
          "636132e6bf6fe52c582853b3"
        ]
      }
    },
    {
      "_id": "60dd781c4524e6c116e2336c",
      "workerFirstName": "MADASWAMY",
      "workerSurname": "KARUPPASWAMY",
      "workerId": "1002",
      "locationName": "HEAD OFFICE",
      "workerDesignation": "IT",
      "workerDepartment": "IT",
      "attendances": {
        "_id": "61307ce485b5055a15ceec02",
        "Date": "2022-11-01T00:00:00.000Z",
        "duration": null,
        "createdAs": "FULL-DAY",
        "detections": [
          "636095dc00d9abc953d8fd57",
          "636132e6bf6fe52c582853b3"
        ]
      }
    }
  ]
}

CodePudding user response：

You can try something like this:

db.user.aggregate([
  {
    "$lookup": {
      "from": "attendance",
      "let": {
        userId: "$_id"
      },
      "pipeline": [
        {
          "$match": {
            $expr: {
              "$eq": [
                "$$userId",
                "$employeeId"
              ]
            }
          }
        },
        {
          "$unwind": {
            path: "$detections",
            preserveNullAndEmptyArrays: true
          }
        },
        {
          "$lookup": {
            "from": "detection",
            "localField": "detections",
            "foreignField": "_id",
            "as": "detections"
          }
        },
        {
          "$unwind": {
            path: "$detections",
            preserveNullAndEmptyArrays: true
          }
        },
        {
          "$group": {
            "_id": {
              "Date": "$Date",
              "_id": "$_id",
              "createdAs": "$createdAs",
              "duration": "$duration",
              "employeeId": "$employeeId"
            },
            "detections": {
              "$push": "$detections"
            }
          }
        },
        {
          "$replaceRoot": {
            "newRoot": {
              "$mergeObjects": [
                "$_id",
                {
                  detections: "$detections"
                }
              ]
            }
          }
        }
      ],
      "as": "attendance"
    }
  }
])