Project 2
1) use Ecommerce
db.customers.insertMany([
{
"_id": "111',
"name": "John Doe",
"email": "[email protected]",
"address": "123 Main Street",
"city": "New York",
"state": "NY",
"zip": "10001",
"country": "USA",
"type": "normal"
},
{
"_id": "222',
"name": "Jessica",
"email": "[email protected]",
"address": "223 Main Street",
"city": "New Yor",
"state": "NY",
"zip": "10001",
"country": "USA",
"type": "premium"
}
)];
db.products.insertMany([
{
"_id": "1",
"name": "Laptop",
"price": 10000,
"categories":"Computers",
"brand": "HP",
"segments": ['Computers', 'Laptops']
},
{
"_id": "2",
"name": "smart phone",
"price": 20000,
"categories":"Computers",
"brand": "HP",
"segments": ['Computers', 'Laptops']
},
{
"_id":3,
"name": 'Amazing spiderman',
"price": 100,
"segments": ['Book', 'Novels'],
"categroy": 'Books'
}
"_id":3,
"name": 'Amazing spiderman',
"price": 100,
"segments": ['Book', 'Novels'],
"categroy": 'Books'
}
]);
db.categories.insertMany([
{
"_id":1",
"categroy": 'Home Appliances',
"sub_categories": ['A.C', 'Fridge', 'Oven']
},
{
"_id":2,
"categroy": 'Computers, Laptop and Mobiles',
"sub_categories": ['Computers', 'Laptops', 'Mobiles']
},
{
"_id":3,
"categroy": 'Fasion',
"sub_categroy": ['mens', 'womens']
},
)];
db.brand.insertMany([
{
"_id": 1,
"name": "Hp"
},
{
"_id": 2,
"name": "Lg"
}
)];
db.segment.insertMany([
{
"_id": 1,
"name": ['Computers', 'Laptops']
},
{
"_id": 2,
"name": ['Book', 'Novels']
}
)];
db.admin.insert([
{
"_id": ObjectId("5f1fcc4b78e7164ef8e8d12a"),
"name": "Admin 1",
"email": "[email protected]",
"password": "123456"
}
)];
db.sellers.insertMany([
{
"_id": 1,
"name": "Raj",
"email": "[email protected]",
"password": "123456"
},
db.cart.insertMany([
{
"_id": 1,
"customer_id": 111,
"products_id": 1,
"total": 1000
}
{
"_id": 2,
"customer": 111,
"products": 2,
"total": 100
}
)];
db.order.insertMany([
{
"_id": 1,
"customer": 111,
"products": 1,
"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"
},
{
"_id": 2,
"customer": 222,
"products": 2,
"total": 1000,
"billing_address": "123 Main Street",
"billing_city": "Chennai",
"billing_state": "Tamilnadu",
"billing_zip": "516000",
"billing_country": "India",
"shipping_address": "123 Main Street",
"shipping_city": "Chennai",
"shipping_state": "Tamilnadu",
"shipping_zip": "516003",
"shipping_country": "India",
"status": "pending"
}
)];
Answers:-
1)db.products.aggregate([{$group:{_id:'price', avg:{$avg:'$price}}}]);
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 }}])