Select field from lookup result

One issue with yesterday’s entry is that, if you only need the score, you don’t need the entire exam document properties returned. The demo below shows how to do it. Yesterday, the aggregation runs through all student documents, and for each student, it grabs the exams related to a student and attaches it to the student in the result. In the example below, for each student, its identifier (student_id) is stored as sid (which is referenced as $$sid in another pipeline) in let. let allows you to define temporary variables. The stage then runs a sub-pipeline defined in pipeline that contains two stages. The first stage, $match, retrieves all exam documents that matches the field student_id with the current student $$sid. The second stage transforms the result from $match by excluding the default field (_id) and including the score in the result stored in scores. scores is a property attached to each student document.

// 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",
		},
	},
	{ $sort: { student_id: 1 } },
]);

Output:

[
	{
		"_id": {
			"$oid": "69a9869230d936b5fcd92500"
		},
		"student_id": 1,
		"name": "Student1",
		"class": "A",
		"scores": [
			{
				"score": 88
			},
			{
				"score": 92
			}
		]
	},
	{
		"_id": {
			"$oid": "69a9869230d936b5fcd92501"
		},
		"student_id": 2,
		"name": "Student2",
		"class": "A",
		"scores": [
			{
				"score": 73
			}
		]
	},
	{
		"_id": {
			"$oid": "69a9869230d936b5fcd92502"
		},
		"student_id": 3,
		"name": "Student3",
		"class": "B",
		"scores": [
			{
				"score": 80
			},
			{
				"score": 85
			}
		]
	},
	{
		"_id": {
			"$oid": "69a9869230d936b5fcd92503"
		},
		"student_id": 4,
		"name": "Student4",
		"class": "B",
		"scores": [
			{
				"score": 61
			}
		]
	}
]