问题描述
使用MongoDB聚合框架时是否可以合并数组字段?这是我要解决的摘要问题:
Is it possible to merge array fields in while using MongoDB aggregation framework? Here is a summary problem I am trying to solve:
抽样汇总输入文档:
{
"Category" : 1,
"Messages" : ["Msg1", "Msg2"],
"Value" : 1
},
{
"Category" : 1,
"Messages" : [],
"Value" : 10
},
{
"Category" : 1,
"Messages" : ["Msg1", "Msg3"],
"Value" : 100
},
{
"Category" : 2,
"Messages" : ["Msg4"],
"Value" : 1000
},
{
"Category" : 2,
"Messages" : ["Msg5"],
"Value" : 10000
},
{
"Category" : 3,
"Messages" : [],
"Value" : 100000
}
我们想按类别"分组,同时总结价值"并合并消息".我已经尝试过以下聚合管道:
We want to group by 'Category' while summing up 'Value' and merging 'Messages'. I have tried this aggregation pipeline:
{group : {
_id : "$Category",
Value : { $sum : "$Value"},
Messages : {$push : "$Messages"}
}
},
{$unwind : "$Messages"},
{$unwind : "$Messages"},
{$group : {
_id : "$_id",
Value : {$first : "$Value"},
Messages : {$addToSet : "$Messages"}
}
}
结果是:
"result" : [{
"_id" : 1,
"Value" : 111,
"Messages" : ["Msg3", "Msg2", "Msg1"]
},
{
"_id" : 2,
"Value" : 11000,
"Messages" : ["Msg5", "Msg4"]
}
]
但是,这完全错过了类别3,因为类别"为3的文档没有任何消息",并且在第二次展开时将其删除.我们希望结果也包括以下内容:
However, this completely misses Category 3 since the documents where 'Category' is 3 do not have any 'Messages' and they are dropped by the second unwind. We would like the result to include the following as well:
{
"_id" : 3,
"Value" : 100000,
"Messages" : []
}
是否有通过聚合框架实现此目标的灵巧方法?
Is there a neat way of achieving this by the aggregation framework?
推荐答案
如果要确保Messages是数组,则可以使用以下技巧:
Here is a trick you can use if Messages is guaranteed to be an array:
> db.messages.find()
{ "Category" : 1, "Messages" : [ "Msg1", "Msg2" ], "Value" : 1 }
{ "Category" : 1, "Messages" : [ ], "Value" : 10 }
{ "Category" : 1, "Messages" : [ "Msg1", "Msg3" ], "Value" : 100 }
{ "Category" : 2, "Messages" : [ "Msg4" ], "Value" : 1000 }
{ "Category" : 2, "Messages" : [ "Msg5" ], "Value" : 10000 }
{ "Category" : 3, "Messages" : [ ], "Value" : 100000 }
> var group1 = {
"$group": {
"_id": "$Category",
"Value": {
"$sum": "$Value"
},
"Messages": {
"$push": "$Messages"
}
}
};
> var project1 = {
"$project": {
"Value": 1,
"Messages": {
"$cond": [
{
"$eq": [
"$Messages",
[ [ ] ]
]
},
[ [ null ] ],
"$Messages"
]
}
}
};
> db.messages.aggregate( group1, project1 )
{ "_id" : 3, "Value" : 100000, "Messages" : [ [ null ] ] }
{ "_id" : 2, "Value" : 11000, "Messages" : [ [ "Msg4" ], [ "Msg5" ] ] }
{ "_id" : 1, "Value" : 111, "Messages" : [ [ "Msg1", "Msg2" ], [ ], [ "Msg1", "Msg3" ] ] }
现在放开两次,然后重新分组以获得单个Messages数组.
Now unwind twice and re-group to get a single Messages array.
> var unwind = {"$unwind":"$Messages"};
> var group2 = {
$group: {
"_id": "$_id",
"Value": {
"$first": "$Value"
},
"Messages": {
"$addToSet": "$Messages"
}
}
};
> var project2 = {
"$project": {
"Category": "$_id",
"_id": 0,
"Value": 1,
"Messages": {
"$cond": [
{
"$eq": [
"$Messages",
[ null ]
]
},
[ ],
"$Messages"
]
}
}
};
> db.messages.aggregate(group1, project1, unwind, unwind, group2 ,project2 )
{ "Value" : 111, "Messages" : [ "Msg3", "Msg2", "Msg1" ], "Category" : 1 }
{ "Value" : 11000, "Messages" : [ "Msg5", "Msg4" ], "Category" : 2 }
{ "Value" : 100000, "Messages" : [ ], "Category" : 3 }
这篇关于在MongoDB聚合中合并数组字段的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!