Mongodb query

All CRUD operations with example

1. Create (Insert):

Use the insertOne(document) or insertMany(documents) method to insert one or multiple documents into a collection.

// Insert one document
db.users.insertOne({
    name: "Alice",
    age: 30,
    email: "alice@example.com"
});

// Insert multiple documents
db.users.insertMany([
    { name: "Bob", age: 25, email: "bob@example.com" },
    { name: "Charlie", age: 35, email: "charlie@example.com" }
]);

2. Read (Query):

Use the find(query, projection) method to query documents in a collection.

// Find all documents in the users collection
db.users.find();

// Find documents with age greater than 25
db.users.find({ age: { $gt: 25 } });

// Find documents with age greater than 25 and project only name and email fields
db.users.find({ age: { $gt: 25 } }, { name: 1, email: 1 });

MongoDB provides a flexible query language supporting various operators such as $eq, $gt, $lt, $in, $and, $or, etc.

3. Update:

Use the updateOne(filter, update, options) or updateMany(filter, update, options) method to update one or multiple documents in a collection.

// Update one document
db.users.updateOne(
    { name: "Alice" },
    { $set: { age: 31 } },
    { new: true }
);

// Update multiple documents
db.users.updateMany(
    { age: { $lt: 30 } },
    { $set: { status: "inactive" } },
    { new: true }
);
// here {new: true} option you can pass if you want updated object return

Update operations can use different modifiers like $set, $unset, $inc, $push, $addToSet, etc. Ex: { $inc: {balance: 5000}}

4. Delete:

Use the deleteOne(filter) or deleteMany(filter) method to delete one or multiple documents from a collection.

// Delete one document
db.users.deleteOne({ name: "Alice" });

// Delete multiple documents
db.users.deleteMany({ age: { $lt: 25 } });

Mongodb Aggregation

• MongoDB’s aggregation framework allows for data transformation and analysis by processing documents in a collection through a pipeline of stages.
• Each stage performs a specific operation on the input documents and passes the results to the next stage.

We will go through each methods first with one-one example, and after that we will go deeper.

Stages in Aggregation Pipeline:

$match:

• Filters the documents to pass only those that match the specified condition.
• Scenario: You want to find all the users who are older than 30.

db.users.aggregate([
    { $match: { age: { $gt: 30 } } }
]);

$project

• Reshapes documents by including, excluding, or renaming fields.
• Scenario: You want to retrieve only the name and email fields of users.

db.users.aggregate([
    { $project: { name: 1, email: 1 } }
]);

$group

• Groups documents by a specified identifier expression and applies accumulator expressions to each group
• Scenario: You want to find the total sales amount for each product category.

db.sales.aggregate([
    { $group: { 
           _id: "$productCategory", 
            totalSales: { $sum: "$amount" } 
           } 
    }
]);

$sort

Scenario: You want to find the top 5 highest-paid employees.

db.employees.aggregate([
    { $sort: { salary: -1 } },
    { $limit: 5 }
]);

$limit & $skip

Scenario: You want to paginate through a list of products, displaying 10 products per page and skipping the first 10.

db.products.aggregate([
    { $skip: 10 },
    { $limit: 10 }
]);

$unwind

• Deconstructs an array field from the input documents and outputs one document for each element of the array.
• Scenario: You want to find the average rating of products across all reviews.

db.reviews.aggregate([
    { $unwind: "$ratings" },
    { $group: { _id: null, averageRating: { $avg: "$ratings" } } }
]);

$lookup

• Performs a left outer join to another collection in the same database to combine data from multiple collections.
• Scenario: You want to find all the orders along with the corresponding customer information.

db.orders.aggregate([
    {
        $lookup: {
            from: "customers",
            localField: "customerId",
            foreignField: "_id",
            as: "customerInfo"
        }
    }
]);

Example 1

db.sales.aggregate([
    { $match: { status: "completed" } },
    { $unwind: "$items" },
    { $group: { _id: "$items.product", totalQuantity: { $sum: "$items.quantity" } } },
    { $sort: { totalQuantity: -1 } },
    { $limit: 5 }
]);

This aggregation pipeline filters completed sales, unwinds the items array, groups by product, calculates the total quantity sold for each product, sorts the results by total quantity in descending order, and finally limits the output to the top 5 products.

Q1. Find the total number of items availabe for each category in a product collection

db.products.aggregate([
    {
        $group: {
            _id: "$category",
            totalItems: { $sum: "$quantity" }
        }
    }
]);

Q2. Find top 10 hieghest rating products list based on search query

db.products.aggregate([
    {
        $match: { $text: { $search: "search_query" } } // Replace "search_query" with your search query
    },
    {
        $sort: { rating: -1 }
    },
    {
        $limit: 10
    }
]);

Q3. I have list of transactions having categories (sopping/food/travel etc), payment source (banks), payment mode ( paytm, phonepe, debit card, credit card, cash ).
Retrieve all transactions based on a date range, specific categories, payment sources, and payment modes

db.transactions.aggregate([
  {
    $match: {
      date: {
        $gte: ISODate("start_date"),
        $lte: ISODate("end_date")
      },
      category: { $in: ["shopping", "food"] },
      payment_source: { $in: ["bank1", "bank2"] },
      payment_mode: { $in: ["paytm", "debit card"] }
    }
  }
]);

Q4. Search products by name ( case-insensitive)

Product.find({ name: { $regex: new RegExp(productName, "i") } }

// use regex for case-insensitive search result

Q5. Reffered to Q3, retrieve all transactions based on search by title, date range, specific categories, payment sources, and payment modes

db.transactions.aggregate([
  {
    $match: {
      title: { $regex: "Your search title", $options: "i" }, // Search by title (case-insensitive)
      date: {
        $gte: ISODate("start_date"), // Date range start
        $lte: ISODate("end_date")   // Date range end
      },
      category: { $in: ["shopping", "food"] }, // Filter by categories
      payment_source: { $in: ["bank1", "bank2"] }, // Filter by payment sources
      payment_mode: { $in: ["paytm", "debit card"] } // Filter by payment modes
    }
  }
]);