How to query results from multiple tables in mongoose?

I have two tables

let SalesItemsSchema = new Schema({
    name: {type: String, required: true},
    quantity : {type:Number , required:true},
    sale_id: {type: String, required: true},
    amount : {type:Number , required:true},
    purchase_amount : {type:Number , required:true},
} , {timestamps : true});

let InventorySchema = new Schema({
    name: {type: String, required: true, max: 100},
    quantity: {type: Number, required: true},
} , {timestamps:true});

I want to get the remaining items in the inventory. For example 100 items of something were added to the inventory and 90 of them were sold. i want to get the difference from two tables (which will be 10) i am using mongoose.

To calculate the remaining inventory items using Mongoose, you can aggregate data from the SalesItems collection and subtract it from the Inventory collection. Here's an example:

const getRemainingInventory = async () => {
  return await Inventory.aggregate([
    {
      $lookup: {
        from: "salesitems", // Collection name for SalesItems
        localField: "name",
        foreignField: "name",
        as: "salesData"
      }
    },
    {
      $addFields: {
        soldQuantity: { $sum: "$salesData.quantity" }
      }
    },
    {
      $project: {
        name: 1,
        remainingQuantity: { $subtract: ["$quantity", "$soldQuantity"] }
      }
    }
  ]);
};

// Example Usage
getRemainingInventory()
  .then(data => console.log(data))
  .catch(err => console.error(err));

Explanation:

1. $lookup: Joins Inventory with SalesItems on the name field.
2. $addFields: Adds soldQuantity by summing up quantities from the joined salesData.
3. $project: Calculates remainingQuantity as inventory.quantity - soldQuantity.

This will return an array of items with their remaining quantities. Ensure the collection name in $lookup matches your database.