Project 2

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 }
}
])