Unable to display the course information for a student

Question

I am a newbie to using mongodb and I am attempting to write an aggregation pipeline using 2 collections student and courses. The student collection represents a students profile and course information. The student's course information has a courseId, subjectId and tutorId. This information is present in a courses collection but there is no shared foreign key between the student and courses collections. I am attempting to find the courses name and level from the courses collection.

There is no key between the student and courses collection but we are always provided with the following search criteria

Student's _id i.e. 99881 Course _id i.e. "761" it is a string The student's {courseId,subjectId,tutorId} matches an array element in the courses collection. These combinations are unique in the courses collection. The goal is to display the following information in the student card

firstName: Jessica
lastName: Determan
courseId: MATH101
courseName: Basic Mathematics
level: 1

Here are the collections

        students collection sample:

        [{
        "_id": "99881",
        "student": {
          "username": "jdeterman",
          "firstName": "Jessica",
          "lastName": "Determan",
          "fullName": "Jessica Determan",
          "courseId": "MATH101",
          "subjectId": 11622,
          "tutorId": 6744  
        }
      }]

Here is the courses collection:

     [{
      "_id": "761",
      "courseKeys": [
      {
        "courseId": "MATH101",
        "name": "Basic Mathematics",
        "subjectId": 11622,
        "tutorId": 6744,
        "level":1
      },
      {
        "courseId": "BIOSCI221",
        "name": "Biology 2",
        "subjectId": 16643,
        "tutorId": 6744,
        "level":2
      },
      {
        "courseId": "ALGEBRA1",
        "name": "Algebra 1",
        "subjectId": 17733,
        "tutorId": 2144,
        "level":1
      },
      {
        "courseId": "ALGEBRA2",
        "name": "Algebra 2",
        "subjectId": 91100,
        "tutorId": 3322,
        "level":2
       }
     ] 
  }]

Here is my attempt but stuck

  db.getCollection("students").aggregate([
    {
        "$match": {
            "_id": 761,
        }
    },
    {
    $unionWith: {
      coll: "courses",
      pipeline: [
        {
          $match: {
            "courses.courseId": "$student.courseId",
            "courses.tutorId": "$student.tutorId",
            "courses.level": "$student.level"
          }
        }
      ]      
    }
  }       
  ])

I would appreciate some help in solving this. Thank you.

Answer 1

Despite the good advice and conclusions made by others in the comments it sounds like you are not in a position to refactor your design and need a query based on the current schema.

This query will firstly do the $match on the courses collection, and then it will $unwind each courseKeys. That temporarily creates a document for every element in the courseKeys array but with each one sharing the "_id": "761" key.

After that you can $lookup the students collection looking for a match of courseKeys.subjectId equal to the student.subjectId. $lookup natively creates an array but in this case all of the unwound documents that don't have a match will be left with an empty array. That is very important becuase when you add another $unwind stage a side effect is that:

$unwind does not output a document if the field value is null, missing, or an empty array.

therefore in theory leaving you with a single match per document (if your design is as you say it is).

The last stage is a $project which is purely to reshape the output documents seasoned to your taste.

db.courses.aggregate([
  {
    $match: {
      _id: "761"
    }
  },
  {
    $unwind: "$courseKeys"
  },
  {
    $lookup: {
      from: "students",
      localField: "courseKeys.subjectId",
      foreignField: "student.subjectId",
      as: "students"
    }
  },
  {
    $unwind: "$students"
  },
  {
    $project: {
      _id: 0,
      firstName: "$students.student.firstName",
      lastName: "$students.student.lastName",
      courseId: "$courseKeys.courseId",
      courseName: "$courseKeys.name",
      level: "$courseKeys.level"
    }
  }
])

See HERE for a working example.