0

I was trying to join multiple collections in MongoDB using the aggregate $lookup.The issue I am facing right now $lookup localField attribute does not accept $ character.

I have following three collections.

student

{
    "_id" : ObjectId("5db12e6dfc368dff1cfc30e5"),
    "studentId" : "S97YAREA51",
    "name" : "Has Maara duwa",
    "age" : 22
}

course

{
    "_id" : ObjectId("5db12e6dfc368dff1cfc20e5"),
    "courseId" : "C04865690",
    "courseName" : "Love and Empathy"
}

studentCourse

{
    "_id" : ObjectId("5db12e6dfc368dff1cfc10e5"),
    "student" : {
        "$ref" : "student",
        "$id" : ObjectId("5db12e6dfc368dff1cfc30e5")
    },
    "course" : {
        "$ref" : "course",
        "$id" : ObjectId("5db12e6dfc368dff1cfc20e5")
    }
}

I need to update studentCourse collection records to something like below.

{
    "_id" : ObjectId("5db12e6dfc368dff1cfc10e5"),
    "student" : {
        "$ref" : "student",
        "$id" : ObjectId("5db12e6dfc368dff1cfc30e5")
    },
    "course" : {
        "$ref" : "course",
        "$id" : ObjectId("5db12e6dfc368dff1cfc20e5")
    },
    "studentId" : "S97YAREA51",
    "courseId" : "C04865690"
}

I tried to create the following aggregation query, but it was failing due to localField does not accept $ character. I would be much appreciated if somebody provides me a simple solution for this matter. 

db.studentCourse.aggregate([

    {
        $lookup:{
            from: "student",
            localField: "student.$id",
            foreignField: "_id",
            as: "student"
        }
    },

    {   $unwind:"$student" },     // $unwind used for getting data in object or for one record only

    {
        $lookup:{
            from: "course", 
            localField: "course.$id", 
            foreignField: "_id",
            as: "course"
        }
    },

    {   $unwind:"$course" },

    // define which fields are you want to fetch
    {   
        $project:{
            "_id" : 1,
            "student" : 1,
            "course" : 1,
            "studentId" : "$student.studentId",
            "courseId" : "$course.courseId",
        } 
    }
]);
sampathlk
  • 338
  • 2
  • 17
  • Hi @SampathT - you are using DBRef with $lookup. The MongoDB Documentation states `Unless you have a compelling reason to use DBRefs, use manual references instead.` (https://docs.mongodb.com/manual/reference/database-references/). Here is a link to another SO article that describes what you are faced with - https://stackoverflow.com/questions/40622714/mongo-how-to-lookup-with-dbref. – barrypicker Oct 29 '19 at 22:41
  • My recommendation is to restructure your documents. Instead of using MongoDB like a RDBMS use it as a document store. Have two collections - 'Courses' and 'Students'. In the 'Students' collection embed documents describing their courses. – barrypicker Oct 29 '19 at 22:41

0 Answers0