Project 2
db.customer.insert ({
"_id": "1",
"name": "John Doe",
"email": "[email protected]",
"address": "123 Main Street",
"city": "New York",
"state": "NY",
"zip": "10001",
"country": "USA",
"type": "normal"
});
db.customer.insert ({
"_id": "4",
"name": "John smith",
"email": "[email protected]",
"address": "123 Main Street",
"city": "New York",
"state": "NY",
"zip": "10001",
"country": "USA",
"type": "normal"
});
db.product.insert({
"_id": "1",
"name": "Product 1",
"price": 100,
"categories": [
"1",
"2"
],
"brand": "1",
"segments": [
"1"
],
"tags": [
"1"
]
});
db.category.insert({
"_id": "1",
"name": "Category 1",
"parent": null
});
db.brand.insert({
"_id": "1",
"name": "Brand 1"
});
db.segment.insert({
"_id": "1",
"name": "Segment 1"
});
db.tag.insert({
"_id": "1",
"name": "Tag 1"
});
db.admin.insert({
"_id": "1",
"name": "Admin 1",
"email": "[email protected]",
"password": "123456"
});
db.seller.insert(
{
"_id": "1",
"name": "Seller 1",
"email": "[email protected]",
"password": "123456",
"city": "California",
});
db.seller.insert(
{
"_id": "2",
"name": "Seller 2",
"email": "[email protected]",
"password": "123456",
"city": "New York",
});
db.cart.insert({
"_id": "1",
"customer": "1",
"products": [
"123"
],
"total": 100
});
db.order.insertOne({
"_id": "20",
"customer": "2",
"products": [
"125"
],
"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",
"Date": new Date("2020-05-18T14:10:30Z")
});
db.order.insertOne({
"_id": "18",
"customer": "4",
"products": [
"123"
],
"total": 500,
"billing_address": "125 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",
"Date": new ISODate("2022-11-18T14:10:30Z")
});
Queries:
1.db.order.aggregate([ { $group: { _id: null, avgValue: { $avg: "$total" } } } ]) ;
- 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 } ]) ;
- db.order.aggregate([ { $match: { createdAt: { $gte: new Date((new Date().getFullYear()), new Date().getMonth(), new Date().getDate()) } } }, { $group: { _id: null, totalValue: { $sum: "$total" } } } ]);
- db.customer.aggregate([ { $group: { _id: "$city", count: { $sum: 1 } } } ]) ;
- db.order.aggregate([ { $lookup: { from: "seller", localField: "_id", foreignField: "_id", as: "seller" } }, { $unwind: "$seller" }, { $match: { "customer.city": "$seller.city" } }, { $group: { _id: "$seller._id", count: { $sum: 1 } } }, { $sort: { count: -1 } } ]);