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 } } }
]);