我用一个简单的例子来解释，我的 MongoDB 集合看起来像这样：[ { pid: erwer, qty: 3, LevelDetails: { level1: { userId: 1, amount: 10 }, level2: { userId: 2, amount: 20 }, level3: { userId: 3, amount: 13 }, } }, { pid: qwsdfg, qty: 1, LevelDetails: { level1: { userId: 1, amount: 10 }, level2: { userId: 4, amount: 20 }, level3: { userId: 3, amount: 13 }, } },]从集合中，我需要每个用户的级别 1、级别 2 和级别 3 的总和。查询结果应如下所示：[ { userId1: { TotalLevel1Amount: 20, TotalLevel2Amount: 0, TotalLevel3Amount: 0 } }, { userId2: { TotalLevel1Amount: 0, TotalLevel2Amount: 20, TotalLevel3Amount: 0 } }, { userId3: { TotalLevel1Amount: 0, TotalLevel2Amount: 0, TotalLevel3Amount: 26 } }, { userId4: { TotalLevel1Amount: 0, TotalLevel2Amount: 20, TotalLevel3Amount: 0 } }]
1 回答
汪汪一只猫
TA贡献1898条经验 获得超8个赞
$set_levelDetails：通过转换LevelDetails为键值对来添加新字段。$unwind: 解构_levelDetails数组。$group: 分组_levelDetails.v.userId并$sum有条件地基于级别 (_levelDetails.k)。$project: 格式化显示的文件。$sort（可选）：按userID升序排序。
db.collection.aggregate([
{
$set: {
_levelDetails: {
$objectToArray: "$LevelDetails"
}
}
},
{
$unwind: "$_levelDetails"
},
{
$group: {
_id: "$_levelDetails.v.userId",
"TotalLevel1Amount": {
$sum: {
$cond: [
{
"$eq": [
"$_levelDetails.k",
"level1"
]
},
"$_levelDetails.v.amount",
0
]
}
},
"TotalLevel2Amount": {
$sum: {
$cond: [
{
"$eq": [
"$_levelDetails.k",
"level2"
]
},
"$_levelDetails.v.amount",
0
]
}
},
"TotalLevel3Amount": {
$sum: {
$cond: [
{
"$eq": [
"$_levelDetails.k",
"level3"
]
},
"$_levelDetails.v.amount",
0
]
}
}
}
},
{
$project: {
_id: 0,
userId: "$_id",
TotalLevel1Amount: 1,
TotalLevel2Amount: 1,
TotalLevel3Amount: 1
}
},
{
$sort: {
userId: 1
}
}
])
对于键值对：
{ 'userId': { // Result } }
步骤 1 到 3 与前面的解决方案相同。
$sort（可选）：按_id升序排序。$project：显示带有array字段的文档（带有属性k和v）。$replaceRoot：将整个文档替换为键（userId）和值（结果）。
db.collection.aggregate([
{
$set: {
_levelDetails: {
$objectToArray: "$LevelDetails"
}
}
},
{
$unwind: "$_levelDetails"
},
{
$group: {
_id: "$_levelDetails.v.userId",
"TotalLevel1Amount": {
$sum: {
$cond: [
{
"$eq": [
"$_levelDetails.k",
"level1"
]
},
"$_levelDetails.v.amount",
0
]
}
},
"TotalLevel2Amount": {
$sum: {
$cond: [
{
"$eq": [
"$_levelDetails.k",
"level2"
]
},
"$_levelDetails.v.amount",
0
]
}
},
"TotalLevel3Amount": {
$sum: {
$cond: [
{
"$eq": [
"$_levelDetails.k",
"level3"
]
},
"$_levelDetails.v.amount",
0
]
}
}
}
},
{
$sort: {
_id: 1
}
},
{
$project: {
array: [
{
k: {
$toString: "$_id"
},
v: {
TotalLevel1Amount: "$TotalLevel1Amount",
TotalLevel2Amount: "$TotalLevel2Amount",
TotalLevel3Amount: "$TotalLevel3Amount"
}
}
]
}
},
{
"$replaceRoot": {
newRoot: {
$arrayToObject: "$array"
}
}
}
])
- 1 回答
- 0 关注
- 219 浏览
添加回答
举报
0/150
提交
取消