After first part of article on $lookup at
https://msdotnetbuddy.blogspot.com/2023/04/how-to-make-joins-in-mongodb.html
Lets move further in understanding $lookup meaning joins in mongoDb.
We will use two collection one for orders & other for stocks in warehouse.
As usual we will drop collections of same name so that we can start fresh.
db.orders.drop()
db.stockWarehouse.drop()
Now populate orders & stockWarehouse collection with some data.
db.orders.insertMany( [
{
"orderId": 1,
"userId":1,
"cartId":1,
"cartItems":[
{
"itemId":1,
"item": "almonds",
"price": 12,
"orderedQuantity": 2
},
{
"itemId": 2,
"item": "pecans",
"price": 20,
"orderedQuantity": 1
},
{
"itemId": 3,
"item": "cookies",
"price": 10,
"orderedQuantity": 60
}
]
},
{
"orderId": 2,
"userId":2,
"cartId":2,
"cartItems":[
{
"itemId":7,
"item": "Segate Hard Disk",
"price": 10000,
"orderedQuantity": 1
},
{
"itemId": 8,
"item": "logitech mouse",
"price": 500,
"orderedQuantity": 3
},
{
"itemId": 9,
"item": "LAN Cable",
"price": 250,
"orderedQuantity": 1
}
]
},
{
"orderId": 3,
"userId":2,
"cartId":3,
"cartItems":[
{
"itemId":4,
"item": "paper",
"price": 12,
"orderedQuantity": 2
},
{
"itemId": 5,
"item": "pencil",
"price": 20,
"orderedQuantity": 5
},
{
"itemId": 6,
"item": "eraser",
"price": 5,
"orderedQuantity": 3
}
]
}
])
db.stockWarehouse.insertMany( [
{
"id": 1,
"stock_item": "almonds",
"warehouse": "A",
"instock": 120
},
{
"id": 2,
"stock_item": "pecans",
"warehouse": "A",
"instock": 30
},
{
"id": 4,
"stock_item": "cookies",
"warehouse": "B",
"instock": 10
},
{
"id": 5,
"stock_item": "paper",
"warehouse": "D",
"instock": 100
},
{
"id": 6,
"stock_item": "pencil",
"warehouse": "A",
"instock": 20
},
{
"id": 7,
"stock_item": "eraser",
"warehouse": "D",
"instock": 80
} ,
{
"id": 8,
"stock_item": "LAN Cable",
"warehouse": "A",
"instock": 140
},
{
"id": 9,
"stock_item": "logitech mouse",
"warehouse": "C",
"instock": 100
},
{
"id": 10,
"stock_item": "Segate Hard Disk",
"warehouse": "D",
"instock": 50
}
])
Now we will use $lookup to check purchased item is in stock ,and in
which warehouse its present.
db.orders.aggregate( [
{
$unwind: "$cartItems"
},
{
$lookup: {
from: "stockWarehouse",
let: { purchasedItem: "$cartItems.item",quantity:"$cartItems.orderedQuantity"},
pipeline: [
{
$match: {
$expr: {
$and: [
{ $eq: ["$stock_item","$$purchasedItem"]},
{ $gte: [ "$instock","$$quantity"]}
]
}
}
},
{
$project: { "stock_item": 1, "id": 1,"warehouse":1,"instock":1}
}
],
as: "stockdata"
}
},
{
$unwind: "$stockdata"
},
{
$group: {
_id: {"orderId":"$orderId"},
userId: { $first: "$userId" },
cartId: { $first: "$cartId" },
orderId: { $first: "$orderId" },
cartItems: {
$push: {
itemId: "$cartItems.itemId",
item: "$cartItems.item",
price: "$cartItems.price",
orderedQuantity: "$cartItems.orderedQuantity",
warehouse: "$stockdata.warehouse",
instock: "$stockdata.instock"
}
}
}
}
])
Output:
[
{
_id: { orderId: 1 },
userId: 1,
cartId: 1,
orderId: 1,
cartItems: [
{
itemId: 1,
item: 'almonds',
price: 12,
orderedQuantity: 2,
warehouse: 'A',
instock: 120
},
{
itemId: 2,
item: 'pecans',
price: 20,
orderedQuantity: 1,
warehouse: 'A',
instock: 30
}
]
},
{
_id: { orderId: 3 },
userId: 2,
cartId: 3,
orderId: 3,
cartItems: [
{
itemId: 4,
item: 'paper',
price: 12,
orderedQuantity: 2,
warehouse: 'D',
instock: 100
},
{
itemId: 5,
item: 'pencil',
price: 20,
orderedQuantity: 5,
warehouse: 'A',
instock: 20
},
{
itemId: 6,
item: 'eraser',
price: 5,
orderedQuantity: 3,
warehouse: 'D',
instock: 80
}
]
},
{
_id: { orderId: 2 },
userId: 2,
cartId: 2,
orderId: 2,
cartItems: [
{
itemId: 7,
item: 'Segate Hard Disk',
price: 10000,
orderedQuantity: 1,
warehouse: 'D',
instock: 50
},
{
itemId: 8,
item: 'logitech mouse',
price: 500,
orderedQuantity: 3,
warehouse: 'C',
instock: 100
},
{
itemId: 9,
item: 'LAN Cable',
price: 250,
orderedQuantity: 1,
warehouse: 'A',
instock: 140
}
]
}
]
Here purchased item "cookie" has required quantity 60 but in stock only 10 units.
Above query will exclude that cartItem so we are not able to know whats problem.
I am modifying further to get list irrespective of whether it is in stock or not.
Here is query.
db.orders.aggregate( [
{
$unwind: "$cartItems"
},
{
$lookup: {
from: "stockWarehouse",
let: { purchasedItem: "$cartItems.item",quantity:"$cartItems.orderedQuantity"},
pipeline: [
{
$match: {
$expr: {
$and: [
{ $eq: ["$stock_item","$$purchasedItem"]}
]
}
}
},
{
$project: { "stock_item": 1, "id": 1,"warehouse":1,"instock":1}
}
],
as: "stockdata"
}
},
{
$unwind: "$stockdata"
},
{
$group: {
_id: {"orderId":"$orderId"},
userId: { $first: "$userId" },
cartId: { $first: "$cartId" },
orderId: { $first: "$orderId" },
cartItems: {
$push: {
itemId: "$cartItems.itemId",
item: "$cartItems.item",
price: "$cartItems.price",
orderedQuantity: "$cartItems.orderedQuantity",
warehouse: "$stockdata.warehouse",
instock: "$stockdata.instock"
}
}
}
}
])
Output:
[
{
_id: { orderId: 1 },
userId: 1,
cartId: 1,
orderId: 1,
cartItems: [
{
itemId: 1,
item: 'almonds',
price: 12,
orderedQuantity: 2,
warehouse: 'A',
instock: 120
},
{
itemId: 2,
item: 'pecans',
price: 20,
orderedQuantity: 1,
warehouse: 'A',
instock: 30
},
{
itemId: 3,
item: 'cookies',
price: 10,
orderedQuantity: 60,
warehouse: 'B',
instock: 10
}
]
},
{
_id: { orderId: 3 },
userId: 2,
cartId: 3,
orderId: 3,
cartItems: [
{
itemId: 4,
item: 'paper',
price: 12,
orderedQuantity: 2,
warehouse: 'D',
instock: 100
},
{
itemId: 5,
item: 'pencil',
price: 20,
orderedQuantity: 5,
warehouse: 'A',
instock: 20
},
{
itemId: 6,
item: 'eraser',
price: 5,
orderedQuantity: 3,
warehouse: 'D',
instock: 80
}
]
},
{
_id: { orderId: 2 },
userId: 2,
cartId: 2,
orderId: 2,
cartItems: [
{
itemId: 7,
item: 'Segate Hard Disk',
price: 10000,
orderedQuantity: 1,
warehouse: 'D',
instock: 50
},
{
itemId: 8,
item: 'logitech mouse',
price: 500,
orderedQuantity: 3,
warehouse: 'C',
instock: 100
},
{
itemId: 9,
item: 'LAN Cable',
price: 250,
orderedQuantity: 1,
warehouse: 'A',
instock: 140
}
]
}
]
Here we are able to see cookie item even tough it is
not in stock.
No comments:
Post a Comment