In MongoDB with two collection, trying to get a course with the "courseCode". In the course collection "assignedTutors" is a array of users._id in the course collection. Want to want the array of "assignedTutors" IDs to get their user info in the users collection.
Problem: With the current code I am not getting the user data in the users collection using the array of "assignedTutors" IDs. In the current example there are 3 user IDs in the "assignedTutors" array I would like to use aggregate to get the user data from the users collection.
If there is better way to do this i am open to that too.
course collection:
{
"_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 collection:
{
"_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"
}
}
current code:
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}`);
});
});
};
Current output:
{
"_id": {
"$oid": "66e765c04ce5539d7f7362c2"
},
"courseCode": "ABC123",
"courseName": "Test",
"courseImgUrl": "abc.png",
"coursePrice": "123",
"assignedTutors": ["66f9a09505e0ca3203870633", "66f9a07e05e0ca3203870630", "66f9a05005e0ca320387062d"],
"description": "XYZ.",
"published": true,
"school": "ABC school"
}
I want it to return a course and with the assignedTutors data on the users collection.
{
"_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"
}
The main issue here is that your assignedTutors array has strings instead of ObjectIDs. So the lookup matches Strings in users.assignedTutors with ObjectIDs in users._id.
If this is something you can change in your DB, then you should. Otherwise, you will need to convert it to before the lookup or using let+pipelines in the lookup. My example below has the strings converted before the lookup.
After that, the only change needed is in the projection: assignedTutors: { $arrayElemAt: ['$assignedTutors', 0] }.
Your second lookup has assignedTutors.tutorData so you need to apply the $arrayElemAt operator to that: assignedTutors: { $arrayElemAt: ["$assignedTutors.tutorData", 0] }.
However, it's unclear why you need that since you already have it in the first lookup and you said you wanted all the tutors.
Here's your current aggregation pipeline with only the above two errors fixed:
db.course.aggregate([
{ $match: { courseCode: courseCode } },
{
$set: {
assignedTutors: {
$map: {
input: "$assignedTutors",
in: { $toObjectId: "$$this" }
}
}
}
},
{
$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.tutorData", 0] }
}
}
])
To get the output as per your question, you only need part of this pipeline - till the first lookup with the ObjectID fix. Even the $project is not needed.
db.course.aggregate([
{ $match: { courseCode: courseCode } },
{
$set: {
assignedTutors: {
$map: {
input: "$assignedTutors",
in: { $toObjectId: "$$this" }
}
}
}
},
{
$lookup: {
from: "users",
localField: "assignedTutors",
foreignField: "_id",
as: "assignedTutors"
}
}
])