Suppose we are keeping records of item purchased from our online
store into mongoDb as follows.
Here we will ensure that their is no existing collection of same
name so lets drop if exist
db.sales.drop()
Now insert our sales data.
db.sales.insertMany( [
{
_id: 0,
email:"sagar@example.com",
purchaseTime:new Date("2024-01-01:12:30:00"),
cartItems: [
{ item_id: 43, quantity: 2, pricePerItem: 10 },
{ item_id: 2, quantity: 1, pricePerItem: 240 }
],
deliveryAddress:{
city:"Mumbai",
street:"M k Gandhi Road",
pin:"400080",
town:"Vikroli",
Bldg:"Arun Niwara,Bldg No 167",
floor:"1st",
roomNo:"5386"
},
isDelivered:false
},
{
_id: 1,
email:"swara@example.com",
purchaseTime:new Date("2024-02-01:10:30:00"),
cartItems: [
{ item_id: 34, quantity: 2, pricePerItem: 20 },
{ item_id: 56, quantity: 5, pricePerItem: 340 },
{ item_id: 78, quantity: 3, pricePerItem: 540 }
],
deliveryAddress:{
city:"Mumbai",
street:"Indiara Gandhi Road",
pin:"400745",
town:"Naupada",
Bldg:"Arun Niwara,Bldg No 167",
floor:"1st",
roomNo:"3276"
},
isDelivered:true,
deliveryDate:new Date("2024-02-05:08:10:00"),
},
{
_id: 2,
email:"sangram@example.com",
purchaseTime:new Date("2024-01-05:14:35:00"),
cartItems: [
{ item_id: 23, quantity: 3, pricePerItem: 110 },
{ item_id: 103, quantity: 4, pricePerItem: 5 },
{ item_id: 38, quantity: 1, pricePerItem: 300 }
],
deliveryAddress:{
city:"Pune",
street:"Bose D K Road",
pin:"402357",
town:"Raja Nagar",
Bldg:"Varun CHS,Bldg No 546",
floor:"2nd",
roomNo:"785"
},
isDelivered:false
},
{
_id: 3,
email:"sachin@example.com",
purchaseTime:new Date("2024-01-01:08:24:00"),
cartItems: [
{ item_id: 4, quantity: 1, pricePerItem: 23 },
{ item_id: 2, quantity: 1, pricePerItem: 240 }
],
deliveryAddress:{
city:"Nashik",
street:"Jangli Maharaj Road",
pin:"2345765",
town:"Vartak Nagar",
Bldg:"Govardhan Das Apt,Bldg No 796",
floor:"8nd",
roomNo:"6885"
},
isDelivered:false
}
] )
Condition 1:
Suppose Now we want all customer email id along with their cartItem
where pricePerItem is greater than 100
db.sales.aggregate( [
{
$project: {
email:"$email",
items: {
$filter: {
input: "$cartItems",
as: "item",
cond: { $gte: [ "$$item.pricePerItem", 100 ] }
}
}
}
}
] )
Output:
[
{
_id: 0,
email: 'sagar@example.com',
items: [ { item_id: 2, quantity: 1, pricePerItem: 240 } ]
},
{
_id: 1,
email: 'swara@example.com',
items: [ { item_id: 56, quantity: 5, pricePerItem: 340 } ]
},
{
_id: 2,
email: 'sangram@example.com',
items: [
{ item_id: 23, quantity: 3, pricePerItem: 110 },
{ item_id: 38, quantity: 1, pricePerItem: 300 }
]
},
{
_id: 3,
email: 'sachin@example.com',
items: [ { item_id: 2, quantity: 1, pricePerItem: 240 } ]
}
]
Condition 2:
Now we want all customer email id along with their cartItem where pricePerItem
is greater than 100 provided customer is from Mumbai.
db.sales.aggregate( [
{
$match: {
"deliveryAddress.city": "Mumbai"
}
},
{
$project: {
email:"$email",
city:"$deliveryAddress.city",
items: {
$filter: {
input: "$cartItems",
as: "item",
cond: { $gte: [ "$$item.pricePerItem", 100 ] }
}
}
}
}
] )
Output:
[
{
_id: 0,
email: 'sagar@example.com',
city: 'Mumbai',
items: [ { item_id: 2, quantity: 1, pricePerItem: 240 } ]
},
{
_id: 1,
email: 'swara@example.com',
city: 'Mumbai',
items: [ { item_id: 56, quantity: 5, pricePerItem: 340 } ]
}
]
Condition 3:
Here we want all customer email id along with all their cartItems no
Other data like purchaseTime or deliveryAddress then
db.sales.aggregate( [
{
$project: {
email:"$email",
items: {
$filter: {
input: "$cartItems",
as: "item",
cond: { 1:1 }
}
}
}
}
] )
Output:
[
{
_id: 0,
email: 'sagar@example.com',
items: [
{ item_id: 43, quantity: 2, pricePerItem: 10 },
{ item_id: 2, quantity: 1, pricePerItem: 240 }
]
},
{
_id: 1,
email: 'swara@example.com',
items: [
{ item_id: 34, quantity: 2, pricePerItem: 20 },
{ item_id: 56, quantity: 5, pricePerItem: 340 }
]
},
{
_id: 2,
email: 'sangram@example.com',
items: [
{ item_id: 23, quantity: 3, pricePerItem: 110 },
{ item_id: 103, quantity: 4, pricePerItem: 5 },
{ item_id: 38, quantity: 1, pricePerItem: 300 }
]
},
{
_id: 3,
email: 'sachin@example.com',
items: [
{ item_id: 4, quantity: 1, pricePerItem: 23 },
{ item_id: 2, quantity: 1, pricePerItem: 240 }
]
}
]
Condition 4:
Suppose delivery team ask for list of carts to be sent in mumbai.
db.sales.aggregate([
{
$match: {
"deliveryAddress.city": "Mumbai",
"isDelivered":false
}
},
{
$project: {
email: 1,
deliveryAddress: 1
}
}
])
Output:
[
{
_id: 0,
email: 'sagar@example.com',
deliveryAddress: {
city: 'Mumbai',
street: 'M k Gandhi Road',
pin: '400080',
town: 'Vikroli',
Bldg: 'Arun Niwara,Bldg No 167',
floor: '1st',
roomNo: '5386'
}
}
]
Condition 5:
Suppose we want all customer email & their cart item classified under 4 sections
below Hundred
below Two Hundred
below Three Hundred
More than Three Hundred
db.sales.aggregate([
{
$match: {
"deliveryAddress.city": "Mumbai"
}
},
{
$project: {
email: "$email",
city: "$deliveryAddress.city",
belowHundred: {
$filter: {
input: "$cartItems",
as: "item",
cond: { $lte: ["$$item.pricePerItem", 100] }
}
},
belowTwoHundred: {
$filter: {
input: "$cartItems",
as: "item",
cond: { $and: [{ $gte: ["$$item.pricePerItem", 100] },
{ $lte: ["$$item.pricePerItem", 200] }] }
}
},
belowThreeHundred: {
$filter: {
input: "$cartItems",
as: "item",
cond: { $and: [{ $gte: ["$$item.pricePerItem", 200] },
{ $lte: ["$$item.pricePerItem", 300] }] }
}
},
greaterThanThreeHundred: {
$filter: {
input: "$cartItems",
as: "item",
cond: { $gte: ["$$item.pricePerItem", 300] }
}
}
}
}
])
Output:
[
{
_id: 0,
email: 'sagar@example.com',
city: 'Mumbai',
belowHundred: [ { item_id: 43, quantity: 2, pricePerItem: 10 } ],
belowTwoHundred: [],
belowThreeHundred: [ { item_id: 2, quantity: 1, pricePerItem: 240 } ],
greaterThanThreeHundred: []
},
{
_id: 1,
email: 'swara@example.com',
city: 'Mumbai',
belowHundred: [ { item_id: 34, quantity: 2, pricePerItem: 20 } ],
belowTwoHundred: [],
belowThreeHundred: [],
greaterThanThreeHundred: [ { item_id: 56, quantity: 5, pricePerItem: 340 } ]
}
]
Condition 5:
Suppose in our cartItems array each array element corresponds with
item purchased it contain price per item & total items purchased but
it does not have item total so now we will write query to generate it.
db.sales.aggregate( [
{
$project:{
email: "$city",
purchaseTime:"$purchaseTime",
deliveryAddress:"$deliveryAddress",
cartItemsWithSumTotal:{
$map:{
input:"$cartItems",
as:"element",
in:{
$mergeObjects: ["$$element",
{
"ItemTotal": { $multiply:
["$$element.quantity","$$element.pricePerItem"]
}
}
]
}
}
}
}
}
])
Output:
[
{
_id: 0,
purchaseTime: ISODate('2024-01-01T07:00:00.000Z'),
deliveryAddress: {
city: 'Mumbai',
street: 'M k Gandhi Road',
pin: '400080',
town: 'Vikroli',
Bldg: 'Arun Niwara,Bldg No 167',
floor: '1st',
roomNo: '5386'
},
cartItemsWithSumTotal: [
{ item_id: 43, quantity: 2, pricePerItem: 10, ItemTotal: 20 },
{ item_id: 2, quantity: 1, pricePerItem: 240, ItemTotal: 240 }
]
},
{
_id: 1,
purchaseTime: ISODate('2024-02-01T05:00:00.000Z'),
deliveryAddress: {
city: 'Mumbai',
street: 'Indiara Gandhi Road',
pin: '400745',
town: 'Naupada',
Bldg: 'Arun Niwara,Bldg No 167',
floor: '1st',
roomNo: '3276'
},
cartItemsWithSumTotal: [
{ item_id: 34, quantity: 2, pricePerItem: 20, ItemTotal: 40 },
{ item_id: 56, quantity: 5, pricePerItem: 340, ItemTotal: 1700 },
{ item_id: 78, quantity: 3, pricePerItem: 540, ItemTotal: 1620 }
]
},
{
_id: 2,
purchaseTime: ISODate('2024-01-05T09:05:00.000Z'),
deliveryAddress: {
city: 'Pune',
street: 'Bose D K Road',
pin: '402357',
town: 'Raja Nagar',
Bldg: 'Varun CHS,Bldg No 546',
floor: '2nd',
roomNo: '785'
},
cartItemsWithSumTotal: [
{ item_id: 23, quantity: 3, pricePerItem: 110, ItemTotal: 330 },
{ item_id: 103, quantity: 4, pricePerItem: 5, ItemTotal: 20 },
{ item_id: 38, quantity: 1, pricePerItem: 300, ItemTotal: 300 }
]
},
{
_id: 3,
purchaseTime: ISODate('2024-01-01T02:54:00.000Z'),
deliveryAddress: {
city: 'Nashik',
street: 'Jangli Maharaj Road',
pin: '2345765',
town: 'Vartak Nagar',
Bldg: 'Govardhan Das Apt,Bldg No 796',
floor: '8nd',
roomNo: '6885'
},
cartItemsWithSumTotal: [
{ item_id: 4, quantity: 1, pricePerItem: 23, ItemTotal: 23 },
{ item_id: 2, quantity: 1, pricePerItem: 240, ItemTotal: 240 }
]
}
]
Suppose rather than calculating item total each time we want it to be
calculated once and saved there in at array element which is a JSON object.
db.sales.find({"cartItems": { $exists: true, $ne: [] }}).forEach(function(doc) {
doc.cartItems.forEach(function(item) {
item.ItemTotalNew = item.quantity * item.pricePerItem;
});
db.sales.updateOne(
{ "_id": doc._id },
{ "$set": { "cartItems": doc.cartItems } }
);
});
Their is also another way to do this,here is it.
It first select all sales document where cartItems is non empty array.
var bulkUpdate = db.sales.initializeUnorderedBulkOp();
db.sales.find({ "cartItems": { $exists: true, $ne: [] } })
.forEach(function(doc) {
doc.cartItems.forEach(function(item) {
item.ItemTotalNew = item.quantity * item.pricePerItem;
});
bulkUpdate.find({ "_id": doc._id })
.updateOne({ "$set": { "cartItems": doc.cartItems } });
});
bulkUpdate.execute();
You can delete this newly added field "ItemTotalNew" by using below query.
db.sales.updateMany(
{}, // Match all documents
{ $unset: { "cartItems.$[].ItemTotalNew": "" } }
)
If you have field "ItemTotalNew" at root then you can run following query.
db.sales.updateMany(
{}, // Match all documents
{ $unset: { "ItemTotalNew": "" } }
)
limit on number of element of inner array:
db.sales.aggregate( [
{
$project: {
items: {
$filter: {
input: "$cartItems",
as: "item",
cond: { $gte: [ "$$item.pricePerItem", 100 ] }
}
}
}
}
] )
In result of above query items array printed as it is in sense we are not
limiting how many element that array can have.But following query will
limit its length to 1
db.sales.aggregate( [
{
$project: {
items: {
$filter: {
input: "$cartItems",
as: "item",
cond: { $gte: [ "$$item.pricePerItem", 100 ] },
limit: 1
}
}
}
}
] )
Note :
We can make exact replica of our collection using $out below is query
db.sales.aggregate([
{ $out: "purchase" }
])
This will create a new collection purchase in same database
having exactly same documents w.r.t. of sales when the
command the above run.
No comments:
Post a Comment