Gallery
Share
Explore

MONGO Aggregation Pipelines and JSON Predicate JOINS

Let's create a detailed example using MongoDB and Mongoose that involves creating collections for students and classes, and then using the aggregation pipeline to simulate registering students into classes.

Step-by-Step Guide

Step 1: Setup MongoDB and Mongoose

Install MongoDB: If you haven't already, sign up at
MongoDB Atlas
⁠
and create a new cluster.
Install Mongoose: Use npm to install Mongoose in your Node.js project.
npm install mongoose

Step 2: Define the Schemas and Models

Create a file named models.js to define the schemas for students and classes.
const mongoose = require('mongoose');

const studentSchema = new mongoose.Schema({
studentId: String,
firstName: String,
lastName: String
}, { collection: 'students' });

const classSchema = new mongoose.Schema({
classId: String,
courseName: String,
dateTime: String,
instructor: String
}, { collection: 'classes' });

const Student = mongoose.model('Student', studentSchema);
const Class = mongoose.model('Class', classSchema);

module.exports = { Student, Class };

Step 3: Insert Sample Data

Create a file named insertData.js to insert sample students and classes into the database.
const mongoose = require('mongoose');
const { Student, Class } = require('./models');

// MongoDB Cloud URI
const uri = "mongodb+srv://username:password@cluster0.mongodb.net/mydatabase?retryWrites=true&w=majority";

mongoose.connect(uri, { useNewUrlParser: true, useUnifiedTopology: true })
.then(() => {
console.log('Connected to MongoDB Cloud');
insertData();
})
.catch((err) => {
console.error('Connection error', err);
});

const insertData = async () => {
const students = [
{ studentId: 'S001', firstName: 'John', lastName: 'Doe' },
{ studentId: 'S002', firstName: 'Jane', lastName: 'Smith' },
{ studentId: 'S003', firstName: 'Alice', lastName: 'Johnson' }
];

const classes = [
{ classId: 'C101', courseName: 'Math 101', dateTime: 'Mon 9AM', instructor: 'Prof. Newton' },
{ classId: 'C102', courseName: 'Physics 101', dateTime: 'Wed 11AM', instructor: 'Prof. Einstein' },
{ classId: 'C103', courseName: 'Chemistry 101', dateTime: 'Fri 1PM', instructor: 'Prof. Curie' }
];

try {
await Student.insertMany(students);
await Class.insertMany(classes);
console.log('Sample data inserted');
} catch (err) {
console.error('Error inserting data:', err);
} finally {
mongoose.connection.close().then(() => {
console.log('Mongoose connection closed');
}).catch(err => {
console.error('Error closing connection:', err);
});
}
};

Step 4: Register Students into Classes

Create a file named registerStudents.js to register students into classes and use the aggregation pipeline to show the registration.
const mongoose = require('mongoose');
const { Student, Class } = require('./models');

// MongoDB Cloud URI
const uri = "mongodb+srv://username:password@cluster0.mongodb.net/mydatabase?retryWrites=true&w=majority";

mongoose.connect(uri, { useNewUrlParser: true, useUnifiedTopology: true })
.then(() => {
console.log('Connected to MongoDB Cloud');
registerStudents();
})
.catch((err) => {
console.error('Connection error', err);
});

const registerStudents = async () => {
const registrations = [
{ studentId: 'S001', classId: 'C101' },
{ studentId: 'S001', classId: 'C102' },
{ studentId: 'S002', classId: 'C101' },
{ studentId: 'S003', classId: 'C103' }
];

const registrationOps = registrations.map(registration => ({
updateOne: {
filter: { _id: registration.classId },
update: { $push: { students: registration.studentId } }
}
}));

try {
const bulkResult = await Class.bulkWrite(registrationOps);
console.log('Students registered to classes', bulkResult);
} catch (err) {
console.error('Error registering students:', err);
} finally {
mongoose.connection.close().then(() => {
console.log('Mongoose connection closed');
}).catch(err => {
console.error('Error closing connection:', err);
});
}
};

Step 5: Display Registered Students in Classes

Create a file named displayRegistrations.js to use the aggregation pipeline to display which students are registered in which classes.
const mongoose = require('mongoose');
const { Student, Class } = require('./models');

// MongoDB Cloud URI
const uri = "mongodb+srv://username:password@cluster0.mongodb.net/mydatabase?retryWrites=true&w=majority";

mongoose.connect(uri, { useNewUrlParser: true, useUnifiedTopology: true })
.then(() => {
console.log('Connected to MongoDB Cloud');
displayRegistrations();
})
.catch((err) => {
console.error('Connection error', err);
});

