I have a list of products mapped in a table in react and i can edit them adding ingredients and quantity of them. I need to be able to find every instance of ingredient in every product, look up price for every ingredient in other document, multiply it by quantity, for example:
- "Soup" has sub array called "ingredients", one fo them is "milk" with quantity of 0,3. It looks up for "milk" in "Ingredients" document, finds price for milk, and multiples it - 5 * 0,3 (for every ingredient in product) and gives back result inside product "price"
Products:
{
"_id": {
"$oid": "62e391b2a16efedc01d71e35"
},
"name": "Soup",
"ingredients": [
{
"name": "milk",
"quantity": "0,3",
"_id": {
"$oid": "62e391b2a16efedc01d71e36"
}
},
{
"name": "flakes",
"value": "0,2",
"_id": {
"$oid": "62e391b2a16efedc01d71e37"
}
}
],
"__v": 2
}
Ingredients:
{
"_id": {
"$oid": "6296689c4a87b691e7e04d39"
},
"name": "milk",
"__v": 0,
"price": 5
},
{
"_id": {
"$oid": "6296689c4a87b691e7e04d39"
},
"name": "flakes",
"__v": 0,
"price": 2
}
Expected result:
{
"_id": {
"$oid": "62e391b2a16efedc01d71e35"
},
"name": "Soup",
"price": 1,9,
"ingredients": [
{
"name": "milk",
"quantity": 0,3,
"_id": {
"$oid": "62e391b2a16efedc01d71e36"
}
},
{
"name": "flakes",
"quantity": 0,2,
"_id": {
"$oid": "62e391b2a16efedc01d71e37"
}
}
],
"__v": 2
}
EDIT: I tried with this but it's only adding first item of array outside of subarray of ingredients and i have no idea how to use it for every item:
Products.aggregate(
[
{
$lookup: {
from: "ingredients",
localField: "ingredients.name",
foreignField: "name",
as: "ingredients.price",
},
},
{
$set: {
price: { $arrayElemAt: ["$ingredients.price", 0] },
},
},
],
function (err, ingredients) {
if (err) throw err
console.log(JSON.stringify(ingredients, null, 4))
}
)