Mongoose Query Question

// Basic Mongoose Queries

// 1. Retrieve all records from a collection

const employees = await Employee.find();

// 2. Filter records using WHERE

const itEmployees = await Employee.find({ department: 'IT' });

// 3. Fetch distinct records

const departments = await Employee.distinct('department');

// 4. Sort data in ascending and descending order

const sortedAsc = await Employee.find().sort({ salary: 1 });

const sortedDesc = await Employee.find().sort({ salary: -1 });

// 5. Fetch top 5 highest salaries

const top5Salaries = await Employee.find().sort({ salary: -1 }).limit(5);

// Aggregations and Grouping

// 6. Count rows in a collection

const employeeCount = await Employee.countDocuments();

// 7. Calculate average salary

const avgSalary = await Employee.aggregate([

{ $group: { _id: null, avgSalary: { $avg: '$salary' } } }

]);

// 8. Group by department and calculate avg salary

const avgSalaryByDept = await Employee.aggregate([

{ $group: { _id: '$department', avgSalary: { $avg: '$salary' } } }

]);

// Functions in Mongoose

// 9. Concatenate strings

const fullNames = await Employee.find({}, { fullName: { $concat: ['$first_name', ' ', '$last_name'] } });

// 10. Length of string

const nameLengths = await Employee.aggregate([

{ $project: { nameLength: { $strLenCP: '$first_name' } } }

]);

// 11. Extract first three characters

const namePrefix = await Employee.aggregate([

{ $project: { prefix: { $substr: ['$first_name', 0, 3] } } }

]);

// 12. Current date and time

const currentDate = new Date();

// 13. Date difference

const dateDiff = Math.floor((new Date('2025-12-31') - new Date('2025-01-01')) / (1000 * 60 * 60 * 24));

// 14. Extract year from date

const hireYears = await Employee.aggregate([

{ $project: { hireYear: { $year: '$hire_date' } } }

]);

// 15. Round value to 2 decimal places

const roundedSalaries = await Employee.aggregate([

{ $project: { roundedSalary: { $round: ['$salary', 2] } } }

]);

// 16. IF-ELSE using CASE

const salaryCategory = await Employee.aggregate([

{ $project: {

first_name: 1,

salary: 1,

salaryCategory: {

$switch: {

branches: [

{ case: { $gt: ['$salary', 70000] }, then: 'High' },

{ case: { $and: [ { $gte: ['$salary', 50000] }, { $lte: ['$salary', 70000] } ] }, then: 'Medium' }

],

default: 'Low'

}

}

}

}

]);

// 17. Replace NULL values

const phoneNumbers = await Employee.aggregate([

{ $project: { phone: { $ifNull: ['$phone', 'N/A'] } } }

]);

// Joins and Multi-Table Queries

// 18. Inner Join (Orders with Customer Details)

const ordersWithCustomers = await Order.aggregate([

{

$lookup: {

from: 'customers',

localField: 'customer_id',

foreignField: '_id',

as: 'customerDetails'

}

}

]);

// 19. Left Join (All Customers and Orders)

const customersWithOrders = await Customer.aggregate([

{

$lookup: {

from: 'orders',

localField: '_id',

foreignField: 'customer_id',

as: 'orders'

}

}

]);

// Subqueries

// 20. Second highest salary

const secondHighestSalary = await Employee.aggregate([

{ $sort: { salary: -1 } },

{ $skip: 1 },

{ $limit: 1 }

]);

// 21. Employees earning more than average

const employeesAboveAvg = await Employee.aggregate([

{

$match: {

salary: { $gt: avgSalary[0].avgSalary }

}

}

]);

// Practical Questions

// 22. Count employees by department

const empCountByDept = await Employee.aggregate([

{ $group: { _id: '$department', empCount: { $sum: 1 } } }

]);

// 23. Count employees in HR and Admin

const hrAndAdminCount = await Employee.aggregate([

{ $match: { department: { $in: ['HR', 'Admin'] } } },

{ $group: { _id: '$department', empCount: { $sum: 1 } } }

]);

// 24. Count employees in HR, Admin, Sales

const specificDeptCount = await Employee.aggregate([

{ $match: { department: { $in: ['HR', 'Admin', 'Sales'] } } },

{ $group: { _id: '$department', empCount: { $sum: 1 } } }

]);