Mongodb aggregate unionWith operation (How do I integrate and merge?)

Question

Sample use case: Students are studying a for a certificate that requires the completion of five courses. The classes can be taken in any order. In the front end, I want to build a dashboard with complete information. The dashboard will show ALL classes offered, and a student's performance integrated to the display. I'm looking for a single array to build that dashboard.

I have a mongodb collection ('classlist) showing all five courses.

[
    {
        class_longname: "Underwater Volleyball",
        class_shortname: "uv",
        difficulty_1_10: 5,
        instructor_name: "Mr. Smith",
        special_needs: "snorkel and mask",
        duration: "20 hours"
    },
    {
        class_longname: "Yoga in the Mountains",
        class_shortname: "ym",
        difficulty_1_10: 7,
        instructor_name: "Ms. Walrus",
        special_needs: "yoga mat",
        duration: "12 hours"
    },
    {
        class_longname: "Roller skating disco dance party",
        class_shortname: "rs",
        difficulty_1_10: 4,
        instructor_name: "Ms. Pineapple",
        special_needs: "roller skates",
        duration: "10 hours"
    },
    {
        class_longname: "MongoDB database programming for beginners",
        class_shortname: "mb",
        difficulty_1_10: 4,
        instructor_name: "Mr. Smith",
        special_needs: "laptop",
        duration: "4 hours"
    },
    {
        class_longname: "Learn to Ride a Unicycle",
        class_shortname: "lu",
        difficulty_1_10: 7,
        instructor_name: "Ms. Apple",
        special_needs: "bring your own unicycle",
        duration: "15 hours"
    }
]

I have a MongoDb collection (course_progress) for all students:

[
    {
        student_name: "Tom R.",
        class_shortname: "ym",
        date_completed: ...,
        pass: true,
    },
    {
        student_name: "Tom R.",
        class_shortname: "uv",
        date_completed: ...,
        pass: true,
    },
    {
        student_name: "Betty S.",
        class_shortname: "mb",
        date_completed: ...,
        pass: true,
    },
    {
        student_name: "Betty S.",
        class_shortname: "rs",
        date_completed: ...,
        pass: false,
    },
    {
        student_name: "Betty S.",
        class_shortname: "lu",
        date_completed: ...,
        pass: true,
    },
]

What I want is an integrated class list for the logged in student of interest (Tom R.)

 [
    {
        class_longname: "Underwater Volleyball",
        class_shortname: "uv",
        difficulty_1_10: 4,
        instructor_name: "Mr. Smith",
        special_needs: "snorkel and mask",
        duration: "20 hours",
        student_name: "Tom R.",
        class_shortname: "uv",
        date_completed: ...,
        pass: true,
    },
    {
        class_longname: "Yoga in the Mountains",
        class_shortname: "ym",
        difficulty_1_10: 7,
        instructor_name: "Ms. Walrus",
        special_needs: "yoga mat",
        duration: "12 hours",
        student_name: "Tom R.",
        class_shortname: "ym",
        date_completed: ...,
        pass: true,
    },
    {
        class_longname: "Roller skating disco dance party",
        class_shortname: "rs",
        difficulty_1_10: 4,
        instructor_name: "Ms. Pineapple",
        special_needs: "roller skates",
        duration: "10 hours"
    },
    {
        class_longname: "MongoDB database programming for beginners",
        class_shortname: "mb",
        difficulty_1_10: 4,
        instructor_name: "Mr. Smith",
        special_needs: "laptop",
        duration: "4 hours"
    },
    {
        class_longname: "Learn to Ride a Unicycle",
        class_shortname: "lu",
        difficulty_1_10: 4,
        instructor_name: "Ms. apple",
        special_needs: "bring your own unicycle",
        duration: "15 hours"
    }
]

Right now my Mongodb code looks like:

const result = await db
     .collection('course_progress')
     .aggregate([
          { $match: { student_name: "Tom R." } },    // actually a variable name, etc.
          { $unionWith: {coll: "classlist" } },
     ])
     .toArray()

And this does "combine" the two arrays, kind of sort of, but not really. What I get is Tom's two course_progress record objects, followed by all five of the classes offered (for a total of seven objects in the array). The records on Tom's course_progress do not include class_longname, instructor_name, etc... Not sure how to merge, integrate and purge this data. I know I could make two round trips to the MongoDB server, get two result arrays, then do a forEach in the client to clean that up, but I was really hoping to do this in the DB in one operation.

Is this even possible in Mongodb?

Answer 1

you should use $lookup to join two docs

db.course_progress.aggregate([
  {
    $match: {
      student_name: "Tom R."
    }
  },
  {
    "$lookup": {
      "from": "classlist",
      "localField": "class_shortname",
      "foreignField": "class_shortname",
      "as": "classData"
    }
  },
  {
    "$unwind": "$classData"
  },
  {
    "$project": {
      class_longname: "$classData.class_longname",
      class_shortname: "1",
      difficulty_1_10: "$classData.difficulty_1_10",
      duration: "$classData.duration",
      instructor_name: "$classData.instructor_name",
      special_needs: "$classData.special_needs",
      date_completed: 1,
      pass: 1,
      student_name: 1
    }
  }
])

https://mongoplayground.net/p/hAkt1QsLbG0