Express + Mongoose + Aggregation + Calculate Available Stock

Question

I'm using express js and mongoose and i'm new to these platforms. It would be great if someone can help me on this. Please refer the the below data and I'm looking for output like:

itemsizeId: 609578ca23033e55886e7590, AvailableQuantity: 112

itemsizeId: 609578ca23033e55886e758f, AvailableQuantity: 495

Note: Group by movementtype (inward / outward).

Available Stock: inward - outward

[
  {
    _id: '609fb1a1a7ed990f30d6cae2',
    refId: 'Purchase-1',
    itemsizeId: '609578ca23033e55886e7590',
    itemId: '609578ca23033e55886e758e',
    sizeId: '60950c0ba4982390f8dfed79',
    movementdate: '2021-05-15T11:33:52.894Z',
    movementtype: 'inward',
    movementcategory: 'purchase',
    quantity: 100,
    id: '609fb1a1a7ed990f30d6cae2',
  },
  {
    _id: '609fb1cba7ed990f30d6cae3',
    refId: 'Purchase-2',
    itemsizeId: '609578ca23033e55886e7590',
    itemId: '609578ca23033e55886e758e',
    sizeId: '60950c0ba4982390f8dfed79',
    movementdate: '2021-05-15T11:34:35.118Z',
    movementtype: 'inward',
    movementcategory: 'purchase',
    quantity: 20,
    id: '609fb1cba7ed990f30d6cae3',
  },
  {
    _id: '609fb1fda7ed990f30d6cae4',
    refId: 'Sale-1',
    itemsizeId: '609578ca23033e55886e7590',
    itemId: '609578ca23033e55886e758e',
    sizeId: '60950c0ba4982390f8dfed79',
    movementdate: '2021-05-15T11:35:25.068Z',
    movementtype: 'outward',
    movementcategory: 'sales',
    quantity: 5,
    id: '609fb1fda7ed990f30d6cae4',
  },
  {
    _id: '609fb255a7ed990f30d6cae5',
    refId: 'Purchase-3',
    itemsizeId: '609578ca23033e55886e758f',
    itemId: '609578ca23033e55886e758e',
    sizeId: '60950be9a4982390f8dfed78',
    movementdate: '2021-05-15T11:36:53.835Z',
    movementtype: 'inward',
    movementcategory: 'purchase',
    quantity: 500,
    id: '609fb255a7ed990f30d6cae5',
  },
  {
    _id: '609fb27ea7ed990f30d6cae6',
    refId: 'Sale-2',
    itemsizeId: '609578ca23033e55886e758f',
    itemId: '609578ca23033e55886e758e',
    sizeId: '60950be9a4982390f8dfed78',
    movementdate: '2021-05-15T11:37:34.066Z',
    movementtype: 'outward',
    movementcategory: 'sales',
    quantity: 8,
    id: '609fb27ea7ed990f30d6cae6',
  },
]

I have tried till below code and I'm struck to complete it:

const itemStock = await itemStockMovementModel.aggregate([
  {
    $match: { movementtype: 'inward' },
  },
  {
    $group: {
      _id: {
        itemsizeId: '$itemsizeId',
      },
      quantity: { $sum: '$quantity' },
    },
  },
])

user_8987128 · Answer

$group by itemsizeId and get quantity sum by condition for inward and outward
$subtract to inward - outward

const itemStock = await itemStockMovementModel.aggregate([
  {
    $group: {
      _id: "$itemsizeId",
      inward: {
        $sum: { $cond: [{ $eq: ["$movementtype", "inward"] }, "$quantity", 0] }
      },
      outward: {
        $sum: { $cond: [{ $eq: ["$movementtype", "outward"] }, "$quantity", 0] }
      }
    }
  },
  {
    $project: {
      quantity: {
        $subtract: ["$inward", "$outward"]
      }
    }
  }
])

Playground

You can use second formula as well, if you don't have third type in movementtype,

$group by itemsizeId
check condition if movementtype is inward then sum quantity otherwise sum negative quantity

const itemStock = await itemStockMovementModel.aggregate([
  {
    $group: {
      _id: "$itemsizeId",
      quantity: {
        $sum: {
          $cond: [
            { $eq: ["$movementtype", "inward"] },
            "$quantity",
            { $subtract: [0, "$quantity"] }
          ]
        }
      }
    }
  }
])

Playground

Express + Mongoose + Aggregation + Calculate Available Stock

1 Answers