$lookup on ObjectId's in an array and project it to same array

Question

I have a class schema as below

const AttendanceSchema = new mongoose.Schema({
    date: Date,
    records: [{
        studentId: {
            type: mongoose.Schema.Types.ObjectId,
            ref: 'User'
        },
        attendance: Boolean
    }]
})

var ClassSchema = new mongoose.Schema({
    title: String,
    description: String,
    attendances: [AttendanceSchema]
});

Here I have attendance object to maintain attendance of class students. So each and everyday, whenever we take attendance from class, we can $push a object with date and attendance record details. One Class object will look like this

{
    title: "Class Name",
    description: "Description",
    attendances: [
    {
        _id: "60ed395fb06f1254b0771ce1",
        date: "2021-07-13T06:57:00.000+00:00",
        records: [
            {
                attendance: true,
                _id: "60ed395fb06f1254b0771ce2",
                studentId: "60e6c440c133a9b590f510d5"
            },
            {
                attendance: false,
                _id: "60ed395fb06f1254b0771ce3",
                studentId: "60e6c4cb7be1ce4b647c546f"
            }
        ]
    },
    {
        _id: "60ed395fb06f1254b0771ce4",
        date: "2021-07-14T06:57:00.000+00:00"
        records: [
            {
                attendance: true,
                _id: "60ed395fb06f1254b0771ce5",
                studentId: "60e6c440c133a9b590f510d5"
            },
            {
                attendance: true,
                _id: "60ed395fb06f1254b0771ce6",
                studentId: "60e6c4cb7be1ce4b647c546f"
            }
        ]
    }
    ]
}

Here you can see each record object contains a object with object id. I want to get the students details with the same format Desired output

title: "Class Name"
description: "Description"
attendances: [
   {
      _id: 60ed395fb06f1254b0771ce1
      date: 2021-07-13T06:57:00.000+00:00
      records: [
         {
            attendance: true
            _id: 60ed395fb06f1254b0771ce2
            studentId: 60e6c440c133a9b590f510d5
            _studentDetail: {
               name: "Name"
               detail: ...
            }
         },
         {
            attendance: false
            _id: 60ed395fb06f1254b0771ce3
            studentId: 60e6c4cb7be1ce4b647c546f
         }
      ]
   },
   ...
]

If I use $lookup, I can only get the student detail in separate object. How can I get it?

Does this answer your question: https://stackoverflow.com/questions/60740560/mongodb-lookup-array-of-elements-with-combined-result/60742052#60742052 ? — mickl, Jul 13 '21 at 08:13
No, In my case local field is in an array inside an array. In that example, you can imagine context object as array. — Pranavan, Jul 13 '21 at 09:22
It is the same pattern just more nested levels, see my answer — mickl, Jul 13 '21 at 09:38

score 1 · Accepted Answer · answered Jul 13 '21 at 09:37

Since your data structure is deeply nested you need to extract all your studentIds before running $lookup. The $reduce operator along with $addFields can be used for it.

Once you get all the students from second collection you have two arrays in each document: students and attendances. Then you need to merge them by running multiple $map operators along with $mergeObjects and $filter to get matching student for each record:

db.Attendance.aggregate([
    {
        $addFields: {
            students: {
                $reduce: {
                    input: "$attendances",
                    initialValue: [],
                    in: { $setUnion: [ "$$value", "$$this.records.studentId" ] }
                }
            }
        }
    },
    {
        $lookup: {
            from: "Students",
            localField: "students",
            foreignField: "_id",
            as: "students"
        }
    },
    {
        $project: {
            title: 1,
            description: 1,
            attendances: {
                $map: {
                    input: "$attendances",
                    as: "a",
                    in: {
                        $mergeObjects: [
                            "$$a",
                            {
                                records: {
                                    $map: {
                                        input: "$$a.records",
                                        as: "r",
                                        in: {
                                            attendance: "$$r.attendance",
                                            _id: "$$r._id",
                                            student: {
                                                $arrayElemAt: [
                                                    { $filter: { input: "$students", cond: { $eq: [ "$$this._id", "$$r.studentId" ] } } }
                                                    , 0
                                                ]
                                            }
                                        }
                                    }
                                }
                            }
                        ]
                    }
                }
            }
        }
    }
])

Mongo Playground

$lookup on ObjectId's in an array and project it to same array

1 Answers1