- In a many-to-many relationship in MongoDB, a document in one collection can have many related documents in another collection, and vice versa. This is typically modeled using referencing in both directions, where each document holds references (IDs) to related documents from the other collection.
- Each student can enroll in many courses, and each course can have many students. This creates a many-to-many relationship between students and courses.
- To achieve this, we’ll use referencing by storing student IDs in the courses collection and course IDs in the students collection.
- First, switch to the desired database (or create one if it doesn't exist):
use schoolDB
Step 2: Insert Sample Data (Referencing)
- Insert students into the students collection:
db.students.insertMany([
{
_id: 1,
name: "John Doe",
age: 20,
course_ids: [101, 102] // List of course references
},
{
_id: 2,
name: "Jane Smith",
age: 22,
course_ids: [101, 103] // List of course references
}
])
- Insert courses into the courses collection:
db.courses.insertMany([
{
_id: 101,
title: "Database Systems",
instructor: "Prof. A",
student_ids: [1, 2] // List of student references
},
{
_id: 102,
title: "Operating Systems",
instructor: "Prof. B",
student_ids: [1] // List of student references
},
{
_id: 103,
title: "Algorithms",
instructor: "Prof. C",
student_ids: [2] // List of student references
}
])
Explanation of Data:
- Each student document holds an array course_ids, which references the courses they are enrolled in.
- Each course document holds an array student_ids, which references the students enrolled in that course.
- Query 1: Find a Student and the Courses They Are Enrolled In
- Find the student:
var student = db.students.findOne({ _id: 1 })
- Find the courses using the course_ids:
db.courses.find({ _id: { $in: student.course_ids } })
// Output:
[
{
"_id": 101,
"title": "Database Systems",
"instructor": "Prof. A",
"student_ids": [1, 2]
},
{
"_id": 102,
"title": "Operating Systems",
"instructor": "Prof. B",
"student_ids": [1]
}
]
- Query 2: Find a Course and the Students Enrolled in It
- Find the course:
var course = db.courses.findOne({ _id: 101 })
- Find the students using the student_ids:
db.students.find({ _id: { $in: course.student_ids } })
// Output:
[
{
"_id": 1,
"name": "John Doe",
"age": 20,
"course_ids": [101, 102]
},
{
"_id": 2,
"name": "Jane Smith",
"age": 22,
"course_ids": [101, 103]
}
]
Step 4: Using $lookup to Perform Joins in Aggregation
- MongoDB’s $lookup aggregation can be used to perform a join between the students and courses collections in a single query.
- Example 1: Find a Student and Join Their Courses
db.students.aggregate([
{
$lookup: {
from: "courses", // Collection to join with
localField: "course_ids", // Field in students collection
foreignField: "_id", // Field in courses collection
as: "courses" // Output array field name
}
},
{ $match: { _id: 1 } } // Find a specific student (John Doe)
])
// Output:
[
{
"_id": 1,
"name": "John Doe",
"age": 20,
"course_ids": [101, 102],
"courses": [
{
"_id": 101,
"title": "Database Systems",
"instructor": "Prof. A",
"student_ids": [1, 2]
},
{
"_id": 102,
"title": "Operating Systems",
"instructor": "Prof. B",
"student_ids": [1]
}
]
}
]
- Example 2: Find a Course and Join the Students Enrolled in It
db.courses.aggregate([
{
$lookup: {
from: "students", // Collection to join with
localField: "student_ids", // Field in courses collection
foreignField: "_id", // Field in students collection
as: "students" // Output array field name
}
},
{ $match: { _id: 101 } } // Find a specific course (Database Systems)
])
// Output:
[
{
"_id": 101,
"title": "Database Systems",
"instructor": "Prof. A",
"student_ids": [1, 2],
"students": [
{
"_id": 1,
"name": "John Doe",
"age": 20,
"course_ids": [101, 102]
},
{
"_id": 2,
"name": "Jane Smith",
"age": 22,
"course_ids": [101, 103]
}
]
}
]
Explanation of the Referencing Approach:
- Referencing is the preferred approach in a many-to-many relationship. Instead of embedding arrays that may grow indefinitely, you store references to related documents (IDs).
- This approach allows documents in both collections to grow independently and stay manageable even if the relationships become complex.
- Scalability: Both students and courses can have an unlimited number of relationships without causing the documents to grow too large.
- Avoids Duplication: Since courses and students are stored in separate collections, you avoid duplicating data.
- Flexible Relationships: The same course can be linked to many students, and the same student can be linked to many courses.
- Multiple Queries: Retrieving related data requires multiple queries or the use of $lookup aggregation.
- Data Consistency: If not managed carefully, it’s possible for an entity (student or course) to reference another entity that doesn’t exist.
- Create Two Collections: In this example, we created students and courses collections.
- Use Referencing: Store the references to related documents (IDs) in arrays (e.g., course_ids in students and student_ids in courses).
- Perform Queries: Use the _id arrays to query related collections and retrieve the associated data.
- Use $lookup for Joins: To combine data from two collections in a single query, use MongoDB's $lookup aggregation.
- This method effectively models a many-to-many relationship in MongoDB, allowing for flexible, scalable, and efficient data retrieval.