Project 2
1.
MongoDB Ecommerce Database
Customer Collection
{
"_id": ObjectId("5f2049fa78e7164ef8e8d13f"),
"name": "John Doe",
"email": "[email protected]",
"address": "123 Main Street",
"city": "New York",
"state": "NY",
"zip": "10001",
"country": "USA",
"type": "normal"
}
Product Collection
{
"_id": ObjectId("5f2049fa78e7164ef8e8d140"),
"name": "Product 1",
"price": 100,
"categories": [
ObjectId("5f1fcc4b78e7164ef8e8d12f"),
ObjectId("5f1fcc4b78e7164ef8e8d12e")
],
"brand": ObjectId("5f1fcc4b78e7164ef8e8d12d"),
"segments": [
ObjectId("5f1fcc4b78e7164ef8e8d12c")
],
"tags": [
ObjectId("5f1fcc4b78e7164ef8e8d12b")
]
}
Category Collection
{
"_id": ObjectId("5f1fcc4b78e7164ef8e8d12f"),
"name": "Category 1",
"parent": null
}
Brand Collection
{
"_id": ObjectId("5f1fcc4b78e7164ef8e8d12d"),
"name": "Brand 1"
}
Segment Collection
{
"_id": ObjectId("5f1fcc4b78e7164ef8e8d12c"),
"name": "Segment 1"
}
Tag Collection
{
"_id": ObjectId("5f1fcc4b78e7164ef8e8d12b"),
"name": "Tag 1"
}
Admin Collection
{
"_id": ObjectId("5f1fcc4b78e7164ef8e8d12a"),
"name": "Admin 1",
"email": "[email protected]",
"password": "123456"
}
Seller Collection
{
"_id": ObjectId("5f1fcc4b78e7164ef8e8d129"),
"name": "Seller 1",
"email": "[email protected]",
"password": "123456"
}
Cart Collection
{
"_id": ObjectId("5f1fcc4b78e7164ef8e8d128"),
"customer": ObjectId("5f1fcc4b78e7164ef8e8d127"),
"products": [
ObjectId("5f1fcc4b78e7164ef8e8d126")
],
"total": 100
}
Order Collection
{
"_id": ObjectId("5f1fcc4b78e7164ef8e8d125"),
"customer": ObjectId("5f1fcc4b78e7164ef8e8d124"),
"products": [
ObjectId("5f1fcc4b78e7164ef8e8d123")
],
"total": 100,
"billing_address": "123 Main Street",
"billing_city": "New York",
"billing_state": "NY",
"billing_zip": "10001",
"billing_country": "USA",
"shipping_address": "123 Main Street",
"shipping_city": "New York",
"shipping_state": "NY",
"shipping_zip": "10001",
"shipping_country": "USA",
"status": "pending"
}
Queries:
1. db.orders.aggregate([
{
$group: {
_id: null,
avgValue: { $avg: "$total" }
}
}
])
2. db.orders.aggregate([
{
$match: {
createdAt: { $gte: new Date((new Date().getFullYear()-2), new Date().getMonth(), new Date().getDate()) }
}
},
{
$lookup: {
from: "customers",
localField: "customer_id",
foreignField: "_id",
as: "customer"
}
},
{
$unwind: "$customer"
},
{
$group: {
_id: "$customer._id",
totalValue: { $sum: "$total" }
}
},
{
$sort: { totalValue: -1 }
},
{
$limit: 10
}
])
3. db.orders.aggregate([
{
$match: {
createdAt: { $gte: new Date((new Date().getFullYear()), new Date().getMonth(), new Date().getDate()) }
}
},
{
$group: {
_id: null,
totalValue: { $sum: "$total" }
}
}
])
4. db.customers.aggregate([
{
$group: {
_id: "$city",
count: { $sum: 1 }
}
}
])
5. db.orders.aggregate([
{
$lookup: {
from: "sellers",
localField: "seller_id",
foreignField: "_id",
as: "seller"
}
},
{
$unwind: "$seller"
},
{
$match: {
"customer.city": "$seller.city"
}
},
{
$group: {
_id: "$seller._id",
count: { $sum: 1 }
}
},
{
$sort: { count: -1 }
}
])