I am a new to 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.

I am a new to 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.

• mongodb
• aggregation-framework

• You can use virtually the same pattern as in this answer to your other question. – aneroid Commented Feb 5 at 20:57
• That solution will not work since there is no key in that collection. I am only given these 2 fields, student _id and courses _id, nothing more. $lookup would need from $courses which does not have a key in the students collection. How do you get to courses.course keys? – BreenDeen Commented Feb 5 at 21:03
• The student's subjectId is present in courses.subjectId. Use that for the lookup. You can even use student's courseId with courses.courseId. If there truly was nothing in common, it would be impossible to do any kind of join/merge with the correct record. – aneroid Commented Feb 5 at 21:04
• Isn't from supposed to be from the collection courses? – BreenDeen Commented Feb 5 at 21:09
• 2 This design looks odd. The student docs should have name, tutorid, and an array of courseIDs. Each course doc should have just courseID and level and such. The $lookup from student to course would be straightforward. – Buzz Moschetti Commented Feb 5 at 21:47

2 Answers 2

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.

There is no key between the student and courses collection

It seems student.courseId is the key, yes?

Assuming it is, the pipeline could be like this:

db.students.aggregate([
  {
    "$match": {
      "_id": "99881"
    }
  },
  {
    "$lookup": {
      "from": "courses",
      "let": {
        "courseId": "$student.courseId"
      },
      "pipeline": [
        {
          "$match": {
            "courseKeys.courseId": "$$courseId"
          }
        },
        {
          "$unwind": "$courseKeys"
        },
        {
          "$match": {
            "courseKeys.courseId": "$$courseId"
          }
        },
        {
          "$project": {
            "courseName": "$courseKeys.name",
            "courseLevel": "$courseKeys.level"
          }
        }
      ],
      "as": "courses"
    }
  }
])

I'm only working on retrieving the data

It's not how things work in Mongo. It's NoSQL database, means SQL patterns do not apply here. The approach of collecting data concerning only about DNF forms, and letting other people to think about how to extract it belongs to SQL universe. It's a good battle-tested way to deal with data backed up by 100+ years of theory. The flip side is it's exponentially expensive to handle big data.

This is where you can benefit from MongoDB - it scales, but the price is you need to think about what data to extract beforehand. Queries should drive the schema design, otherwise you will get worse performance compared to SQL + overhead of maintenance cost.

This design looks odd.

Agree with Buzz on that. This schema restricts students to a single course. I don't see how it could happen in a real life school/college