Project 2
db.products.insertMany([{
... sku: "111445GB3",
... title: "Samsung One mobile phone",
... description: "The greatest Anddroid phone on the market .....",
...
... manufacture_details: {
... model_number: "A123X",
... release_date: new ISODate("2012-05-17T08:14:15.656Z")
... },
...
... shipping_details: {
... weight: 350,
... width: 10,
... height: 10,
... depth: 1
... },
...
... quantity: 99,
...
... pricing: {
... price: 1000
... }
... }]);
db.products.update({sku: "111445GB3"}, {$set: { categories: ['mobile/15G', 'mobile/fm'] }});
db.products.find({categories: /^mobile\/fm/})
db.categories.insert({title: "Mobiles containing a FM radio", parent: "mobile", path: "mobile/fm"})
db.categories.insert({title: "Mobiles with 15G support", parent: "mobile", path: "mobile/15G"})
db.categories.ensureIndex({parent: 1, path: 1})
db.categories.ensureIndex({path: 1})
db.categories.find({parent: /^mobile/}, {_id: 0, path: 1})
db.Carts.insertMany([{
_id: "the_users_session_id",
status:'active',
quantity: 2,
total: 2000,
products: []}]);
db.products.update({
sku: "111445GB3", quantity: {$gte: 1}
}, {
$inc: {quantity: -1},
$push: {
in_carts: {
quantity:1, id: "the_users_session_id", timestamp: new ISODate()
}
}
})
db.inventory.insertOne( {
item: "journal",
price: 9.99,
qty: 25,
size: { h: 14, l: 21, w: 1 },
features: "Beautiful, handmade journal.",
categories: ["writing", "bestseller"],
image: "items/journal.jpg"
} )
db.inventory.updateOne(
{ _id: ObjectId("600e814359ba901629a14e13") },
{
$unset: {image: 1, size: 1, qty: 1, price: 1},
$set : {
item: "journal",
features: ["Inner pocket", "Durable cover"],
skus: [ {
sku: "154A",
price: {
base: NumberDecimal(9.99),
currency: "USD"
},
quantity: 20,
options: {
size: { h: 14, l: 21, w: 1 },
features: ["72 sheets of premium lined paper"],
colors: ["brown", "red"],
ruling: "wide",
image: "images/journal1.jpg"
}
}, {
sku: "154B",
price: {
base: NumberDecimal(14.99),
currency: "USD",
discount: NumberDecimal(4.00)
},
quantity: 15,
options: {
size: { h: 18, l: 22, w: 2 },
features: ["140 sheets of premium paper"],
colors: ["brown"],
ruling: "unlined",
image: "images/journals.jpg"
}
} ]
}
}
)
db.customers.createIndex(
{ email: 1 },
{ unique: true }
)
db.Customers.insertOne( {
_id: "journalfanatic@e/mail.com",
fname: "Journal",
lname: "Fanatic",
hashedAndSaltedPassword: "$1$O3JMY.Tw$AdLnLjQ/5jXF9.MTp3gHv/",
emailVerified: false,
address: {
country: "United States",
street1: "99 Main Street",
street2: "Apt #3",
city: "Boston",
state: "MA",
zip: "74586"
}
} )
db.Customers.updateOne(
{ _id: "journalfanatic@e/mail.com" },
{ $set: {
"address.zip": "60601",
shippingAddress: {
street1: "50 Work Street",
street2: "Floor 16",
city: "Chicago",
state: "IL",
zip: "60601" } },
$setOnInsert: { dateModified: new Date() }
}
)
db.reset.insertOne({
customerId: "journalfanatic@e/mail.com",
token: "493654",
status: "requested",
expiration: new Date(ISODate().getTime() + (1000 * 60 * 30)),
requestTime: new Date()
} )
db.payments.insertOne( {
customerId: "journalfanatic@e/mail.com",
status: "verified",
gateway: "stripe",
type: "credit",
amount: NumberDecimal(1.00),
card: {
brand: "Visa",
pan: "4242424242424242",
expirationMonth: 1,
expirationYear: 2090,
cvv: 123
}
} )
db.payments.remove( { } )
db.payments.insertOne( {
customerId: "journalfanatic@e/mail.com",
status: "verified",
gateway: "stripe",
type: "credit",
amount: NumberDecimal(1.00),
card: {
brand: "Visa",
panLastFour: "4242",
expirationMonth: 1,
expirationYear: 2090,
cvvVerified: true
}
} )
db.payments.insertOne( {
customerId: "journalfanatic@e/mail.com",
status: "awaitingVerification",
gateway: "stripe",
type: "token",
token: "card_1IDHBZFdjJUqVVV2gPlbz8BC"
} )
db.orders.insertMany( [{
... customerId: "journalfanatic@e/mail.com",
... paymentId: "600e6f37aa2232f59e273082",
... paymentStatus: "paid",
... status: "shippedAwaitingDelivery",
... currency: "USD",
... totalCost: NumberDecimal(39.85),
... items: [
... { sku: "154B",
... quantity: "2",
... price: NumberDecimal(14.99),
... discount: NumberDecimal(1.00),
... preTaxTotal: NumberDecimal(27.98),
... tax: NumberDecimal(1.00),
... total: NumberDecimal(28.98)
... }, { sku: "154A",
... quantity: "1",
... price: NumberDecimal(9.99),
... preTaxTotal: NumberDecimal(9.99),
... tax: NumberDecimal(0.87),
... total: NumberDecimal(10.86)
... }
... ],
... shipping: {
... address:{
... street1: "50 Work Street",
... street2: "Floor 16",
... city: "Chicago",
... state: "IL",
... country: "USA",
... zip: "60601"
... },
... origin: {
... street1: "1 Penn Ave",
... street2: " ",
... city: "New York",
... state: "NY",
... country: "USA",
... zipCode: "46281"
... },
... carrier: "USPS",
... tracking: "123412341234"
... }
... } ]);
Simple Queries to perform on ecommerce db
- Write a query to find the average order value for all Customers.
db.getCollection('orders').aggregate([{ $lookup:{from: 'products',
localField: 'sku',foreignField: '_id', as: 'orderdetails'}
}]);
- Query your database to get top 10 customers whose orders have the highest value and who have been customers since less than 2 months
db.collection.aggregate({ $group : { _id: null, max: { $max : "$Customer" }}});
- Query your database to find the sum of all orders received in this month
db.aggregateSumDemo.aggregate([ {
$group: {
_id: null,
"orders": {
$sum: "$orders"
}
}
} ] );
- Query your database to find customers count in various cities, group them by names of cities
db.collection.aggregate([
{
"$group": {
"_id": "$city"
}
},
{
"$group": {
"_id": 0,
"count": { "$sum": 1 }
}
}
]);
- Query your database to get all the sellers who have base in the same city in which the customers who ordered their products: Order them in the order of highest number of products sold to customers in same city to lowest.
db.getCollection('orders').aggregate([{
... $lookup:
... {
... from: 'seller',
... localField: 'seller_id',
... foreignField: '_id',
... as: 'orderdetails'
... }
... }]);