Mongoose 加入两个集合并仅从加入的集合中获取特定字段 [英] Mongoose join two collections and get only specific fields from the joined collection

问题描述

我在 mongoose 中加入两个集合时遇到问题.我有两个集合:学生和考试.

I have a problem joining two collections in mongoose. I have two collections namely: student and exams.

学生模型:

{
  fullname: { type: String, required: true },
  email: { type: String, required: true },
}

考试模式:

{
  test: { type: String, required: false },
  top10: [
    type: {
      studentId: { type: String, required: true },
      score: { type: Number, required: false },
    }
  ]
}

现在，我想通过 studentId 加入他们两个.结果应该是:

Now, I want to join them two by studentId. The result should be:

{
 "test": "Sample Test #1",
 "students": [
            {
                "studentId": "5f22ef443f17d8235332bbbe",
                "fullname": "John Smith",
                "score": 11
            },
            {
                "studentId": "5f281ad0838c6885856b6c01",
                "fullname": "Erlanie Jones",
                "score": 9
            },
            {
                "studentId": "5f64add93dc79c0d534a51d0",
                "fullname": "Krishna Kumar",
                "score": 5
            }
        ]
 }

我所做的是使用聚合:

 return await Exams.aggregate([
    {$lookup:
        {
            from: 'students',
            localField: 'top10.studentId',
            foreignField: '_id',
            as: 'students'
        }
    }
 ]);

但是这个结果并不是我所希望的.任何想法如何实现这一目标?我很乐意提供任何帮助.谢谢！

But this result is not what I had hoped it should be. Any ideas how to achieve this? I would gladly appreciate any help. Thanks!

推荐答案

你可以试试，

• $lookup 与 students 集合
• $project 显示必填字段，$map 迭代top10 数组的循环，在内部使用 $reduce 获取学生的全名和使用 $mergeObjects
与 top10 对象合并

• $lookup with students collection
• $project to show required fields, $map to iterate loop of top10 array and inside use $reduce to get fullname from students and merge with top10 object using $mergeObjects

db.exams.aggregate([
  {
    $lookup: {
      from: "students",
      localField: "top10.studentId",
      foreignField: "_id",
      as: "students"
    }
  },
  {
    $project: {
      test: 1,
      students: {
        $map: {
          input: "$top10",
          as: "top10",
          in: {
            $mergeObjects: [
              "$$top10",
              {
                fullname: {
                  $reduce: {
                    input: "$students",
                    initialValue: 0,
                    in: {
                      $cond: [
                        { $eq: ["$$this._id", "$$top10.studentId"] },
                        "$$this.fullname",
                        "$$value"
                      ]
                    }
                  }
                }
              }
            ]
          }
        }
      }
    }
  }
])

游乐场

第二个选项你可以在 $lookup 之前使用 $unwind，

Second option you can use $unwind before $lookup,

• $unwind 解构 top10 数组
• $lookup 与 students 集合
• $addFields 使用 $arrayElemtAt
将学生数组转换为对象
• $group 通过 _id 构造学生数组并推送必填字段

• $unwind deconstruct top10 array
• $lookup with students collection
• $addFields to convert students array to object using $arrayElemtAt
• $group by _id and construct students array and push required fields

db.exams.aggregate([
  { $unwind: "$top10" },
  {
    $lookup: {
      from: "students",
      localField: "top10.studentId",
      foreignField: "_id",
      as: "students"
    }
  },
  { $addFields: { students: { $arrayElemAt: ["$students", 0] } } },
  {
    $group: {
      _id: "$_id",
      test: { $first: "$test" },
      students: {
        $push: {
          studentId: "$top10.studentId",
          score: "$top10.score",
          fullname: "$students.fullname"
        }
      }
    }
  }
])

游乐场

这篇关于Mongoose 加入两个集合并仅从加入的集合中获取特定字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！