Project 2

To design the ecommerce database in MongoDB with the given specifications, we can create the following collections:

1. Customers Collection
2. Products Collection
3. Categories Collection
4. Brands Collection
5. Segments Collection
6. Tags Collection
7. Orders Collection
8. Sellers Collection

customers collection

{
"_id": ObjectId("..."),
"firstName": "John",
"lastName": "Doe",
"email": "[email protected]",
"password": "password",
"type": "normal",
"address": {
"street": "123 Main St",
"city": "Anytown",
"state": "CA",
"zip": "12345"
}
}

Products collection

{
"_id": ObjectId("..."),
"name": "Product A",
"description": "Product A description",
"price": 10.99,
"categories": [
"Category A",
"Subcategory A1"
],
"brands": [
"Brand A"
],
"segments": [
"Segment A"
],
"tags": [
"Tag A",
"Tag B"
]
}

Categories

{
"_id": ObjectId("..."),
"name": "Category A",
"subcategories": [
"Subcategory A1",
"Subcategory A2"
]
}

Brands

{
"_id": ObjectId("..."),
"name": "Brand A"
}

Segments

{
"_id": ObjectId("..."),
"name": "Segment A"
}

Tags

{
"_id": ObjectId("..."),
"name": "Tag A"
},
{
"_id": ObjectId("..."),
"name": "Tag B"
}

Orders

{
"_id": ObjectId("..."),
"customerId": ObjectId("..."),
"items": [
{
"productId": ObjectId("..."),
"quantity": 2
},
{
"productId": ObjectId("..."),
"quantity": 1
}
],
"total": 30.97,
"billingInfo": {
"name": "John Doe",
"address": {
"street": "123 Main St",
"city": "Anytown",
"state": "CA",
"zip": "12345"
},
"cardNumber": "1234 5678 9012 3456",
"expirationDate": "01/25",
"cvv": "123"
},
"shippingInfo": {
"name": "Jane Doe",
"address": {
"street": "456 Main St",
"city": "Anytown",
"state": "CA",
"zip": "12345"
},
"trackingNumber": "123456789"
},
"status": "shipped"
}

Sellers

{
"_id": ObjectId("..."),
"firstName": "Jane",
"lastName": "Doe",
"email": "[email protected]",
"password": "password",
"address": {
"street": "456 Main St",
"city": "Anytown",
"state": "CA",
"zip": "12345"
}
}

1.

db.orders.aggregate([
{
$group: {
_id: null,
avgOrderValue: { $avg: "$total" }
}
}
]);

2.

db.customers.aggregate([
{
$lookup: {
from: "orders",
localField: "_id",
foreignField: "customerId",
as: "orders"
}
},
{
$match: {
"orders.date": { $gte: ISODate("2023-01-10T00:00:00Z") }
}
},
{
$addFields: {
totalOrdersValue: { $sum: "$orders.total" },
customerSince: { $subtract: [ISODate("2023-03-10T00:00:00Z"), "$createdAt"] }
}
},
{
$match: {
customerSince: { $lt: 5184000000 } // 60 days in milliseconds
}
},
{
$sort: {
totalOrdersValue: -1
}
},
{
$limit: 10
},
{
$project: {
_id: 1,
firstName: 1,
lastName: 1,
totalOrdersValue: 1
}
}
]);

3.

db.orders.aggregate([
{
$match: {
date: {
$gte: ISODate("2023-03-01T00:00:00Z"),
$lt: ISODate("2023-04-01T00:00:00Z")
}
}
},
{
$group: {
_id: null,
totalOrdersValue: { $sum: "$total" }
}
}
]);

4.

db.customers.aggregate([
{
$group: {
_id: "$address.city",
customerCount: { $sum: 1 }
}
}
]);

5.

db.orders.aggregate([
{
$lookup: {
from: "sellers",
localField: "sellerId",
foreignField: "_id",
as: "seller"
}
},
{
$unwind: "$seller"
},
{
$lookup: {
from: "customers