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

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