const displayRegistrations = async () => {
try {
const result = await Class.aggregate([
{
$lookup: {
from: 'students',
localField: 'students',
foreignField: 'studentId',
as: 'registeredStudents'
}
},
{
$project: {
classId: 1,
courseName: 1,
dateTime: 1,
instructor: 1,
registeredStudents: {
studentId: 1,
firstName: 1,
lastName: 1
}
}
}
]);

console.log(JSON.stringify(result, null, 2));
} catch (err) {
console.error('Error displaying registrations:', err);
} finally {
mongoose.connection.close().then(() => {
console.log('Mongoose connection closed');
}).catch(err => {
console.error('Error closing connection:', err);
});
}
};

Explanation

Schemas and Models: We define Student and Class schemas and models.
The Class schema is modified to include an array of studentIds representing the students registered in each class.
Inserting Data: In insertData.js, we insert sample students and classes into the database.
Registering Students: In registerStudents.js, we register students into classes by pushing their studentId into the students array of the Class model. We use bulkWrite for efficiency.
Displaying Registrations: In displayRegistrations.js, we use the aggregation pipeline with $lookup to join students and classes, displaying the students registered in each class.

Running the Programs

Insert Sample Data:
bash
Copy code
node insertData.js

Register Students:
bash
Copy code
node registerStudents.js

Display Registrations:
bash
Copy code
node displayRegistrations.js

This setup will help students understand how to use Mongoose and MongoDB's aggregation framework to perform operations similar to predicate joins in SQL, showing a clear path from schema definition to data manipulation and retrieval.


megaphone

The error you're encountering is due to the fact that the _id field in MongoDB is expected to be an ObjectId, but in the registerStudents function, we're trying to match it against a string.

To resolve this issue, we need to ensure that the classId field in the Class schema is used for matching, instead of the default _id field.

Updated Schemas

Let's first update the models.js to include students as an array of student IDs in the Class schema:

const mongoose = require('mongoose');
const studentSchema = new mongoose.Schema({ studentId: String, firstName: String, lastName: String}, { collection: 'students' });
const classSchema = new mongoose.Schema({ classId: String, courseName: String, dateTime: String, instructor: String, students: [String] // This will store student IDs as strings}, { collection: 'classes' });
const Student = mongoose.model('Student', studentSchema);const Class = mongoose.model('Class', classSchema);
module.exports = { Student, Class };

Updated registerStudents.js

Now, update the registerStudents.js to use the classId field for matching:

const mongoose = require('mongoose');const { Student, Class } = require('./models');
// MongoDB Cloud URIconst uri = "mongodb+srv://username:password@cluster0.mongodb.net/mydatabase?retryWrites=true&w=majority";
mongoose.connect(uri, { useNewUrlParser: true, useUnifiedTopology: true }) .then(() => { console.log('Connected to MongoDB Cloud'); registerStudents(); }) .catch((err) => { console.error('Connection error', err); });
const registerStudents = async () => { const registrations = [ { studentId: 'S001', classId: 'C101' }, { studentId: 'S001', classId: 'C102' }, { studentId: 'S002', classId: 'C101' }, { studentId: 'S003', classId: 'C103' } ];
 const registrationOps = registrations.map(registration => ({ updateOne: { filter: { classId: registration.classId }, update: { $push: { students: registration.studentId } } } }));
 try { const bulkResult = await Class.bulkWrite(registrationOps); console.log('Students registered to classes', bulkResult); } catch (err) { console.error('Error registering students:', err); } finally { mongoose.connection.close().then(() => { console.log('Mongoose connection closed'); }).catch(err => { console.error('Error closing connection:', err); }); }};

Updated displayRegistrations.js

Update displayRegistrations.js to ensure it uses the classId for displaying the registrations:

const mongoose = require('mongoose');const { Student, Class } = require('./models');
// MongoDB Cloud URIconst uri = "mongodb+srv://username:password@cluster0.mongodb.net/mydatabase?retryWrites=true&w=majority";
mongoose.connect(uri, { useNewUrlParser: true, useUnifiedTopology: true }) .then(() => { console.log('Connected to MongoDB Cloud'); displayRegistrations(); }) .catch((err) => { console.error('Connection error', err); });
const displayRegistrations = async () => { try { const result = await Class.aggregate([ { $lookup: { from: 'students', localField: 'students', foreignField: 'studentId', as: 'registeredStudents' }
Want to print your doc?
This is not the way.
Try clicking the ⋯ next to your doc name or using a keyboard shortcut (
CtrlP
) instead.