Project 2
To design an ecommerce database in MongoDB with the specifications provided, we need to create the following collections:
- Customers
- Products
- Categories
- Brands
- Segments
- Tags
- Orders
- Sellers
Let's create the collections one by one along with their fields:
- Customers collection:
javascript
db.createCollection("Customers")
db.Customers.insertOne({
firstName: "John",
lastName: "Doe",
email: "[email protected]",
password: "password123",
type: "premium",
address: {
street: "123 Main St",
city: "New York",
state: "NY",
zip: "10001"
}
})
Fields in Customers collection:
- firstName (string)
- lastName (string)
- email (string)
- password (string)
- type (string): either "normal" or "premium"
- address (embedded document):
- street (string)
- city (string)
- state (string)
- zip (string)
- Products collection:
javascript
db.createCollection("Products")
db.Products.insertOne({
name: "Product 1",
price: 9.99,
description: "This is a sample product.",
categories: [
{ name: "Category 1", subcategories: [{ name: "Subcategory 1" }] },
{ name: "Category 2" }
],
brands: ["Brand 1", "Brand 2"],
segments: ["Segment 1", "Segment 2"],
tags: ["Tag 1", "Tag 2"]
})
Fields in Products collection:
- name (string)
- price (number)
- description (string)
- categories (array of embedded documents):
- name (string)
- subcategories (array of embedded documents):
- name (string)
- brands (array of strings)
- segments (array of strings)
- tags (array of strings)
- Categories collection:
javascript
db.createCollection("Categories")
db.Categories.insertMany([
{
name: "Category 1",
subcategories: [{ name: "Subcategory 1" }, { name: "Subcategory 2" }]
},
{ name: "Category 2" }
])
Fields in Categories collection:
- name (string)
- subcategories (array of embedded documents):
- name (string)
- Brands collection:
javascript
db.createCollection("Brands")
db.Brands.insertMany(["Brand 1", "Brand 2", "Brand 3"])
Fields in Brands collection:
- value (string)
- Segments collection:
javascript
db.createCollection("Segments")
db.Segments.insertMany(["Segment 1", "Segment 2", "Segment 3"])
Fields in Segments collection:
- value (string)
- Tags collection:
javascript
db.createCollection("Tags")
db.Tags.insertMany(["Tag 1", "Tag 2", "Tag 3"])
Fields in Tags collection:
- value (string)
- Orders collection:
javascript
db.createCollection("Orders")
db.Orders.insertOne({
customerId: ObjectId("614cf56df34c10f6ad1fe306"),
products: [
{ productId: ObjectId("614cf5b8f34c10f6ad1fe308"), quantity: 2 },
{ productId: ObjectId("614cf5cef34c10f6ad1fe30a"), quantity: 1 }
],
status: "pending",
shipping: {
Here are the simple queries that can be performed on the ecommerce database:
- Find the average order value for all Customers:
javascript
db.Orders.aggregate([
{
$group: {
_id: "$customerId",
orderTotal: { $sum: { $multiply: ["$products.quantity", "$products.price"] } }
}
},
{
$group: {
_id: null,
avgOrderValue: { $avg: "$orderTotal" }
}
}
])
- Get top 10 customers whose orders have the highest value and who have been customers since less than 2 months:
javascript
db.Orders.aggregate([
{
$match: {
createdAt: { $gte: new Date(Date.now() - 60 * 60 * 24 * 60 * 1000) }
}
},
{
$group: {
_id: "$customerId",
orderTotal: { $sum: { $multiply: ["$products.quantity", "$products.price"] } }
}
},
{
$lookup: {
from: "Customers",
localField: "_id",
foreignField: "_id",
as: "customer"
}
},
{
$addFields: {
customer: { $arrayElemAt: ["$customer", 0] }
}
},
{
$match: {
"customer.createdAt": { $gte: new Date(Date.now() - 60 * 60 * 24 * 60 * 1000 * 60) }
}
},
{
$sort: {
orderTotal: -1
}
},
{
$limit: 10
}
])
- Find the sum of all orders received in this month:
javascript
db.Orders.aggregate([
{
$match: {
createdAt: { $gte: new Date(new Date().getFullYear(), new Date().getMonth(), 1) }
}
},
{
$group: {
_id: null,
total: { $sum: { $multiply: ["$products.quantity", "$products.price"] } }
}
}
])
- Find customers count in various cities, group them by names of cities:
javascript
db.Customers.aggregate([
{
$group: {
_id: "$address.city",
count: { $sum: 1 }
}
}
])
- Get all the sellers who have base in the same city in which the customers who ordered their products. Order them in the order of highest number of products sold to customers in same city to lowest:
javascript
db.Orders.aggregate([
{
$lookup: {
from: "Sellers",
localField: "products.sellerId",
foreignField: "_id",
as: "seller"
}
},
{
$unwind: "$seller"
},
{
$lookup: {
from: "Customers",
localField: "customerId",
foreignField: "_id",
as: "customer"
}
},
{
$unwind: "$customer"
},
{
$match: {
"seller.address.city": "$customer.address.city"
}
},
{
$group: {
_id: "$seller._id",
seller: { $first: "$seller" },
productsSold: { $sum: { $size: "$products" } }
}
},
{
$sort: {
productsSold: -1
}
}
])