MongoDB关联查询统计palletWidth字段总和并用作过滤条件

实现方案 #

你原本的思路是正确的，现有代码重复查询了两次Pallet集合，优化后逻辑更简洁、查询效率更高：

1. 仅保留一次Pallet关联查询，避免多余的数据库IO
2. 直接用$addFields计算托盘宽度总和，符合MongoDB的语法支持
3. 顺带补充你匹配逻辑中用到的托盘总重量计算，避免空值报错

以下是修正后的完整代码：

const cargoQuery = Cargo.aggregate([
  // 关联查询对应货物的所有托盘数据
  {
    $lookup: {
      from: Pallet.collection.name,
      localField: "_id",
      foreignField: "cargoId",
      as: "pallets"
    }
  },
  // 计算需要的衍生字段
  { 
    $addFields: {
      // 直接累加所有托盘的宽度，无托盘时默认返回0
      maxLineWidthEuro: { $sum : "$pallets.palletWidth" },
      // 补充计算匹配逻辑需要的托盘总重量
      totalPalletsWeight: { $sum: "$pallets.cargoWeight" }
    }
  },
  // 业务规则筛选
  {
    $match: {
      $or: [
        /*仅按重量筛选的品类*/
        {cargoType: {$eq: cRuleINDBULK}, $expr: {$lte: [{ $multiply: ["$weight", 0.98]}, +tRuleMaxWeightINDBULK]}},
        {cargoType: {$eq: cRuleFOODBULK}, $expr: {$lte: [{ $multiply: ["$weight", 0.98]}, +tRuleMaxWeightFOODBULK]}},
        {cargoType: {$eq: cRuleADR}, $expr: {$lte: [{ $multiply: ["$weight", 0.98]}, +tRuleMaxWeightADR]}},
        {cargoType: {$eq: cRuleINDLIQ}, $expr: {$lte: [{ $multiply: ["$weight", 0.98]}, +tRuleMaxWeightINDLIQ]}},
        {cargoType: {$eq: cRuleFOODLIQ}, $expr: {$lte: [{ $multiply: ["$weight", 0.98]}, +tRuleMaxWeightFOODLIQ]}},
        {cargoType: {$eq: cRuleANIMALS}, $expr: {$lte: [{ $multiply: ["$weight", 0.98]}, +tRuleMaxWeightANIMALS]}},
        
        /*需校验长宽高的品类*/
        {cargoType: {$eq: cRuleSPECIAL}, $expr: {$lte: [{ $multiply: ["$weight", 0.98]}, +tRuleMaxWeightSPECIAL]}, height: {$lte: +tRuleMaxHeightSPECIAL}, width: {$lte: +tRuleMaxWidthSPECIAL}, clength: {$lte: +tRuleMaxLengthSPECIAL}},
        {cargoType: {$eq: cRuleHANG}, $expr: {$lte: [{ $multiply: ["$weight", 0.98]}, +tRuleMaxWeightHANG]}, height: {$lte: +tRuleMaxHeightHANG}, width: {$lte: tRuleMaxWidthHANG}, clength: {$lte: tRuleMaxLengthHANG}},
        {cargoType: {$eq: cRuleSPECIALHEIGHTS}, $expr: {$lte: [{ $multiply: ["$weight", 0.98]}, +tRuleMaxWeightSPECIALHEIGHTS]}, height: {$lte: tRuleMaxHeightSPECIALHEIGHTS}, width: {$lte: tRuleMaxWidthSPECIALHEIGHTS}, clength: {$lte: tRuleMaxLengthSPECIALHEIGHTS}},
        {cargoType: {$eq: cRuleNOTPALLETIZED}, $expr: {$lte: [{ $multiply: ["$weight", 0.98]}, +tRuleMaxWeightNOTPALLETIZED]}, height: {$lte: tRuleMaxHeightNOTPALLETIZED}, width: {$lte: tRuleMaxWidthNOTPALLETIZED}, clength: {$lte: tRuleMaxLengthNOTPALLETIZED}},
        {cargoType: {$eq: cRuleSPECIALLENGTHS}, $expr: {$lte: [{ $multiply: ["$weight", 0.98]}, +tRuleMaxWeightSPECIALLENGTHS]}, height: {$lte: tRuleMaxHeightSPECIALLENGTHS}, width: {$lte: tRuleMaxWidthSPECIALLENGTHS}, clength: {$lte: tRuleMaxLengthSPECIALLENGTHS}},

        // 托盘类货物筛选
        {cargoType: {$eq: cRulePALLETIZED}, 
          $expr: {$lte: [{ $multiply: ["$totalPalletsWeight", 0.98]}, tRuleMaxWeightPALLETIZED]}, 
          maxLineWidthEuro: {$lte: totalWidth} 
        }
      ],
  }},
  // 关联路由、用户信息，排序逻辑保持不变
  {
    $lookup: {
      from: Route.collection.name,
      localField: "_id",
      foreignField: "cargoId",
      as: "routes"
    }
  },
  {
    $lookup: {
      from: User.collection.name,
      localField: "userId",
      foreignField: "_id",
      as: "user"
    }
  },
  {
    $sort : {
      date : -1
    }
  }
]);

let fetchedCargos;
if (pageSize && currentPage) {
  cargoQuery.skip(pageSize * (currentPage - 1)).limit(pageSize);
}
cargoQuery
  .then(documents => {
    fetchedCargos = documents;
    // 注意：如果需要返回符合筛选条件的总条数，而非全表总条数，需要调整聚合用$facet同步查询
    return Cargo.count();
  })
  .then(count => {
    res.status(200).json({
      message: "Cargos fetched successfully!",
      cargos: fetchedCargos,
      maxCargos: count
    });
  })
  .catch(error => {
    res.status(500).json({
      message: "Fetching cargos failed!"
    });
  });

原理说明 #

MongoDB的$sum运算符原生支持直接对数组类型的数值字段求和，$sum: "$pallets.palletWidth"会自动提取pallets数组所有元素的palletWidth属性值累加，完全符合你示例中300+99=399的计算需求，不需要额外编写带pipeline的lookup查询。

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