mongdb queris part2
Tags: webdev, javascript, beginners, tutorial
Published: 2024-02-03 02:50:58

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:**
   ```javascript
   db.collection1.find({ field1: { $nin: db.collection2.distinct("field1") } });
   ```

2. **Fetch employees not working on any project:**
   ```javascript
   db.employee.find({ "_id": { $nin: db.projectAssignment.distinct("employeeId") } });
   ```

3. **Fetch employees from EmployeeDetails who joined in the Year 2020:**
   ```javascript
   db.employeeDetails.find({ "joinDate": { $gte: ISODate("2020-01-01"), $lt: ISODate("2021-01-01") } });
   ```

4. **Fetch employees from EmployeeDetails with salary records in EmployeeSalary:**
   ```javascript
   db.employeeDetails.find({ "_id": { $in: db.employeeSalary.distinct("employeeId") } });
   ```

5. **Fetch project-wise count of employees:**
   ```javascript
   db.projectAssignment.aggregate([
     { $group: { _id: "$projectId", employeeCount: { $sum: 1 } } }
   ]);
   ```

6. **Fetch employee names and salaries with NULLs for missing salary records:**
   ```javascript
   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"] }
       }
     }
   ]);
   ```

7. **Fetch all Employees who are also managers:**
   ```javascript
   db.employeeDetails.find({ "_id": { $in: db.employeeDetails.distinct("managerId", { managerId: { $ne: null } }) } });
   ```

8. **Fetch duplicate records from EmployeeDetails:**
   ```javascript
   db.employeeDetails.aggregate([
     { $group: { _id: { employeeId: "$employeeId", employeeName: "$employeeName" }, count: { $sum: 1 } } },
     { $match: { count: { $gt: 1 } } }
   ]);
   ```

9. **Fetch only odd documents from the collection:**
   ```javascript
   db.collection.find({ $expr: { $mod: [{ $indexOfArray: [{ $range: [0, { $size: "$arrayField" }] }, "$$CURRENT"] }, 2] } });
   ```

10. **Find the 3rd highest salary without using top or limit keyword:**
    ```mongodb
    db.employeeSalary.distinct("salary").sort().reverse()[2];
    ```


1. **Fetch records present in one table but not in another:**
   ```sql
   SELECT * 
   FROM Table1
   WHERE Column1 NOT IN (SELECT Column1 FROM Table2);
   ```

2. **Fetch employees not working on any project:**
   ```sql
   SELECT *
   FROM Employee
   WHERE EmployeeID NOT IN (SELECT DISTINCT EmployeeID FROM ProjectAssignment);
   ```

3. **Fetch employees from EmployeeDetails who joined in the Year 2020:**
   ```sql
   SELECT *
   FROM EmployeeDetails
   WHERE YEAR(JoinDate) = 2020;
   ```

4. **Fetch employees from EmployeeDetails with salary records in EmployeeSalary:**
   ```sql
   SELECT *
   FROM EmployeeDetails
   WHERE EmployeeID IN (SELECT DISTINCT EmployeeID FROM EmployeeSalary);
   ```

5. **Fetch project-wise count of employees:**
   ```sql
   SELECT ProjectID, COUNT(EmployeeID) AS EmployeeCount
   FROM ProjectAssignment
   GROUP BY ProjectID;
   ```

6. **Fetch employee names and salaries with NULLs for missing salary records:**
   ```sql
   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;
   ```

7. **Fetch all Employees who are also managers:**
   ```sql
   SELECT *
   FROM EmployeeDetails
   WHERE EmployeeID IN (SELECT DISTINCT ManagerID FROM EmployeeDetails WHERE ManagerID IS NOT NULL);
   ```

8. **Fetch duplicate records from EmployeeDetails:**
   ```sql
   SELECT EmployeeID, EmployeeName, COUNT(*)
   FROM EmployeeDetails
   GROUP BY EmployeeID, EmployeeName
   HAVING COUNT(*) > 1;
   ```

9. **Fetch only odd rows from the table:**
   ```sql
   SELECT *
   FROM TableName
   WHERE ROWID % 2 <> 0;
   ```

10. **Find the 3rd highest salary without using top or limit keyword:**
    ```sql
    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:**
   ```javascript
   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
    }
  ]
}
]);
   ```
   - Inserts a document containing information about employees, contractors, and freelancers.

2. **Query for Freelancers in Graphic Design:**
   ```javascript
   db.staff.find(
     { "freelancers": { $elemMatch: { "service": "Graphic Design" } } },
     { "freelancers.$": 1, "_id": 0 }
   );
   ```
   - Retrieves freelancers specializing in Graphic Design.

3. **Query for All Employees:**
   ```javascript
   db.staff.find({}, { "employees": 1, "_id": 0 });
   ```
   - Retrieves information about all employees.

4. **Query for Employees with Salary > 80000:**
   ```javascript
   db.staff.find(
     { "employees.salary": { $gt: 80000 } },
     { "employees.$": 1, "_id": 0 }
   );
   ```
   - Retrieves employees with a salary greater than 80000.

5. **Query for Employees with Salary > 80000 using $elemMatch:**
   ```javascript
   db.staff.find(
     { "employees": { $elemMatch: { "salary": { $gt: 80000 } } } },
     { "_id": 0, "employees.$": 1 }
   );
   ```
   - Another approach using $elemMatch to retrieve employees with a salary greater than 80000.

6. **Aggregate to Retrieve Employees with Salary > 80000:**
   ```javascript
   db.staff.aggregate([
     { $match: { "employees": { $elemMatch: { "salary": { $gt: 80000 } } } } },
     { $project: { "_id": 0, "empwithsalary": "$employees" } }
   ]);
   ```
   - Uses aggregation to match and project employees with a salary greater than 80000.

7. **Query for Freelancers:**
   ```javascript
   db.staff.find({}, { "freelancers": 1, "_id": 0 });
   ```
   - Retrieves information about all freelancers.

8. **Query for Freelancers in Graphic Design (Alternative):**
   ```javascript
   db.staff.find({"freelancers.service": "Graphic Design" });
   db.staff.find({ "freelancers.service": 'Graphic Design' });
   ```
   - Alternative queries to retrieve freelancers in Graphic Design.

9. **Aggregate to Calculate Total Salary of Employees:**
   ```javascript
   db.staff.aggregate([
     { $unwind: "$employees" },
     { $group: { _id: null, totalSalary: { $sum: "$employees.salary" } } }
   ]);
   ```
   - Uses aggregation to unwind and group employees, calculating the total salary.

10. **Aggregate to Group and Reorganize Data:**
    ```javascript
    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.

11. **Aggregate to Calculate Total Salary and Unwind Contractors/Freelancers:**
    ```javascript
    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.

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