DEV Community

Anjali Gurjar
Anjali Gurjar

Posted on

1

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

Image of Datadog

The Essential Toolkit for Front-end Developers

Take a user-centric approach to front-end monitoring that evolves alongside increasingly complex frameworks and single-page applications.

Get The Kit

Top comments (0)

Image of Docusign

🛠️ Bring your solution into Docusign. Reach over 1.6M customers.

Docusign is now extensible. Overcome challenges with disconnected products and inaccessible data by bringing your solutions into Docusign and publishing to 1.6M customers in the App Center.

Learn more