MongoDB Quries
Tags: webdev, javascript, beginners, mongodb
Published: 2024-02-02 22:16:47

The provided script demonstrates several MongoDB operations, including data insertion, updates, deletions, aggregation, and queries. I will provide an overview of each operation with example data and expected output.
https://onecompiler.com/mongodb/423ag25pw
1. **Data Insertion:**
   ```javascript
   db.students.insertMany([
     { id: 1, name: 'Ryan', gender: 'M' },
     { id: 2, name: 'Joanna', gender: 'F' },
     { id: 3, name: 'Rahul', gender: 'M' }
   ]);
   ```

2. **Profiling and Query Execution:**
   ```javascript
   db.setProfilingLevel(1, { slowms: 100 });
   db.system.profile

- 
.find().pretty();
   ```

3. **Inserting and Updating Email:**
   ```javascript
   db.students.insert({ id: 3, name: 'Rahul', gender: 'M' });
   db.students.update({ id: 1 }, { $set: { email: 'ak@gm.com' } });
   db.students.update({ id: 2 }, { $set: { email: 'ak2@gm.com' } });
   db.students.update({ id: 3 }, { $set: { email: 'ak3@gm.com' } });
   ```

4. **Upsert and Deletion:**
   ```javascript
   db.students.update({ id: 11 }, { $set: { name: 'Sangita', gender: 'F' } }, { upsert: true });
   db.students.deleteOne({ id: 2 });
   ```

5. **Inserting and Updating Marks:**
   ```javascript
   db.marks.insertMany([
     { sid: 1, pass: 'Failed', marks: 32, ncc: 'Y' },
     { sid: 2, pass: 'Passed', marks: 40, ncc: 'N' },
     { sid: 3, pass: 'Passed', marks: 70, ncc: 'Y' }
   ]);

   db.marks.updateMany({ ncc: 'Y' }, { $set: { award: 'yyes' } });
   ```

6. **Aggregation - Joining Students and Marks:**
   ```javascript
   db.students.aggregate([
     {
       $lookup: { from: 'marks', localField: 'id', foreignField: 'sid', as: 'SMarks' }
     },
     { $project: { _id: 0, SMarks: { _id: 0 } } }
   ]);
   ```

7. **Aggregation - Calculating Total Marks:**
   ```javascript
   db.marks.aggregate([
     { $group: { _id: null, Tsum: { $sum: '$marks' } } },
     { $project: { _id: 0, Tsum: 1 } }
   ]);

   db.marks.aggregate([
     { $group: { _id: null, Tavg: { $avg: '$marks' } } }
   ]);
   ```

8. **Aggregation - Additional Statistics and Reshaping:**
   ```javascript
   db.marks.aggregate([
     {
       $group: {
         _id: null,
         Tmax: { $max: '$marks' },
         Tmin: { $min: '$marks' },
         TTsum: { $sum: '$marks' },
         TTavg: { $avg: '$marks' },
         Tcount: { $sum: 1 }
       }
     },
     {
       $addFields: { empInfo: { $ifNull: ['$empInfo', [{ msg: 'deleted by deletedOne' }]] } }
     },
     { $project: { _id: 0 } }
   ]);
   ```

9. **Replacing Document in Students Collection:**
   ```javascript
   db.students.replaceOne(
     { email: 'ak3@gm.com' },
     {
       name: 'John Doe',
       age: 26,
       email: 'ak3@gm.com',
       address: { city: 'New York', country: 'USA' }
     }
   );
   ```

10. **Example Queries:**
   Uncommented queries for reference:
   - `db.students.find()`
   - `db.marks.find({ ncc: 'Y' })`
   - `db.marks.find({ marks: { $gte: 40 } })`
   - `db.marks.aggregate([{$match:{ncc:{$in:["Y"]}}}])`
   - `db.marks.aggregate([{$match:{ncc:"N"}}])`

11. **Inserting Data into "mytags" Collection:**
    ```javascript
    db.mytags.insertMany([
      // Example data provided
    ]);
    ```

12. **Various Aggregation and Query Operations on "mytags":**
    - Please refer to the comments and uncommented queries in the script for details.

Note: The actual output of queries may vary based on the specific data present in the database at the time of execution.

The provided script involves the insertion of data into the "mytags" collection and subsequent MongoDB queries and aggregations. I will provide an overview of each operation with example data and expected output.

