MongoDB Practice : The E-Commerce Store

The Database Schema: ECommerceDB

Our database consists of three collections designed to work together:

• Users: Stores customer information. The user's address is embedded directly as an object, a common pattern in MongoDB for tightly related data.

• Products: Contains details about each product, including price, stock status, and descriptive tags in an array.

• Orders: Tracks customer orders. It links to Users via userId.

Setup: Populating the Database

Creating the collections and inserting sample data using insertOne or insertMany

Open mongosh and run the following commands.

// Switch to (or create) the database
use ECommerceDB;

// --- Insert Users ---
db.Users.insertMany([
  { _id: 1001, 
	  name: "Alice Johnson", 
	  email: "alice@example.com", 
	  address: { 
		  street: "123 Park Ave", 
		  city: "New York", zip: "10001" } 
	  },
  
  { _id: 1002, 
	  name: "Bob Smith", 
	  email: "bob@example.com", 
	  address: { 
		  street: "456 Oak St", 
		  city: "Chicago", zip: "60601" } 
	  },
  
  { _id: 1003, 
	  name: "Charlie Green", 
	  email: "charlie@example.com", 
	  address: { 
		  street: "789 Pine Ln", 
		  city: "Los Angeles", 
		  zip: "90001" } 
	  },

  { _id: 1004, 
	  name: "Diana Rose", 
	  email: "diana@example.com", 
	  address: { 
		  street: "234 Maple Rd", 
		  city: "New York", zip: "10002" } 
	  },
  
  { _id: 1005, 
	  name: "Ethan Black", 
	  email: "ethan@example.com", 
	  address: { 
		  street: "101 Birch Blvd", 
		  city: "Dallas", zip: "75201" } 
	  }
]);

// --- Insert Products ---
db.Products.insertMany([
  { _id: 2001, 
	  name: "Wireless Mouse", 
	  category: "Electronics", 
	  price: 899, inStock: true, 
	  tags: ["gadget", "tech", "office"], 
	  rating: 4.6 },
  
  { _id: 2002, 
	  name: "Laptop Stand", 
	  category: "Accessories", 
	  price: 1499, inStock: true, 
	  tags: ["laptop", "accessories", "office"], 
	  rating: 3.9 },
  
  { _id: 2003, 
	  name: "Bluetooth Headphones", 
	  category: "Electronics", 
	  price: 2999, inStock: false, 
	  tags: ["gadget", "audio", "tech"], 
	  rating: 4.1 },
  
  { _id: 2004, 
	  name: "Hardcover Notebook", 
	  category: "Stationery", 
	  price: 199, inStock: true, 
	  tags: ["stationery", "book", "office"], 
	  rating: 4.8 },
  
  { _id: 2005, 
	  name: "Stainless Steel Water Bottle", 
	  category: "Home", 
	  price: 499, inStock: true, 
	  tags: ["bottle", "kitchen", "eco-friendly"], 
	  rating: 4.2 },
  
  { _id: 2006, 
	  name: "Ergonomic Mouse", 
	  category: "Electronics", 
	  price: 699, inStock: true, 
	  tags: ["gadget", "tech", "ergonomic"], 
	  rating: 4.8 }
]);

// --- Insert Orders ---
db.Orders.insertMany([
  { _id: 4001, userId: 1001, 
	  items: [
	  { productId: 2001, quantity: 1, priceAtOrderTime: 899 }, 
	  { productId: 2002, quantity: 1, priceAtOrderTime: 1499 }
	  ], 
	  totalAmount: 2398, status: "Shipped", 
	  orderDate: new Date("2024-05-10") },
	  
  { _id: 4002, userId: 1002, 
	  items: [
	  { productId: 2003, quantity: 1, priceAtOrderTime: 2999 }, 
	  { productId: 2004, quantity: 2, priceAtOrderTime: 199 }
	  ], 
	  totalAmount: 3397, status: "Pending", 
	  orderDate: new Date("2024-05-12") },
  
  { _id: 4003, userId: 1003, 
	  items: [
	  { productId: 2005, quantity: 3, priceAtOrderTime: 499 }, 
	  { productId: 2001, quantity: 2, priceAtOrderTime: 899 }
	  ], 
	  totalAmount: 3295, status: "Delivered", 
	  orderDate: new Date("2024-05-11") },
  
  { _id: 4004, userId: 1004, 
	  items: [
	  { productId: 2002, quantity: 1, priceAtOrderTime: 1499 }, 
	  { productId: 2003, quantity: 1, priceAtOrderTime: 2999 }
	  ], 
	  totalAmount: 4498, status: "Delivered", 
	  orderDate: new Date("2024-05-13") },
  
  { _id: 4005, userId: 1005, 
	  items: [
	  { productId: 2004, quantity: 3, priceAtOrderTime: 199 }, 
	  { productId: 2005, quantity: 1, priceAtOrderTime: 499 }
	  ], 
	  totalAmount: 1096, status: "Shipped", 
	  orderDate: new Date("2024-05-14") }
]);

