node.js - 如何在 Mongodb 聚合(组)中进行 $sum 和 $filter

Question

尝试将上述数据与组聚合相加总是返回 0。

{
     team: "5d091e8b402c1d089042000d"
     _id: "5d2e63ce89be227d171eb4a7"
     goals: [
      {
        player: "5d20636a4b0bf2670c1e014c"
        createdAt: "1970-01-01T00:18:31.111+00:00"
        value: 1
        _id: 5d2e63ce89be227d171eb4aa
      },
      {
        player: "5d20636a4b0bf2670c1e014c"
        createdAt: "1970-01-01T00:18:31.111+00:00"
        value: 1
        _id: 5d2e63ce89be227d171eb4aa
      }
     ]
    }

    {
     team: "5d091e8b402c1d089042000d"
     _id: "5d2e63ce89be227d171eb4a7"
     goals: [
      {
        player: "5d20636a4b0bf2670c1e014c"
        createdAt: "1970-01-01T00:18:31.111+00:00"
        value: 1
        _id: 5d2e63ce89be227d171eb4aa
      },
      {
        player: "5d20636a4b0bf2670c1e014c"
        createdAt: "1970-01-01T00:18:31.111+00:00"
        value: 1
        _id: 5d2e63ce89be227d171eb4aa
      }
     ]
    }

当前尝试:

{
  _id: "$team",
    total: { 
      "$sum": {
        "$filter": {
            "input": "$goals", 
            "as": "s",
            "cond": { "$eq": [ "$$s.player", "5d20636a4b0bf2670c1e014c"] }
        }  
      }
    }
}

以上返回:

{ _id: 5d20636a4b0bf2670c1e014c, total: 0}

Answer 1

将过滤作为单独的管道阶段移动，然后运行双 $sum 会更容易(内部对单个文档的数组求和，外部聚合跨文档值)

db.collection.aggregate([
    {
        $addFields: {
            goals: {
                $filter: {
                    input: "$goals",
                    as: "g",
                    cond: {
                        $eq: [ "$$g.player", "5d20636a4b0bf2670c1e014c" ]
                    }
                }
            }
        }
    },
    {
        $group: {
            _id: "$team",
            total: { $sum: { $sum: "$goals.value" } }
        }
    }
])

Mongo Playground