MongoDB中按courseCode关联课程与讲师用户数据的问题

问题说明 #

现有course和users两个集合，需要根据courseCode查询指定课程，并将course集合中assignedTutors字段的用户ID数组替换为users集合中对应的完整用户信息。当前使用的聚合查询未实现预期效果，需修正代码完成关联查询。

集合结构 #

course集合 #

{
    "_id": {
        "$oid": "66e765c04ce5539d7f7362c2"
    },
    "courseCode": "ACB123",
    "courseName": "ABC",
    "courseImgUrl": "https://ABC.png",
    "coursePrice": "123",
    "assignedTutors": ["66f9a09505e0ca3203870633", "66f9a07e05e0ca3203870630", "66f9a05005e0ca320387062d"],
    "description": "This course ABC",
    "published": true,
    "school": "ABC school"
}

users集合 #

{
    "_id": {
        "$oid": "66f9a09505e0ca3203870633"
    },
    "userName": "tutor3",
    "password": "xxx",
    "email": "tutor3@email.com",
    "userType": "tutor",
    "firstName": "Tutor",
    "lastName": "Three",
    "teachingCourse": ["66f9adbb05e0ca3203870732", "66e765c04ce5539d7f7362c2"],
    "certificates": [],
    "purchasedCourse": [],
    "__v": {
        "$numberInt": "0"
    }
}

当前实现代码 #

module.exports.getCourseByCode = function (courseCode) {
    return new Promise((resolve, reject) => {
      Course.aggregate([
        {
          $match: { courseCode: courseCode }
        },
        {
          $lookup: {
            from: 'users',
            localField: 'assignedTutors',
            foreignField: '_id',
            as: 'assignedTutors'
          }
        },
        {
          $unwind: '$assignedTutors'
        },
        {
          $lookup: {
            from: 'users',
            localField: 'assignedTutors._id',
            foreignField: '_id',
            as: 'assignedTutors.tutorData'
          }
        },
        {
          $project: {
            _id: 1,
            courseCode: 1,
            courseName: 1,
            courseImgUrl: 1,
            coursePrice: 1,
            school: 1,
            description: 1,
            published: 1,
            assignedTutors: { $arrayElemAt: ['$assignedTutors', 0] }
          }
        }
      ])
        .then((courses) => {
            console.log(courses)
          if (courses.length === 0) {
            reject(`Course with code ${courseCode} not found.`);
          } else {
            resolve(courses[0]);
          }
        })
        .catch((err) => {
          reject(`Error while retrieving course with code ${courseCode}: ${err.message}`);
        });
    });
};

当前输出结果 #

{
    "_id": {
        "$oid": "66e765c04ce5539d7f7362c2"
    },
    "courseCode": "ABC123",
    "courseName": "Test",
    "courseImgUrl": "abc.png",
    "coursePrice": "123",
    "assignedTutors": ["66f9a09505e0ca3203870633", "66f9a07e05e0ca3203870630", "66f9a05005e0ca320387062d"],
    "description": "XYZ.",
    "published": true,
    "school": "ABC school"
}

期望输出结果 #

{
    "_id": {
        "$oid": "66e765c04ce5539d7f7362c2"
    },
    "courseCode": "ABC123",
    "courseName": "Test",
    "courseImgUrl": "abc.png",
    "coursePrice": "123",
    "assignedTutors": [{
            "_id": {
                "$oid": "66f99b8005e0ca32038705f6"
            },
            "userName": "user1",
            "password": "zzz",
            "email": "user1@email.com",
            "userType": "xxx",
            "firstName": "userF",
            "lastName": "userL",
            "teachingCourse": [],
            "certificates": [],
            "purchasedCourse": [],
            "__v": {
                "$numberInt": "0"
            }
        }, {
            "_id": {
                "$oid": "66f99b8005e0ca32038705f6"
            },
            "userName": "user2",
            "password": "zzz",
            "email": "user2@email.com",
            "userType": "xxx",
            "firstName": "userF",
            "lastName": "userL",
            "teachingCourse": [],
            "certificates": [],
            "purchasedCourse": [],
            "__v": {
                "$numberInt": "0"
            }
        }, {
            "_id": {
                "$oid": "66f99b8005e0ca32038705f6"
            },
            "userName": "user3",
            "password": "zzz",
            "email": "user3@email.com",
            "userType": "xxx",
            "firstName": "userF",
            "lastName": "userL",
            "teachingCourse": [],
            "certificates": [],
            "purchasedCourse": [],
            "__v": {
                "$numberInt": "0"
            }
        }
    ],
    "description": "XYZ.",
    "published": true,
    "school": "ABC school"
}

修正方案与优化实现 #

问题分析 #

原代码存在以下冗余和错误：

1. 多余的$unwind操作会将数组拆分为多条文档，破坏原课程结构
2. 第二次$lookup属于重复查询，完全没必要
3. $project中的$arrayElemAt错误地将关联后的用户数组还原为单个元素，导致最终输出还是原ID数组

修正后的聚合查询代码 #

module.exports.getCourseByCode = function (courseCode) {
    return new Promise((resolve, reject) => {
        Course.aggregate([
            {
                $match: { courseCode: courseCode }
            },
            {
                $lookup: {
                    from: 'users',
                    localField: 'assignedTutors',
                    foreignField: '_id',
                    as: 'assignedTutors'
                }
            }
        ])
        .then((courses) => {
            if (courses.length === 0) {
                reject(`Course with code ${courseCode} not found.`);
            } else {
                resolve(courses[0]);
            }
        })
        .catch((err) => {
            reject(`Error while retrieving course with code ${courseCode}: ${err.message}`);
        });
    });
};

说明：MongoDB 3.2及以上版本的$lookup支持直接将数组类型的localField与foreignField匹配，自动返回匹配到的用户文档数组，无需额外处理。

更优实现：使用Mongoose的populate方法 #

如果项目基于Mongoose，可以利用其内置的关联引用功能，代码更简洁：

1. 先定义Course模型时，将assignedTutors指定为对User模型的引用：

const CourseSchema = new mongoose.Schema({
    courseCode: String,
    courseName: String,
    courseImgUrl: String,
    coursePrice: String,
    assignedTutors: [{ type: mongoose.Schema.Types.ObjectId, ref: 'User' }],
    description: String,
    published: Boolean,
    school: String
});

const Course = mongoose.model('Course', CourseSchema);

2. 然后直接使用findOne配合populate实现关联查询：

module.exports.getCourseByCode = async function (courseCode) {
    try {
        const course = await Course.findOne({ courseCode }).populate('assignedTutors');
        if (!course) {
            throw new Error(`Course with code ${courseCode} not found.`);
        }
        return course;
    } catch (err) {
        throw new Error(`Error while retrieving course with code ${courseCode}: ${err.message}`);
    }
};

说明：populate方法会自动将assignedTutors字段的ID数组替换为对应的User文档，代码更简洁易维护，适合大多数Mongoose场景。

内容的提问来源于stack exchange，提问作者Marco Siu