MongoDB聚合查询问题：无法筛选出低库存产品

我正在创建MongoDB聚合查询以找出低库存产品。集合中的每个产品都指定了low_stock阈值，希望获取总库存数量低于该阈值的产品库存记录。

我已实现按产品分组并计算总库存数量的聚合查询，但添加$match条件以low_stock值筛选时，查询返回空结果。移除该条件后可正常返回数据，但我需要对比totalQuantity与产品的low_stock值来筛选结果。

当前聚合查询代码 #

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 [];
    }
  };

移除的$match代码片段 #

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

移除该条件后返回的数据 #

[
    {
        "_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
        }
    }
]

Product模型定义 #

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模型定义 #

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;

请问我的聚合查询存在什么问题？或有更优的实现方式吗？恳请提供帮助。

内容的提问来源于stack exchange，提问作者mufazmi