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

rivea0 profile image

LeetCode Meditations: Serialize and Deserialize Binary Tree

Eda -

kartikmehta8 profile image

Exploring Graph Databases: Neo4j

Kartik Mehta -

0xmesto profile image

Unofficial ChatGPT API with simple agent-based

0xMesto -

paulike profile image

Conditional Expressions

Paul Ngugi -