MongoDB : - $lookup array inside object

Question

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"
        ]
      }
    }
  ]
}

@AbbasShaikh user - { "_id":"random_id", "workerId":"1", "workerFirstName":"randomName" } attendance - { "_id":"random_id", "workerId":"1", "employeeId":"user_id", "detections":["detectionId1","detectionId2"] } detection - { "_id":"random_id", } — chirag prajapati
– chirag prajapati, Commented Nov 15, 2022 at 8:25
Please add some documents from all the three collections, and your desired output as well. — Charchit Kapoor
– Charchit Kapoor, Commented Nov 15, 2022 at 9:29

Ali Iqbal · Accepted Answer · 2022-11-15 12:07:36Z

You have to unwind the $attandances.detections first then lookup from the detections document with attandences.detections as locafield and _id as the foreignField

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,
                },
            },
            {
                '$unwind': {
                 path: '$attandances.detections', 
                 preserveNullAndEmptyArrays: true
                }
            },
            {
                $lookup: {
                    from: "detections",
                    localField: "attandances.detections",
                    foreignField: "_id",
                    as: "occurances",
                },
            },
        ])

You will get the following output

Charchit Kapoor · Accepted Answer · 2022-11-15 11:48:22Z

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"
    }
  }
])

Playground link.

Collectives™ on Stack Overflow

MongoDB : - $lookup array inside object

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related