MongoDB Aggregation - Find the products with low stock

Question

I'm trying to create a MongoDB aggregation query to find products with low stock. Each product in my collection has a low_stock quantity specified, and I want to retrieve all product stocks whose quantity is below this threshold.

I've already implemented an aggregation query that groups product stocks by the product and calculates the total quantity. However, when I add a $match condition to filter by the low_stock value, the query returns an empty result.

Here's my current aggregation query:

findAllLowStockAggregation = async (filter: Partial<IProductStock>) => {
    try {
      const result: IProductStock[] = await ProductStockModel.aggregate([
        {
          $match: filter,
        },
        {
          $group: {
            _id: "$product",
            totalQuantity: { $sum: "$quantity" },
          },
        },
        {
          $lookup: {
            from: "products",
            localField: "_id",
            foreignField: "_id",
            as: "product",
          },
        },
        {
          $unwind: "$product",
        },
        {
          $match: {
            "totalQuantity": { $lte: "$product.low_stock" },
          },
        },
        {
          $unwind: "$product",
        },
      ]);
  
      console.log({ result });
      if (result && result.length > 0) {
        return result;
      } else {
        console.log('No low stock products found.');
        return [];
      }
    } catch (error) {
      console.error('Aggregation error:', error);
      return [];
    }
  };

The issue seems to be with the $match condition at the end of the query. When I remove it, the query returns data, but I want to filter results by comparing the totalQuantity with the low_stock value of the product.

Removed Code

{
  $match: {
    "totalQuantity": { $lte: "$product.low_stock" },
  },
},

It responded the below data. I have tried my best to fetch the exact issue and fix it, but still I am fixing

[
    {
        "_id": "65433f947176c62d87c94c70",
        "totalQuantity": 10,
        "product": {
            "_id": "65433f947176c62d87c94c70",
            "company": "6541e997b0d857ecd9233709",
            "low_stock": 15,
            "createdAt": "2023-11-02T06:20:04.579Z",
            "updatedAt": "2023-11-02T06:20:04.579Z",
            "__v": 0
        }
    },
    {
        "_id": "65433f947176c62d87c94c72",
        "totalQuantity": 100,
        "product": {
            "_id": "65433f947176c62d87c94c72",
            "company": "6541e997b0d857ecd9233709",
            "low_stock": 110,
            "createdAt": "2023-11-02T06:20:04.581Z",
            "updatedAt": "2023-11-02T06:20:04.581Z",
            "__v": 0
        }
    }
]

My Product Model and Product Stock Model are as follows:

Product Model

import { Schema, model, Document, Types } from 'mongoose';

export interface IProduct {
  company: Types.ObjectId;
  low_stock?: number;
}

export interface IProductDocument extends Document, IProduct {}

const productSchema = new Schema<IProductDocument>({
  company: {
    type: Schema.Types.ObjectId,
    ref: CompanyModel,
    required: true,
  },
  low_stock: {
    type: Number,
    default: 1,
    required: true,
  },
}, { timestamps: true });

const ProductModel = model<IProductDocument>('Product', productSchema, 'products');

export default ProductModel;

Product Stock Model

import { Schema, model, Document, Types } from 'mongoose';

export interface IProductStock {
  company: Types.ObjectId;
  product: Types.ObjectId;
  quantity: number;
}

export interface IProductStockDocument extends Document, IProductStock {}

const productStockSchema = new Schema<IProductStockDocument>({
  company: {
    type: Schema.Types.ObjectId,
    ref: CompanyModel,
    required: true,
  },
  product: {
    type: Schema.Types.ObjectId,
    ref: ProductModel,
    required: true,
  },
  quantity: {
    type: Number,
    required: true,
  },

}, { timestamps: true });

const ProductStockModel = model<IProductStockDocument>('ProductStock', productStockSchema, 'product_stocks');

export default ProductStockModel;

Is there something wrong with my aggregation query, or is there a better way to achieve this? Any help is greatly appreciated.

Answer 1

You need the $expr operator to compare both fields in the document.

{
  $match: {
    $expr: {
      $lte: [
        "$totalQuantity",
        "$product.low_stock",
      ]
    }
  }
}