DEV Community

avinash-repo
avinash-repo

Posted on

mongdb queris part2

#webdev #javascript #beginners #tutorial

MongoDB uses a different syntax from SQL due to its NoSQL nature. Here are the equivalent queries in MongoDB:

  1. Fetch records present in one collection but not in another: 
   db.collection1.find({ field1: { $nin: db.collection2.distinct("field1") } });
Enter fullscreen mode Exit fullscreen mode
  1. Fetch employees not working on any project: 
   db.employee.find({ "_id": { $nin: db.projectAssignment.distinct("employeeId") } });
Enter fullscreen mode Exit fullscreen mode
  1. Fetch employees from EmployeeDetails who joined in the Year 2020: 
   db.employeeDetails.find({ "joinDate": { $gte: ISODate("2020-01-01"), $lt: ISODate("2021-01-01") } });
Enter fullscreen mode Exit fullscreen mode
  1. Fetch employees from EmployeeDetails with salary records in EmployeeSalary: 
   db.employeeDetails.find({ "_id": { $in: db.employeeSalary.distinct("employeeId") } });
Enter fullscreen mode Exit fullscreen mode
  1. Fetch project-wise count of employees: 
   db.projectAssignment.aggregate([
     { $group: { _id: "$projectId", employeeCount: { $sum: 1 } } }
   ]);
Enter fullscreen mode Exit fullscreen mode
  1. Fetch employee names and salaries with NULLs for missing salary records: 
   db.employeeDetails.aggregate([
     {
       $lookup: {
         from: "employeeSalary",
         localField: "_id",
         foreignField: "employeeId",
         as: "salaryInfo"
       }
     },
     {
       $project: {
         _id: 1,
         employeeName: 1,
         salary: { $ifNull: [{ $arrayElemAt: ["$salaryInfo.salary", 0] }, "No Salary Record"] }
       }
     }
   ]);
Enter fullscreen mode Exit fullscreen mode
  1. Fetch all Employees who are also managers: 
   db.employeeDetails.find({ "_id": { $in: db.employeeDetails.distinct("managerId", { managerId: { $ne: null } }) } });
Enter fullscreen mode Exit fullscreen mode
  1. Fetch duplicate records from EmployeeDetails: 
   db.employeeDetails.aggregate([
     { $group: { _id: { employeeId: "$employeeId", employeeName: "$employeeName" }, count: { $sum: 1 } } },
     { $match: { count: { $gt: 1 } } }
   ]);
Enter fullscreen mode Exit fullscreen mode
  1. Fetch only odd documents from the collection: 
   db.collection.find({ $expr: { $mod: [{ $indexOfArray: [{ $range: [0, { $size: "$arrayField" }] }, "$$CURRENT"] }, 2] } });
Enter fullscreen mode Exit fullscreen mode

  1. Find the 3rd highest salary without using top or limit keyword:

    db.employeeSalary.distinct("salary").sort().reverse()[2];

  2. Fetch records present in one table but not in another:

   SELECT * 
   FROM Table1
   WHERE Column1 NOT IN (SELECT Column1 FROM Table2);
Enter fullscreen mode Exit fullscreen mode
  1. Fetch employees not working on any project: 
   SELECT *
   FROM Employee
   WHERE EmployeeID NOT IN (SELECT DISTINCT EmployeeID FROM ProjectAssignment);
Enter fullscreen mode Exit fullscreen mode
  1. Fetch employees from EmployeeDetails who joined in the Year 2020: 
   SELECT *
   FROM EmployeeDetails
   WHERE YEAR(JoinDate) = 2020;
Enter fullscreen mode Exit fullscreen mode
  1. Fetch employees from EmployeeDetails with salary records in EmployeeSalary: 
   SELECT *
   FROM EmployeeDetails
   WHERE EmployeeID IN (SELECT DISTINCT EmployeeID FROM EmployeeSalary);
Enter fullscreen mode Exit fullscreen mode
  1. Fetch project-wise count of employees: 
   SELECT ProjectID, COUNT(EmployeeID) AS EmployeeCount
   FROM ProjectAssignment
   GROUP BY ProjectID;
Enter fullscreen mode Exit fullscreen mode
  1. Fetch employee names and salaries with NULLs for missing salary records: 
   SELECT E.EmployeeID, E.EmployeeName, ISNULL(S.Salary, 'No Salary Record') AS Salary
   FROM EmployeeDetails E
   LEFT JOIN EmployeeSalary S ON E.EmployeeID = S.EmployeeID;
Enter fullscreen mode Exit fullscreen mode
  1. Fetch all Employees who are also managers: 
   SELECT *
   FROM EmployeeDetails
   WHERE EmployeeID IN (SELECT DISTINCT ManagerID FROM EmployeeDetails WHERE ManagerID IS NOT NULL);
Enter fullscreen mode Exit fullscreen mode
  1. Fetch duplicate records from EmployeeDetails: 
   SELECT EmployeeID, EmployeeName, COUNT(*)
   FROM EmployeeDetails
   GROUP BY EmployeeID, EmployeeName
   HAVING COUNT(*) > 1;
Enter fullscreen mode Exit fullscreen mode
  1. Fetch only odd rows from the table: 
   SELECT *
   FROM TableName
   WHERE ROWID % 2 <> 0;
Enter fullscreen mode Exit fullscreen mode

  1. Find the 3rd highest salary without using top or limit keyword:

    SELECT DISTINCT Salary
