node.js - 在带有 MongoDB 聚合的字典数组上使用 $mergeObjects

Question

我正在尝试收集每个用户的任务统计数据的总累积值(按城市分组)。

这是我的输入:

[
    {
        "missions": {
            "Denver": {
                "savedTowers": 3,
                "savedCity": 0,
                "hoursFasted": 68,
                "fastPointsEarned": 4080
            },
            "Boston": {
                "savedTowers": 2,
                "savedCity": 0,
                "hoursFasted": 32,
                "fastPointsEarned": 1920
            }
        }
    },
    {
        "missions": {
            "Denver": {
                "savedTowers": 4,
                "savedCity": 0,
                "hoursFasted": 87,
                "fastPointsEarned": 5220
            },
            "Boston": {
                "savedTowers": 7,
                "savedCity": 1,
                "hoursFasted": 120,
                "fastPointsEarned": 7200
            }
        }
    }
]

这是代码:

db.collection("users").aggregate([
    {
        "$match": {
            "missions": {
                "$exists": true,
                "$gt": {}
            }
        }
    },
    {
        "$project": {
            "_id": 0,
            "city": {
                "$objectToArray": "$missions"
            }
        }
    },
    {
        "$unwind" : "$city"
    },
    {
        "$group": {
            "_id": {
                "city": "$city.k"
            },
            "cities": {
                "$addToSet": "$city.k"
            },
            "stats": {
                "$addToSet": "$city.v"
            },
            "players": {
                "$sum": 1
            }
        }
    },
    {
        "$project": {
            "_id": 0,
            "city": "$_id.city",
            "stats": {
                "$mergeObjects": "$stats"
            },
            "players": "$players"
        }
    }
]).toArray(function(err, response) {
    if (err != null) {
        console.log("Error: " + err.message);
        handleError(res, "Failed to fetch Mission Analytics", err.message);
    } else {
        res.status(200).send({ "mission_stats": response });                
    }
});

这是实际输出:

{
    "mission_stats": [
        {
            "city": "Boston",
            "stats": {
                "savedTowers": 2,
                "savedCity": 0,
                "hoursFasted": 32,
                "fastPointsEarned": 1920
            },
            "players": 2
        },
        {
            "city": "Denver",
            "stats": {
                "savedTowers": 3,
                "savedCity": 0,
                "hoursFasted": 68,
                "fastPointsEarned": 4080
            },
            "players": 2
        }
    ]
}

这是预期的输出:

{
    "mission_stats": [
        {
            "city": "Boston",
            "stats": {
                "savedTowers": 9,
                "savedCity": 0,
                "hoursFasted": 152,
                "fastPointsEarned": 9120
            },
            "players": 2
        },
        {
            "city": "Denver",
            "stats": {
                "savedTowers": 7,
                "savedCity": 0,
                "hoursFasted": 155,
                "fastPointsEarned": 9300
            },
            "players": 2
        }
    ]
}

为什么 $mergeObjects 将统计数据数组缩减为一个对象，但也未能合并这些值？我没有在最终合并对象中看到累积值。

Answer 1

您将使用最后一个 $mergeObjects 操作覆盖统计信息。

您可以尝试以下聚合(未测试)

您必须将值对象转换为键值对数组，然后使用 $unwind+$group 按每个键进行分组并累积统计信息。返回命名键值对象的最后一步。

db.colname.aggregate([
  /** match stage **/
  {"$project":{"city":{"$objectToArray":"$missions"}}},
  {"$unwind":"$city"},
  {"$addFields":{"city-v":{"$objectToArray":"$city.v"}}},
  {"$unwind":"$city-v"},
  {"$group":{
    "_id":{"id":"$city.k","key":"$city-v.k"},
    "stats":{"$sum":"$city-v.v"}
  }},
  {"$group":{
    "_id":"$_id.id",
    "players":{"$sum":1},
    "stats":{"$mergeObjects":{"$arrayToObject":[[["$_id.key","$stats"]]]}}
  }}
])