Mongoose query

const mongoose = require('mongoose');
const Employee = require('./Employee'); // Assuming the Employee model exists
const Department = require('./Department'); // Assuming the Department model exists

async function getEmployeeCountByDepartment() {
const result = await Employee.aggregate([
{
$lookup: {
from: 'departments', // This is the collection name in MongoDB
localField: 'department', // The field in Employee model that holds department reference
foreignField: '_id', // The field in Department model that we are referencing
as: 'department_info', // The resulting array will be called department_info
}
},
{
$unwind: '$department_info' // Unwind the department_info array to flatten the structure
},
{
$group: {
_id: '$department_info.name', // Group by department name
employee_count: { $sum: 1 } // Count the number of employees in each department
}
},
{
$sort: { employee_count: -1 } // Optionally, sort by employee count in descending order
}
]);

console.log(result); // This will print the department and its corresponding employee count
}

getEmployeeCountByDepartment();