Question 116222 in Mongodb Question posted in
The official documentation can be found here.

MongoDB : – $lookup array inside object

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

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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
    enter image description here
    enter image description here
    enter image description here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search