CRUD Operations

Part A: Reading and Querying Data (Read)

1. Find all products in the "Electronics" category.

db.Products.find({ category: "Electronics" });

2. Find products that are cheaper than ₹1000.

db.Products.find({ price: { $lt: 1000 } });

3. Find products that are highly rated (rating > 4.5) and are in stock.

db.Products.find({ 
			rating: { $gt: 4.5 }, 
			inStock: true 
			});

• This query combines two conditions. MongoDB implicitly uses a logical AND when you provide multiple fields in the filter document.

4. Find all products that have an "office" tag.

db.Products.find({ tags: "office" });

• When querying an array, MongoDB will match if the value exists anywhere within that array.

5. Find all products with a rating of exactly 4.8 and show only their name and price.

db.Products.find(
		{ rating: 4.8 }, 
		{ name: 1, price: 1, _id: 0 }
	);

• This introduces projection. The second object in the find method specifies which fields to return (1 for include, 0 for exclude). We explicitly exclude _id.

6. Find products that are either out of stock OR have a rating below 4.0.

db.Products.find({
  $or: [
    { inStock: false },
    { rating: { $lt: 4.0 } }
  ]
});

• For logical OR conditions, we use the $or operator, which takes an array of filter documents.

Part B: Updating Documents (Update)

1. A price correction: Update the "Wireless Mouse" (ID 2001) to have a price of ₹949.

db.Products.updateOne(
  { _id: 2001 },
  { $set: { price: 949 } }
);

• updateOne targets a single document. We use the $set operator to modify the price field without affecting any other part of the document.

2. A sale is on! Reduce the price of all "Electronics" by 10%.

db.Products.updateMany(
  { category: "Electronics" },
  { $mul: { price: 0.90 } } 
  // Multiplies the current price by 0.90
);

• updateMany affects all documents matching the filter. The $mul operator is perfect for percentage-based updates.

3. Add a new "sale" tag to all products cheaper than ₹1500.

db.Products.updateMany(
  { price: { $lt: 1500 } },
  { $addToSet: { tags: "sale" } }
);

• $addToSet is used to add an element to an array, but only if it's not already present. This prevents duplicate tags.

Part C: Deleting Documents (Delete)

1. The "Bluetooth Headphones" (ID 2003) have been discontinued.

db.Products.deleteOne({ _id: 2003 });

• deleteOne is a precise operation to remove a single document based on a filter.

2. Clear out all products that are out of stock.

db.Products.deleteMany({ inStock: false });

• deleteMany is used for bulk deletion. In a real application, you might archive these products instead, but this demonstrates the operation.

Advanced Query: Joining Collections with $lookup

Business Question: "Get a list of all orders, but also include the full name and email of the user who placed each order."

db.Orders.aggregate([
  {
    $lookup: {
      from: "Users",       
      // The collection to join with
      
      localField: "userId",   
      // The field from the input documents (Orders)
      
      foreignField: "_id",        
      // The field from the documents of the "from" collection (Users)
      as: "userDetails"         
      // The name for the new array field to add
    }
  },
  {
    $unwind: "$userDetails" 
    // Deconstruct the userDetails array
  },
  {
    $project: { // Reshape the output
      _id: 1,
      totalAmount: 1,
      status: 1,
      orderDate: 1,
      "customerName": "$userDetails.name",
      "customerEmail": "$userDetails.email"
    }
  }
]);

1. $lookup performs a left outer join, pulling in data from the Users collection where Orders.userId matches Users._id.
2. $unwind is used to deconstruct the userDetails array created by $lookup, creating a separate document for each order-user pair.
3. $project cleans up the final output to present a clear, readable result.