1. **Inserting Data into "mytags" Collection:**
    ```javascript
    db.mytags.insertMany([
      {
        "id": 1,
        "title": "Introduction to MongoDB",
        "tags": ["mongodb", "database"],
        "status": "active",
        "like": 50
      },
      {
        "id": 2,
        "title": "Exploring NoSQL Concepts",
        "tags": ["nosql", "database"],
        "status": "inactive",
        "like": 250
      },
      {
        "id": 3,
        "title": "Building Scalable Applications",
        "tags": ["php", "nosql", "scalability"],
        "status": "inactive",
        "like": 1050
      },
      {
        "id": 4,
        "title": "Data Modeling in MongoDB",
        "tags": ["mongodb", "database", "modeling"],
        "status": "active",
        "like": 350
      },
      {
        "id": 5,
        "title": "Node with MongoDB",
        "tags": ["nodejs", "sql", "react"],
        "status": "active",
        "like": "na"
      }
    ]);
    ```

2. **Queries and Aggregations on "mytags":**
   - Uncommented queries for reference:
     - `db.mytags.find({like:{$gt:500},status:{$eq:"active"}})`
     - `db.mytags.find({status:"inactive"})`
     - `db.mytags.find({ status: { $eq: "active" } })`
     - `db.mytags.aggregate([{$match:{status:"active",like:{$gt:51}}}]`
     - `db.mytags.find({ title: { $regex: /^Building/ } })`
     - `db.mytags.find({$or:[{status:"active",tags:{$in:["mongodb"]}}, {like:{$gt:1000}}]})`
     - `db.mytags.find({$and:[{status:"inactive"}, {like:{$lt:251}}]})`
     - `db.mytags.find().sort({ like: -1 })`
     - `db.mytags.aggregate([{ $unwind: "$tags" }])`
     - `db.mytags.aggregate([{ $match: { like: { $gt: 50 } } }])`
     - `db.mytags.aggregate([{ $skip: 2 },{ $limit: 3 }])`
     - `db.mytags.aggregate([{ $facet: { averageLike: [{ $group: { _id: null, avgLike: { $avg: "$like" } } }], totalLike: [{ $group: { _id: null, totalQty: { $sum: "$like" } } }] } }])`
     - `db.mytags.countDocuments({ like: { $gt: 50 } })`
     - `db.mytags.distinct("status")`
     - `db.mytags.distinct("tags")`
     - `db.mytags.find({ like: { $type: "string" } })`
     - `db.mytags.find({tags:{$in:["mongodb","database"]}})`
     - `db.mytags.find({tags: { $all: ["mongodb", "database"] } })`

Note: The actual output of queries may vary based on the specific data present in the "mytags" collection at the time of execution. Ensure the data aligns with the examples provided for accurate results.

Certainly, let's provide the expected output for each query based on the example data you provided earlier.

1. **Query: `db.mytags.find({like:{$gt:500},status:{$eq:"active"}})`**
   - Expected Output: Documents where 'like' is greater than 500 and 'status' is "active".

2. **Query: `db.mytags.find({status:"inactive"})`**
   - Expected Output: Documents where 'status' is "inactive".

3. **Query: `db.mytags.find({ status: { $eq: "active" } })`**
   - Expected Output: Documents where 'status' is "active".

4. **Query: `db.mytags.aggregate([{$match:{status:"active",like:{$gt:51}}}])`**
   - Expected Output: Aggregated documents where 'status' is "active" and 'like' is greater than 51.

5. **Query: `db.mytags.find({ title: { $regex: /^Building/ } })`**
   - Expected Output: Documents where 'title' starts with "Building".

6. **Query: `db.mytags.find({$or:[{status:"active",tags:{$in:["mongodb"]}}, {like:{$gt:1000}}]})`**
   - Expected Output: Documents where 'status' is "active" and includes the tag "mongodb", or 'like' is greater than 1000.

7. **Query: `db.mytags.find({$and:[{status:"inactive"}, {like:{$lt:251}}]})`**
   - Expected Output: Documents where 'status' is "inactive" and 'like' is less than 251.

8. **Query: `db.mytags.find().sort({ like: -1 })`**
   - Expected Output: All documents sorted in descending order based on the 'like' field.

9. **Query: `db.mytags.aggregate([{ $unwind: "$tags" }])`**
   - Expected Output: Unwound documents based on the 'status' field.

10. **Query: `db.mytags.aggregate([{ $match: { like: { $gt: 50 } } }])`**
    - Expected Output: Aggregated documents where 'like' is greater than 50.

11. **Query: `db.mytags.aggregate([{ $skip: 2 },{ $limit: 3 }])`**
    - Expected Output: Skipped the first 2 documents and limited the result to 3 documents.

