Join collections with lookup

You can use $lookup aggregation stage to look up documents from another collection. In the example below, it would look like this join statement in SQL: students.student_id=exams.student_id. The aggregation stage references exams collection and looks up documents where students collection’s student_id is equal to exams collection’s student_id. The matching exams documents will appear as array elements under exams.

// 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.getCollection("students").aggregate([
	{
		$lookup: {
			from: "exams",
			localField: "student_id",
			foreignField: "student_id",
			as: "exams",
		},
	},
	{ $sort: { student_id: 1 } },
]);

Output:

[
	{
		"_id": {
			"$oid": "69a981bfc9175ba603204dfd"
		},
		"student_id": 1,
		"name": "Student1",
		"class": "A",
		"exams": [
			{
				"_id": {
					"$oid": "69a981bfc9175ba603204e01"
				},
				"student_id": 1,
				"exam_type": "midterm",
				"score": 88
			},
			{
				"_id": {
					"$oid": "69a981bfc9175ba603204e02"
				},
				"student_id": 1,
				"exam_type": "final",
				"score": 92
			}
		]
	},
	{
		"_id": {
			"$oid": "69a981bfc9175ba603204dfe"
		},
		"student_id": 2,
		"name": "Student2",
		"class": "A",
		"exams": [
			{
				"_id": {
					"$oid": "69a981bfc9175ba603204e03"
				},
				"student_id": 2,
				"exam_type": "midterm",
				"score": 73
			}
		]
	},
	{
		"_id": {
			"$oid": "69a981bfc9175ba603204dff"
		},
		"student_id": 3,
		"name": "Student3",
		"class": "B",
		"exams": [
			{
				"_id": {
					"$oid": "69a981bfc9175ba603204e04"
				},
				"student_id": 3,
				"exam_type": "midterm",
				"score": 80
			},
			{
				"_id": {
					"$oid": "69a981bfc9175ba603204e05"
				},
				"student_id": 3,
				"exam_type": "final",
				"score": 85
			}
		]
	},
	{
		"_id": {
			"$oid": "69a981bfc9175ba603204e00"
		},
		"student_id": 4,
		"name": "Student4",
		"class": "B",
		"exams": [
			{
				"_id": {
					"$oid": "69a981bfc9175ba603204e06"
				},
				"student_id": 4,
				"exam_type": "final",
				"score": 61
			}
		]
	}
]