Mongodb聚合unionWith操作(如何集成合并？)

Question

示例用例:学生正在学习一个证书，该证书要求完成五门课程。这些类可以按任何顺序进行。在前端，我想构建一个包含完整信息的仪表板。仪表板将显示所提供的所有课程，并将学生的表现集成到显示器中。我正在寻找单个数组来构建仪表板。

我有一个mongodb集合('classlist)，显示了所有五门课程。

[
    {
        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"
    }
]

我有一个供所有学生使用的MongoDb集合(course_progress)：

[
    {
        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,
    },
]

我想要的是为登录的感兴趣的学生(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"
    }
]

现在我的Mongodb代码看起来像这样：

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

这确实将两个数组“组合”在某种程度上，但并不是真的。我得到的是Tom的两个course_progress record对象，后面是提供的所有五个类(数组中总共有七个对象)。汤姆的course_progress上的记录不包括class_longname，instructor_name等。不确定如何合并、集成和清除此数据。我知道我可以对MongoDB服务器做两次往返，得到两个结果数组，然后在客户端执行一次forEach来清理它，但我真的希望在数据库中只需一次操作就能做到这一点。

这在Mongodb中是可能的吗？

Answer 1

您应该使用$lookup连接两个文档

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