英文:
MongoDB - Group by inner element
问题
我正在用一个简单的例子来解释,看看我的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 },
}
},
]
从集合中,我需要计算每个用户的Level 1、Level 2和Level 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 } }
]
英文:
I am explaining with a simple example see my MongoDB collection looks like this:
[
{
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 },
}
},
]
From the collection, I need the sum of Level 1, Level 2, and Level 3 for each user.
The query result should look like this:
[
{ 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
得分: 1
$set
: 将LevelDetails
转换为键值对,并添加新字段_levelDetails
。$unwind
: 展开_levelDetails
数组。$group
: 根据_levelDetails.v.userId
进行分组,并根据级别(_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': { // 结果 } }
步骤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"
}
}
}
])
英文:
$set
: Add new field_levelDetails
by convertingLevelDetails
to key-value pair.$unwind
: Deconstruct_levelDetails
array.$group
: Group by_levelDetails.v.userId
and$sum
conditionally based on level (_levelDetails.k
).$project
: Format displayed document.$sort
(Optional): Sort byuserID
ascending.
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
}
}
])
> To Key-Value Pair: { 'userId': { // Result } }
Steps 1 to 3 are the same as the previous solution.
$sort
(Optional): Sort by_id
ascending.$project
: Display document witharray
field (with propertiesk
andv
).$replaceRoot
: Replace entire documents to key (userId
) and value (result).
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"
}
}
}
])
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论