How to use aggregate with $lookup in Mongoose with foreign key in sub sub array?

Question

I'm trying to JOIN 3 tables in my express-mongo project. I have a table called Product LIKE this:

Product:

_id:5f92a8dfad47ce1b66d4473b
name:"Freno 1"
createdFrom:5f648f7d642ed7082f5ff91f
category:5f92a00c4637a61a397320a1
description:"Freni di tutti i tipi"
sellingPrice:1050
purchasePrice:350

And I have to extract the available quantity after sum and subtract the quantity from other 2 tables: purchaseorders and salesorders

purchaseorders

_id:5f930c6c6817832c0d5acbb4
items:[
{
_id:5f930c6c6817832c0d5acbb5
product:5f92abaf17ec621c1da4f4f9
quantity:10
purchasingPrice:1500
discount:300
},
{
_id:5f930c6c6817832c0d5acbb6
product:5f92a8dfad47ce1b66d4473b
quantity:7
purchasingPrice:1500
discount:300
}]

salesorders

_id:5f930c6c6817832c0d5acbb4
items:[
{
_id:5f930c6c6817832c0d5acbb5
product:5f92abaf17ec621c1da4f4f9
quantity:3
sellingPrice:1500
discount:300
},
{
_id:5f930c6c6817832c0d5acbb6
product:5f92a8dfad47ce1b66d4473b
quantity:3
sellingPrice:1500
discount:300
}]

Purchaseorders and salesorders have be designed to contains different product in same order. I'm trying to combine this 3 tables to have a result like this:

[
{_id: 5f92a8dfad47ce1b66d4473b,
name: "Freno 1",
quantity: 4 },
etc.. with all other products
]

I wanna sum all quantities in purchaseorders and subtract the sum of quantity of the salesorders.

I'm trying to use aggregation starting FROM Product table like this:

let result = await Product.aggregate([
        {
            $lookup: {
                from: "purchaseorders",
                localField: "_id",
                foreignField: "items.product",
                as: "purchaseorders"
            }
        },
        {
            $lookup: {
                from: "salesorders",
                localField: "_id",
                foreignField: "items.product",
                as: "salesorders"
            }
        },
        {
            $unwind: "$purchaseorders"
        },
        {
            $unwind: "$purchaseorders.items"
        },
        {
            $unwind: "$salesorders"
        },
        {
            $unwind: "$salesorders.items"
        },
        {
            $group: {
                _id: "$_id"
            }
        }
        ])

Thanks who tries to help me!!

Answer 1

You can try,

• $addFields to add quantity field,
• $reduce to iterate loop of purchaseorders, $reduce to iterate loop of items and get the matching product's quantity and $add with initial value of reduce
• $reduce to iterate loop of salesorders, $reduce to iterate loop of items and get the matching product's quantity and $add with initial value of reduce
• $subtract purchaseorders's quantity with salesorders'a quantity

let result = await Product.aggregate([
  // skipped { $lookup },
  // skipped { $lookup },
  {
    $project: {
      _id: 1,
      name: 1,
      quantity: {
        $subtract: [
          {
            $reduce: {
              input: "$purchaseorders",
              initialValue: 0,
              in: {
                $add: [
                  "$$value",
                  {
                    $reduce: {
                      input: "$$this.items",
                      initialValue: 0,
                      in: {
                        $cond: [
                          { $eq: ["$$this.product", "$_id"] },
                          { $add: ["$$value", "$$this.quantity"] },
                          "$$value"
                        ]
                      }
                    }
                  }
                ]
              }
            }
          },
          {
            $reduce: {
              input: "$salesorders",
              initialValue: 0,
              in: {
                $add: [
                  "$$value",
                  {
                    $reduce: {
                      input: "$$this.items",
                      initialValue: 0,
                      in: {
                        $cond: [
                          { $eq: ["$$this.product", "$_id"] },
                          { $add: ["$$value", "$$this.quantity"] },
                          "$$value"
                        ]
                      }
                    }
                  }
                ]
              }
            }
          }
        ]
      }
    }
  }
])

Playground