Mongodb:如何使用聚合在嵌套字段上构建排名

Question

这是我的收藏：

[
{userId: "u1", data: { score1: 1, score2: 2, score3: 3 }, day: 1},
{userId: "u1", data: { score1: 1, score2: 0, score3: 0 }, day: 2}, 
{userId: "u1", data: { score1: 5, score2: 3, score3: 2 }, day: 3},
{userId: "u2", data: { score1: 2, score2: 5, score3: 1 }, day: 1}, 
{userId: "u2", data: { score1: 1, score2: 1, score3: 6 }, day: 2},
{userId: "u2", data: { score1: 3, score2: 5, score3: 3 }, day: 3},
{userId: "u3", data: { score1: 4, score2: 1, score3: 1 }, day: 1},
{userId: "u3", data: { score1: 0, score2: 1, score3: 1 }, day: 2},
{userId: "u3", data: { score1: 0, score2: 1, score3: 10 }, day: 3}
]

我想建立以下排行榜表格：

{
score1: [
{"u1": 7}, // sum of all score1 for u1
{"u2": 6}, // sum of all score1 for u2
{"u3": 4}, // sum of all score1 for u3
],
score2: [
{"u2": 11}, // sum of all score2 for u2
{"u1": 5}, // sum of all score2 for u1
{"u3": 3}, // sum of all score2 for u3
],
score3: [
{"u3": 12}, // sum of all score3 for u3
{"u2": 10}, // sum of all score3 for u2
{"u1": 5}, // sum of all score3 for u1
],
}

到目前为止，我可以根据userId进行分组，并计算其中3个的每个分数的总和：

db.myCollection.aggregate([
{
$group: {
_id: "$userId",
score1: { $sum: "$score1" },
score2: { $sum: "$score2" },
score3: { $sum: "$score3" }
}
}
])

这给了我：

[
{ 
_id: "u1",
score1: 7,
score2: 5,
score3: 5
},
{ 
_id: "u2",
score1: 6,
score2: 11,
score3: 10
},
{ 
_id: "u3",
score1: 4,
score2: 3,
score3: 12
},
]

如何提取每种类型的分数并建立相应的排行榜？

提前谢谢。

Answer 1

我会首先在data字段上使用$objectToArray，然后在$unwind字段上使用，这样每个文档都有1个用户和1个分数。然后按userId和data.k分组(其中包含"score1"、"score2"等(并计算和。然后按分数名称重新组合，并将一个具有k:userId, v:<score>的对象推送到一个数组中。然后在null上再次分组，并将k:scoreName, v:<object with user scores>推送到阵列。最后$arrayToObject将该数组转换为您想要的对象：

db.collection.aggregate([
{$addFields: {data: {$objectToArray: "$data"}}},
{$unwind: "$data"},
{$group: {
_id: {userId: "$userId", scoreName: "$data.k"},
score: {$sum:"$data.v"}
}},
{$group: {
_id:"$_id.scoreName",
data:{$push:{k:"$_id.userId", v:"$score"}}
}},
{$group: {
_id: null,
scores:{$push:{k:"$_id", v:{$arrayToObject:"$data"}}}
}},
{$replaceRoot:{newRoot:{$arrayToObject:"$scores"}}}
])

游乐场