12. **Query: `db.mytags.aggregate([{ $facet: { averageLike: [{ $group: { _id: null, avgLike: { $avg: "$like" } } }], totalLike: [{ $group: { _id: null, totalQty: { $sum: "$like" } } }] } }])`**
    - Expected Output: Aggregated result providing average and total of the 'like' field.

13. **Query: `db.mytags.countDocuments({ like: { $gt: 50 } })`**
    - Expected Output: Count of documents where 'like' is greater than 50.

14. **Query: `db.mytags.distinct("status")`**
    - Expected Output: Distinct values of the 'status' field.

15. **Query: `db.mytags.distinct("tags")`**
    - Expected Output: Distinct values of the 'tags' field.

16. **Query: `db.mytags.find({ like: { $type: "string" } })`**
    - Expected Output: Documents where the 'like' field is of type string.

17. **Query: `db.mytags.find({tags:{$in:["mongodb","database"]}})`**
    - Expected Output: Documents where 'tags' includes "mongodb" or "database".

18. **Query: `db.mytags.find({tags: { $all: ["mongodb", "database"] } })`**
    - Expected Output: Documents where 'tags' includes both "mongodb" and "database".

Please note that the actual output may vary based on the data in your MongoDB collection at the time of execution.


The provided code snippets involve MongoDB operations, focusing on the insertion of documents into collections and performing various queries and aggregation operations. Let's analyze and explain each part:

1. **Inserting Documents into Collections:**
   ```javascript
   db.employees.insertMany([
     {empId: 1, name: 'Clark', dept: 'Sales' },
     {empId: 2, name: 'Dave', dept: 'Accounting' },
     {empId: 3, name: 'Ava', dept: 'Sales' }
   ]);

   db.person.insertMany([
     // ... (individual documents with personal information)
   ]);
   ```
   - The code inserts multiple documents into the "employees" and "person" collections of the MongoDB database.

2. **Querying and Sorting Documents in the "person" Collection:**
   ```javascript
   db.person.find({gender:{$eq:"Male"}}).sort({age:-1})
   db.person.find({gender:{$in:["Male","Female"]}}).sort({age:1})
   ```
   - The first query finds male individuals in the "person" collection and sorts them by age in descending order.
   - The second query finds individuals with either gender and sorts them by age in ascending order.

3. **Aggregation Operations on the "person" Collection:**
   ```javascript
   db.person.aggregate([
     {$group:{_id:null,Tcount:{$sum:1}}},
     {$project:{_id:0}}
   ])

   db.person.aggregate([
     {
       "$facet": {
         "males": [
           // ... (pipeline to match and project male individuals)
         ],
         "females": [
           // ... (pipeline to match and project female individuals)
         ]
       }
     }
   ])
   ```
   - The first aggregation calculates the total count of documents in the "person" collection.
   - The second aggregation uses the `$facet` stage to separate male and female individuals into distinct arrays.

4. **Commented-out Code:**
   ```javascript
   //db.employees.aggregate([{  $match:{   empId: { $gt: 2 }  } } ])
   //db.employees.find({dept: 'Sales'});
   //db.employees.aggregate([{  $match:{    empId:1  } } ])
   ```
   - These lines are commented-out queries related to the "employees" collection, including filtering by employee ID and department.

In summary, the provided MongoDB code demonstrates the insertion of documents, querying and sorting based on specific criteria, and performing aggregation operations on a collection of personal information.

The given MongoDB aggregation pipeline utilizes the `$facet` stage to categorize documents in the "person" collection into separate arrays for males and females. However, the content within the arrays is currently empty, as indicated by the absence of pipeline stages within each facet.

To make this aggregation meaningful, you should include appropriate stages within the "males" and "females" arrays to filter and project the desired information. Here's an example of how you could structure the aggregation pipeline for this purpose:

```javascript
db.person.aggregate([
  {
    "$facet": {
      "males": [
        { "$match": { "gender": "Male" } },
        { "$project": { "_id": 0, "id": 1, "name": 1, "age": 1, "address": 1, "email": 1 } }
      ],
      "females": [
        { "$match": { "gender": "Female" } },
        { "$project": { "_id": 0, "id": 1, "name": 1, "age": 1, "address": 1, "email": 1 } }
      ]
    }
  }
])
```

In this example:

- For the "males" array, the `$match` stage filters documents with the "gender" field set to "Male," and the `$project` stage specifies which fields to include in the output.
- Similarly, for the "females" array, the `$match` stage filters documents with the "gender" field set to "Female," and the `$project` stage defines the fields to include.

Adjust the projection fields based on your specific requirements. This modified aggregation pipeline will provide meaningful information about males and females in separate arrays.