Yesterday’s entry shows that you can return specific field from lookup documents. However, the resulting scores is an array of objects, with each object having the only property you specified. In our example, if you only want to return an array of scores, you can transform the result set from the previous lookup aggregation stage. Below, I used $set to transform the scores property by selecting a specific value from each element: $scores.score.
// test data
db.getCollection("students").insertMany([
{ student_id: 1, name: "Student1", class: "A" },
{ student_id: 2, name: "Student2", class: "A" },
{ student_id: 3, name: "Student3", class: "B" },
{ student_id: 4, name: "Student4", class: "B" },
]);
db.getCollection("exams").insertMany([
{ student_id: 1, exam_type: "midterm", score: 88 },
{ student_id: 1, exam_type: "final", score: 92 },
{ student_id: 2, exam_type: "midterm", score: 73 },
{ student_id: 3, exam_type: "midterm", score: 80 },
{ student_id: 3, exam_type: "final", score: 85 },
{ student_id: 4, exam_type: "final", score: 61 },
]);
db.students.aggregate([
{
$lookup: {
from: "exams",
let: { sid: "$student_id" },
pipeline: [
{ $match: { $expr: { $eq: ["$student_id", "$$sid"] } } },
{ $project: { _id: 0, score: 1 } },
],
as: "scores",
},
},
{ $set: { scores: "$scores.score" } }, // [88,92] instead of [{score:88},{score:92}]
{ $sort: { student_id: 1 } },
]);Output:
[
{
"_id": {
"$oid": "69a98941413dc80c6f216856"
},
"student_id": 1,
"name": "Student1",
"class": "A",
"scores": [88, 92]
},
{
"_id": {
"$oid": "69a98941413dc80c6f216857"
},
"student_id": 2,
"name": "Student2",
"class": "A",
"scores": [73]
},
{
"_id": {
"$oid": "69a98941413dc80c6f216858"
},
"student_id": 3,
"name": "Student3",
"class": "B",
"scores": [80, 85]
},
{
"_id": {
"$oid": "69a98941413dc80c6f216859"
},
"student_id": 4,
"name": "Student4",
"class": "B",
"scores": [61]
}
]