FROM EmployeeSalary E1
WHERE 3 = (SELECT COUNT(DISTINCT Salary) FROM EmployeeSalary E2 WHERE E1.Salary <= E2.Salary);

The provided MongoDB queries and commands focus on the manipulation and retrieval of data from the "staff" collection. Below is an analysis of each command:

  1. Inserting Data: 
   db.staff.insertMany([{
  "employees": [
    {
      "id": 1,
      "name": "John Doe",
      "position": "Software Engineer",
      "salary": 80000
    },
    {
      "id": 2,
      "name": "Jane Smith",
      "position": "Data Analyst",
      "salary": 65000
    },
    {
      "id": 3,
      "name": "Robert Johnson",
      "position": "Project Manager",
      "salary": 90000
    }
  ],
  "contractors": [
    {
      "id": 101,
      "name": "Emily Brown",
      "project": "Data Migration",
      "dailyRate": 400
    },
    {
      "id": 102,
      "name": "Michael Davis",
      "project": "Web Development",
      "dailyRate": 450
    }
  ],
  "freelancers": [
    {
      "id": 201,
      "name": "Sophia Wilson",
      "service": "Graphic Design",
      "hourlyRate": 50
    },
    {
      "id": 202,
      "name": "Daniel White",
      "service": "Content Writing",
      "hourlyRate": 40
    }
  ]
}
]);
Enter fullscreen mode Exit fullscreen mode
  • Inserts a document containing information about employees, contractors, and freelancers.
  1. Query for Freelancers in Graphic Design: 
   db.staff.find(
     { "freelancers": { $elemMatch: { "service": "Graphic Design" } } },
     { "freelancers.$": 1, "_id": 0 }
   );
Enter fullscreen mode Exit fullscreen mode
  • Retrieves freelancers specializing in Graphic Design.
  1. Query for All Employees: 
   db.staff.find({}, { "employees": 1, "_id": 0 });
Enter fullscreen mode Exit fullscreen mode
  • Retrieves information about all employees.
  1. Query for Employees with Salary > 80000: 
   db.staff.find(
     { "employees.salary": { $gt: 80000 } },
     { "employees.$": 1, "_id": 0 }
   );
Enter fullscreen mode Exit fullscreen mode
  • Retrieves employees with a salary greater than 80000.
  1. Query for Employees with Salary > 80000 using $elemMatch: 
   db.staff.find(
     { "employees": { $elemMatch: { "salary": { $gt: 80000 } } } },
     { "_id": 0, "employees.$": 1 }
   );
Enter fullscreen mode Exit fullscreen mode
  • Another approach using $elemMatch to retrieve employees with a salary greater than 80000.
  1. Aggregate to Retrieve Employees with Salary > 80000: 
   db.staff.aggregate([
     { $match: { "employees": { $elemMatch: { "salary": { $gt: 80000 } } } } },
     { $project: { "_id": 0, "empwithsalary": "$employees" } }
   ]);
Enter fullscreen mode Exit fullscreen mode
  • Uses aggregation to match and project employees with a salary greater than 80000.
  1. Query for Freelancers: 
   db.staff.find({}, { "freelancers": 1, "_id": 0 });
Enter fullscreen mode Exit fullscreen mode
  • Retrieves information about all freelancers.
  1. Query for Freelancers in Graphic Design (Alternative): 
   db.staff.find({"freelancers.service": "Graphic Design" });
   db.staff.find({ "freelancers.service": 'Graphic Design' });
Enter fullscreen mode Exit fullscreen mode
  • Alternative queries to retrieve freelancers in Graphic Design.
  1. Aggregate to Calculate Total Salary of Employees: 
   db.staff.aggregate([
     { $unwind: "$employees" },
     { $group: { _id: null, totalSalary: { $sum: "$employees.salary" } } }
   ]);
Enter fullscreen mode Exit fullscreen mode
  • Uses aggregation to unwind and group employees, calculating the total salary.

  1. Aggregate to Group and Reorganize Data:

    db.staff.aggregate([
  { $unwind: "$employees" },
  { $match: { "employees.position": { $exists: true } } },
  {
    $group: {
      _id: null,
      employees: { $push: "$employees" },
      contractors: { $first: "$contractors" },
      freelancers: { $first: "$freelancers" }
    }
  },
  { $project: { _id: 0 } }
]);

- Uses aggregation to group, match, and reorganize data into distinct categories.
Enter fullscreen mode Exit fullscreen mode

  1. Aggregate to Calculate Total Salary and Unwind Contractors/Freelancers:

    db.staff.aggregate([
  { $unwind: "$employees" },
  { $match: { "employees.salary": { $gt: 80000 } } },
  { $group: { _id: null, totalSalary: { $sum: "$employees.salary" } } },
  { $unwind: "$contractors" },
  { $unwind: "$freelancers" }
]);

- Aggregation to calculate total salary of employees with salary > 80000 and unwind contractors and freelancers.
Enter fullscreen mode Exit fullscreen mode

Please note that these queries assume a specific structure of the "staff" collection and may need adjustments based on the actual data model.

Top comments (0)

Read next

vikas76 profile image

How Artificial Intelligence and Data Science Work Together to Solve Complex Problems in 2025

Vikas76 -

thinkthroo profile image

Node.js util module usage in Changesets

thinkThroo -

atixag profile image

The future of software architecture: focus on event-driven architecture

ATIXAG -

seatunnel profile image

Conquering SeaTunnel Challenges: Your Go-To Solutions Revealed

Apache SeaTunnel -