我正在尝试创建一个MongoDB聚合查询来查找库存低的产品。我的集合中的每个产品都指定了low_stock数量，我想检索数量低于此阈值的所有产品库存。
我已经实现了一个聚合查询，它按产品对产品库存进行分组并计算总数量。但是，当我添加$match条件以按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条件。当我删除它时，查询返回数据，但我想通过比较totalQuantity和产品的low_stock值来过滤结果。

删除代码

{
  $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
        }
    }
]

型
我的产品型号和产品库存型号如下：

产品型号

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;

型

产品库存型号

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;

型
我的聚合查询有什么问题吗？或者有更好的方法来实现这一点吗？任何帮助都非常感谢。