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 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 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: { 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 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);
});
}
};

Summary of Changes

Schemas: Updated classSchema to include students as an array of strings.
Registration: Modified the registerStudents.js script to use classId instead of _id for matching.
Display: Ensured displayRegistrations.js script uses classId for displaying registered students.
These updates should resolve the error and allow you to successfully register students into classes and display the registrations.
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.