MongoDB聚合查询无法按scac统计延迟订单数，如何修正查询语句？

原需求 #

期望输出格式为 RSPI:348，按scac（对应字段为dqFact）分组，统计每个scac对应的延迟总时长。

原有查询代码 #

aggregate([
    { 
      '$match' : { 
        'salesOrder.shipDate' : { 
           '$gte' : '2021-03-18T00:00:00', 
           '$lte' : '2021-03-18T23:59:59'
         }
       }
    },
    { "$match": { "dqCategory" : "shipmentdata" } },
    { "$match": { "dqState" : "fail" } },
    { "$match": { "salesOrder.region" : "US" } },
    { 
      "$group" : {
        "_id": {
          "scac": "$dqFact",
          "shipDelay": "$dqMeasure.shippeddelay"
        }
      }
    },
    { 
      "$project": { 
        "_id": 0,
        "dqMeasure.shippeddelay": 1,
        "dqFact":1
       } 
    }
])

原有查询返回结果 #

{
    "_id" : {
        "scac" : "RPSI",
        "shipDelay" : "31"
    }
}

/* 第二条返回结果 */
{
    "_id" : {
        "scac" : "RPSI",
        "shipDelay" : "317"
    }
}

错误原因 #

1. 分组阶段将shipDelay也放入了_id分组键，导致同一个scac的不同延迟值被拆分为多个独立分组，没有执行聚合计算
2. 未使用$sum累加器统计延迟总时长
3. 投影阶段字段映射错误，分组后的所有字段都存储在_id对象中，直接取顶层的dqFact和dqMeasure.shippeddelay无法获取到对应值

修正后查询代码 #

aggregate([
    // 合并所有匹配条件，减少聚合阶段提升执行效率
    {
        $match: {
            "salesOrder.shipDate": {
                $gte: "2021-03-18T00:00:00",
                $lte: "2021-03-18T23:59:59"
            },
            "dqCategory": "shipmentdata",
            "dqState": "fail",
            "salesOrder.region": "US"
        }
    },
    // 仅按scac（对应dqFact字段）分组，累加所有延迟时长
    {
        $group: {
            _id: "$dqFact",
            totalDelay: {
                $sum: { $toInt: "$dqMeasure.shippeddelay" } // 先将字符串类型的延迟值转为整数再求和
            }
        }
    },
    // 调整输出为要求的嵌套结构
    {
        $project: {
            _id: {
                $arrayToObject: [ 
                    [ { k: "$_id", v: "$totalDelay" } ] 
                ]
            }
        }
    }
])

修正后输出结果 #

{
    "_id" : {
        "RSPI": 348
    }
}

如果不需要外层的_id包装，可将最后一个投影阶段替换为$replaceRoot阶段，直接返回{"RSPI":348}格式的结果：

{
    $replaceRoot: {
        newRoot: {
            $arrayToObject: [
                [ { k: "$_id", v: "$totalDelay" } ]
            ]
        }
    }
}

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