我有一个具有以下结构的mongo集合
{
"userId" : ObjectId("XXX"),
"itemId" : ObjectId("YYY"),
"resourceId" : 1,
"_id" : ObjectId("528455229486ca3606004ec9"),
"parameter" : [
{
"name" : "name1",
"value" : 150,
"_id" : ObjectId("528455359486ca3606004eed")
},
{
"name" : "name2",
"value" : 0,
"_id" : ObjectId("528455359486ca3606004eec")
},
{
"name" : "name3",
"value" : 2,
"_id" : ObjectId("528455359486ca3606004eeb")
}
]
}
可以有多个文档具有相同的'useId'和不同的'itemId',但参数在所有文档中都具有相同的键/值对.
我想要完成的是为每个唯一的"userId"返回聚合参数"name1","name2"和"name3"而忽略'itemId'.所以每个用户的最终结果都是这样的:
{
"userId" : ObjectId("use1ID"),
"name1" : (aggregatedValue),
"name2" : (aggregatedValue),
"name3" : (aggregatedVAlue)
},
{
"userId" : ObjectId("use2ID"),
"name1" : (aggregatedValue),
"name2" : (aggregatedValue),
"name3" : (aggregatedVAlue)
}
是否可以使用mongoDB的聚合方法来实现这一点？你能帮我建一个正确的查询来完成吗？
最简单的形式是通过"参数""名称"保持键入:
db.collection.aggregate(
// Unwind the array
{ "$unwind": "$parameter"},
// Group on the "_id" and "name" and $sum "value"
{ "$group": {
"_id": {
"userId": "$userId",
"name": "$parameter.name"
},
"value": { "$sum": "$parameter.value" }
}},
// Put things into an array for "nice" processing
{ "$group": {
"_id": "$_id.userId",
"values": { "$push": {
"name": "$_id.name",
"value": "$value"
}}
}}
)
如果您确实需要将名称的"值"作为字段值,则可以执行以下操作.但是,由于您正在"投影"字段/属性,因此您必须在代码中全部指定它们.你不能再"动态"了,你正在编码/生成每一个:
db.collection.aggregate([
// Unwind the array
{ "$unwind": "$parameter"},
// Group on the "_id" and "name" and $sum "value"
{ "$group": {
"_id": {
"userId": "$userId",
"name": "$parameter.name"
},
"value": { "$sum": "$parameter.value"}
}},
// Project out discrete "field" names with $cond
{ "$project": {
"name1": { "$cond": [
{ "$eq": [ "$_id.name", "name1" ] },
"$value",
0
]},
"name2": { "$cond": [
{ "$eq": [ "$_id.name", "name2" ] },
"$value",
0
]},
"name3": { "$cond": [
{ "$eq": [ "$_id.name", "name3" ] },
"$value",
0
]},
}},
// The $cond put "0" values in there. So clean up with $group and $sum
{ "$group": {
_id: "$_id.userId",
"name1": { "$sum": "$name1" },
"name2": { "$sum": "$name2" },
"name3": { "$sum": "$name3" }
}}
])
因此,虽然额外的步骤可以为您提供所需的结果(以及最终项目更改_id为userId),但我认为短版本足够可行,除非您确实需要它.考虑那里的输出:
{
"_id" : ObjectId("53245016ea402b31d77b0372"),
"values" : [
{
"name" : "name3",
"value" : 2
},
{
"name" : "name2",
"value" : 0
},
{
"name" : "name1",
"value" : 150
}
]
}
所以这将是我个人使用的.但是你的选择.
京公网安备 11010802040832号 | 京ICP备19059560号-6 