In a relational database, we often use SQL JOIN operations to combine records from different tables. In MongoDB, $lookup provides similar functionality between collections.

Common uses include:

• Combining student records with course information.
• Displaying orders with customer details.
• Finding employees with department information.
• Combining library bookings with student records.

Basic syntax:

db.collection1.aggregate([
    {
        $lookup: {
            from: "collection2",
            localField: "fieldInFirstCollection",
            foreignField: "fieldInSecondCollection",
            as: "matchedDocuments"
        }
    }
])

Important properties:

• from: the collection to search for matching documents.
• localField: the field from the input collection.
• foreignField: the field from the other collection.
• as: the name of the array that stores matching documents.

We will create two collections: lookup_students and lookup_courses.

First, insert student records:

use school

db.lookup_students.insertMany([
    {
        student_id: 101,
        name: "Rahul",
        course_id: 1
    },
    {
        student_id: 102,
        name: "Priya",
        course_id: 2
    },
    {
        student_id: 103,
        name: "Aman",
        course_id: 1
    },
    {
        student_id: 104,
        name: "Neha",
        course_id: 3
    }
])

Next, insert course records:

db.lookup_courses.insertMany([
    {
        course_id: 1,
        course_name: "Java",
        fees: 5000
    },
    {
        course_id: 2,
        course_name: "Python",
        fees: 6000
    },
    {
        course_id: 3,
        course_name: "Web Development",
        fees: 7000
    }
])

Note: Run each insertMany() example only once to avoid duplicate sample records. These examples use separate collection names so they do not overwrite the earlier lessons' sample data.

Match the course_id in the student collection with the course_id in the course collection.

db.lookup_students.aggregate([
    {
        $lookup: {
            from: "lookup_courses",
            localField: "course_id",
            foreignField: "course_id",
            as: "courseDetails"
        }
    }
])

Each student document now contains a courseDetails array with the matching course information.

Example output for Rahul:

{
    student_id: 101,
    name: "Rahul",
    course_id: 1,
    courseDetails: [
        {
            course_id: 1,
            course_name: "Java",
            fees: 5000
        }
    ]
}

The actual output also contains MongoDB-generated _id fields for the inserted documents.

The as property defines the field that stores matching documents. In our example, that field is courseDetails.

• If a matching course exists, the array contains its document.
• If multiple documents match, the array can contain multiple documents.
• If no document matches, the array is empty.

By default, $lookup does not remove students that have no matching course. It returns those students with an empty courseDetails array.

Use $unwind to turn the matching course array into an individual course document, then use $project to select the required fields.

db.lookup_students.aggregate([
    {
        $lookup: {
            from: "lookup_courses",
            localField: "course_id",
            foreignField: "course_id",
            as: "courseDetails"
        }
    },
    {
        $unwind: "$courseDetails"
    },
    {
        $project: {
            _id: 0,
            student_id: 1,
            student_name: "$name",
            course_name: "$courseDetails.course_name",
            course_fees: "$courseDetails.fees"
        }
    }
])

Example result:

[
    {
        student_id: 101,
        student_name: "Rahul",
        course_name: "Java",
        course_fees: 5000
    },
    {
        student_id: 102,
        student_name: "Priya",
        course_name: "Python",
        course_fees: 6000
    }
]

The output above is abbreviated. The complete result includes all four sample students because each has a matching course.

Important: $unwind removes documents whose array is empty unless preserveNullAndEmptyArrays: true is specified.

If you want to retain students even when they have no matching course, use preserveNullAndEmptyArrays: true in $unwind.

db.lookup_students.aggregate([
    {
        $lookup: {
            from: "lookup_courses",
            localField: "course_id",
            foreignField: "course_id",
            as: "courseDetails"
        }
    },
    {
        $unwind: {
            path: "$courseDetails",
            preserveNullAndEmptyArrays: true
        }
    },
    {
        $project: {
            _id: 0,
            name: 1,
            course_name: "$courseDetails.course_name"
        }
    }
])

This is useful when the input collection contains documents without matching records in the other collection.

You can use $match after $lookup to filter documents based on fields in the matching array.

db.lookup_students.aggregate([
    {
        $lookup: {
            from: "lookup_courses",
            localField: "course_id",
            foreignField: "course_id",
            as: "courseDetails"
        }
    },
    {
        $match: {
            "courseDetails.course_name": "Java"
        }
    }
])

This returns students whose matching course is Java.

You can also filter on the original collection's fields, such as name or student_id.

The names of localField and foreignField do not need to be identical. Their values and data types must be compatible for matching.

For example, imagine a collection with these fields:

// In lookup_students
{
    student_id: 101,
    name: "Rahul",
    course_id: 1
}

// In lookup_courses
{
    course_code: 1,
    course_name: "Java",
    fees: 5000
}

The lookup would use:

db.lookup_students.aggregate([
    {
        $lookup: {
            from: "lookup_courses",
            localField: "course_id",
            foreignField: "course_code",
            as: "courseDetails"
        }
    }
])

Here, course_id is matched against course_code.

$lookup is useful when related information is stored in separate collections. For data that is usually accessed together, embedded documents may also be a suitable design choice.

Use the lookup_students and lookup_courses collections to complete these tasks:

1. Display every student with matching course details.
2. Display only the student name and course name.
3. Show students enrolled in Java.
4. Display each student's course fees.
5. Use $unwind to display course details as individual fields.
6. Keep students even when no matching course exists.
7. Try matching course_id against a differently named field in the course collection.