How to aggregate array of ObjectId pairs with their relevant collection

Question

I have a course collection in which I am allotting teachers for each subject of that course. The allotment is saved as an array of JSON please take a look at the reference doc below.

{
   "_id" : ObjectId("5cc7d72d8e165005cbef939e"),
   "isAssigned" : true,
   "name" : "11",
   "section" : "A",
   "allotment" : [
       {
           "subject" : ObjectId("5cc3f7cc88e95a0c8e8ccd7d"),
           "teacher" : ObjectId("5cbee0e37a3c852868ec9797")
       },
       {
           "subject" : ObjectId("5cc3f80e88e95a0c8e8ccd7e"),
           "teacher" : ObjectId("5cbee10c7a3c852868ec9798")
       }
   ]
}

I am trying to match the subject and teacher fields along with their doc from two different collections. I could get them in two different array's but couldn't get them as structured in my expected output

Doc in teachers collection

{
 _id: ObjectId("5cbee0e37a3c852868ec9797"),
 name: "Alister"
}

Doc in subject

{
 _id: ObjectId("5cc3f7cc88e95a0c8e8ccd7d"),
 name: "English",
 code: "EN"
}

Query I tried

Course.aggregate([
               {"$match": matchQuery},
               {"$lookup": {
                   "from": "subjects",
                   "localField": "allotment.subject",
                   "foreignField": "_id",
                   "as": "subjectInfo"
                   }
               },
               {"$lookup": {
                   "from": "teachers",
                   "localField": "allotment.teacher",
                   "foreignField": "_id",
                   "as": "teacherInfo"}
               },
               ])

Output of that Query

{
 isAssigned: true
 name: "11"
 section: "A"
 subjectInfo:[
  {_id: "5cc3f7cc88e95a0c8e8ccd7d", name:"English", code:"EN"}
  {_id: "5cc3f80e88e95a0c8e8ccd7e", name: "Science", code:"SC"}
 ]
 teacherInfo:[
  {_id: ObjectId("5cbee0e37a3c852868ec9797"),name: "Alister"},
  { _id: ObjectId("5cbee10c7a3c852868ec9798"),name: "Frank"}
 ]
}

Expexted output

{
   "_id" : ObjectId("5cc7d72d8e165005cbef939e"),
   "isAssigned" : true,
   "name" : "11",
   "section" : "A",
   "allotment" : [
       {
           "subject" : {
             _id: ObjectId("5cc3f7cc88e95a0c8e8ccd7d"),
             name: "English",
             code: "EN"
           }
           "teacher" : {
              _id: ObjectId("5cbee0e37a3c852868ec9797"),
              name: "Alister"
           }
       },
       {
           "subject" : {
             _id: ObjectId("5cc3f80e88e95a0c8e8ccd7e"),
             name: "Science",
             code: "SC"
           }
           "teacher" : {
              _id: ObjectId("5cbee10c7a3c852868ec9798"),
              name: "Frank"
           }
       }
   ]
}

Use mongoose populate options, this link might be useful for you https://stackoverflow.com/questions/19222520/populate-nested-array-in-mongoose — Amol B Jamkar, Apr 30 '19 at 06:44

score 2 · Answer 1 · answered Apr 30 '19 at 07:20

Just unwind the array before the lookups:

Course.aggregate([
               {"$match": matchQuery},
               {"$unwind: "$allotment"}
               {"$lookup": {
                   "from": "subjects",
                   "localField": "allotment.subject",
                   "foreignField": "_id",
                   "as": "subjectInfo"
                   }
               },
               {"$lookup": {
                   "from": "teachers",
                   "localField": "allotment.teacher",
                   "foreignField": "_id",
                   "as": "teacherInfo"}
               },
               ])

if you want to re-group after that to restore expected format you can add:

{ $group : { 
         _id: "$_id",
         name: {$first: "$name"},
         section: {$first: "$section},
         isAssigned: {$first: "$isAssigned},
         allotment: {$push: {teacher: "$teacherInfo.0", subject: "$subjectInfo.0"}}

I'm assuming teacherInfo and subjectInfo are never empty, if this is not the case you should add a $match to filter empty ones.

Tomáš Linhart · Answer 2 · 2019-04-30T07:25:14.597

Take a look at $lookup aggregation stage which lets you join collections. There's a plenty of examples on the usage in the documentation.

EDIT: Here's the complete pipeline that should provide the expected result:

courses.aggregate(
    [
        { 
            "$unwind" : {
                "path" : "$allotment"
            }
        }, 
        { 
            "$lookup" : {
                "from" : "subjects", 
                "localField" : "allotment.subject", 
                "foreignField" : "_id", 
                "as" : "allotment.subject"
            }
        }, 
        { 
            "$lookup" : {
                "from" : "teachers", 
                "localField" : "allotment.teacher", 
                "foreignField" : "_id", 
                "as" : "allotment.teacher"
            }
        }, 
        { 
            "$addFields" : {
                "allotment.subject" : {
                    "$arrayElemAt" : [
                        "$allotment.subject", 
                        0.0
                    ]
                }, 
                "allotment.teacher" : {
                    "$arrayElemAt" : [
                        "$allotment.teacher", 
                        0.0
                    ]
                }
            }
        }, 
        { 
            "$group" : {
                "_id" : "$_id", 
                "isAssigned" : {
                    "$first" : "$isAssigned"
                }, 
                "name" : {
                    "$first" : "$name"
                }, 
                "section" : {
                    "$first" : "$section"
                }, 
                "allotment" : {
                    "$addToSet" : "$allotment"
                }
            }
        }
    ]
)

OK, ideally, provide an example document from each collection and the expected result. Then, we can try to provide a solution. — Tomáš Linhart, Apr 30 '19 at 06:39
Have edited my question and provided a detailed insight please review it — naveen ashok, Apr 30 '19 at 06:56

Vikash_Singh · Accepted Answer · 2019-04-30T07:43:32.990

Firstly you have to $unwind the allotment array and then apply $lookup for subject and then repeat same for teachers and finally apply $group to combine back it inside array. See below aggregate query that is have tried and its working for me.

    Course.aggregate([
        {"$match": matchQuery},
        {
        $unwind: '$allotment'  
        },
        {
            $lookup:{
            "from": "subjects",
            "localField": "allotment.subject",
            "foreignField": "_id",
            "as": "allotment.subject"
            }
        },
        {
        $unwind: '$allotment.subject'  
        },
        {
            "$lookup": {
            "from": "teachers",
            "localField": "allotment.teacher",
            "foreignField": "_id",
            "as": "allotment.teacher"
            }
        },
        {
        $unwind: '$allotment.teacher'  
        },
        { 
                "$group" : {
                    "_id" : "$_id", 
                    "isAssigned" : {
                        "$first" : "$isAssigned"
                    }, 
                    "name" : {
                        "$first" : "$name"
                    }, 
                    "section" : {
                        "$first" : "$section"
                    }, 
                    "allotment" : {
                        "$addToSet" : "$allotment"
                    }
                }
            }
    ])

How to aggregate array of ObjectId pairs with their relevant collection

3 Answers3