Mongoose对象数组聚合计算返回0异常问题及原因解析

系统背景 #

基于Express和Mongoose开发BMI、体重数据追踪系统，核心Tracker模型包含两个对象数组字段：

• caloriesIntake：存储食物名称、摄入量、卡路里数据
• caloriesBurned：存储运动项目、时长、卡路里数据

示例数据：

{
  "trackers": [
    {
      "_id": "657b58f0e05669de911e9c4a",
      "userId": "654d08d00149cc5b1ac434e7",
      "date": "2023-12-20",
      "caloriesIntake": [
        {
          "foodName": "pizza",
          "quantity": "300",
          "calories": 788.7
        },
        {
          "foodName": "test",
          "quantity": "20",
          "calories": 100
        },
        {
          "foodName": "cheese",
          "quantity": "20",
          "calories": 78.78
        }
      ],
      "caloriesBurned": [
        {
          "activity": "Basketball, shooting baskets",
          "time": "50",
          "calories": 272.5
        },
        {
          "activity": "test",
          "time": "20",
          "calories": 200
        }
      ],
      "__v": 5,
      "neck": 20,
      "weight": 70
    },
    {
      "_id": "657b59e7e05669de911e9c5e",
      "userId": "654d08d00149cc5b1ac434e7",
      "date": "2023-12-28",
      "caloriesIntake": [
        {
          "foodName": "ratatouille",
          "quantity": "400",
          "calories": 233.2
        },
        {
          "foodName": "test",
          "quantity": "30",
          "calories": 100
        }
      ],
      "caloriesBurned": [
        {
          "activity": "Swimming butterfly",
          "time": "30",
          "calories": 399.5
        },
        {
          "activity": "test",
          "time": "20",
          "calories": 400
        }
      ],
      "__v": 4
    }
  ]
}

需求与实现代码 #

需先计算每日caloriesIntake、caloriesBurned的总和，再对这些总和执行$avg、$max、$min聚合统计，编写的聚合管道如下：

const result = await Tracker.aggregate([
            {
                $match: {
                    userId: userId,
                }
            },
            {
                $group: {
                  _id: null,
                  avgBMI: { $avg: '$BMI' },
                  minBMI: { $min: '$BMI' },
                  maxBMI: { $max: '$BMI' },
                  avgCaloriesIntake: { $avg: { $sum: '$caloriesIntake.calories' }},
                  minCaloriesIntake: { $min: { $sum: '$caloriesIntake.calories' }},
                  maxCaloriesIntake: { $max: { $sum: '$caloriesIntake.calories' }},
                  minCaloriesBurned: { $min: { $sum: '$caloriesBurned.calories' }},
                  avgCaloriesBurned: { $avg: { $sum: '$caloriesBurned.calories' }},
                  maxCaloriesBurned: { $max: { $sum: '$caloriesBurned.calories' }},
                },
              },
              {
                $project: {
                  _id: 0,
                  avgBMI: 1,
                  minBMI: 1,
                  maxBMI: 1,
                  avgCaloriesIntake: 1,
                  minCaloriesIntake: 1,
                  maxCaloriesIntake: 1,
                  avgCaloriesBurned: 1,
                  minCaloriesBurned: 1,
                  maxCaloriesBurned: 1,
                },
              },
            ]).exec();

异常现象 #

执行后，BMI等非数组字段计算结果正常（示例返回null是因为数据中无BMI字段），但所有卡路里相关统计指标全部返回0。尝试使用$unwind拆分数组后，问题未解决，且其他字段计算值出现异常。返回结果示例：

{
  "metrics": {
    "avgBMI": null,
    "minBMI": null,
    "maxBMI": null,
    "avgCaloriesIntake": 0,
    "minCaloriesIntake": 0,
    "maxCaloriesIntake": 0,
    "minCaloriesBurned": 0,
    "avgCaloriesBurned": 0,
    "maxCaloriesBurned": 0
  }
}

问题根源 #

最终排查发现，问题源于数据存储类型不统一：旧文档中calories字段为字符串类型（如"calories": "400"），虽已将Mongoose模型修改为数值类型（"calories": 400），但未同步更新旧数据，导致MongoDB聚合计算时无法正确将字符串转换为数值，最终统计结果全部为0。

内容的提问来源于stack exchange，提问作者Valentin-Constantin Baltatescu