使用$总和的对象,猫鼬/ MongoDB的计算总价值
问题描述:
我有这样的:
var actions = [
project: 23123,
title: 'Change of windows
energySaving: {
electricity: {
lighting: 24324,
equipment: 23423,
fans: 234234,
distribution: 234324,
chiller: 234234,
other: 234324
},
heating: {
ventilation: 234324,
shell: 23423,
tapWater: 23423
},
}
]);
这是我的查询来获取从行动收集某些字段:
mongoose.model('Action').find({project: project._id})
.select('title description energySaving')
.exec(function(err, actions){
res.status(200).send(actions);
});
})
相反获得整个“energySaving”财产,我想用一个“totalEnergySaving”来代替它,它是所有子对象的总和。是否有可能使用聚合来做到这一点?如果是这样,那可能是$sum
功能。虽然不太确定。
答
由于具有任意字段名称,因此用当前模式设计实现聚合将非常困难。为了做到种类最多的查询和操作,你就需要重新设计你的架构来存储这样的数据:
{
"_id" : ObjectId("54f46f18c36dcc206d0cec38"),
"project" : 23123,
"title" : "Change of windows",
"energySaving" : [
{
"energy" : "electricity",
"type" : "lighting",
"value" : 24324
},
{
"energy" : "electricity",
"type" : "equipment",
"value" : 24324
},
{
"energy" : "electricity",
"type" : "fans",
"value" : 24324
},
{
"energy" : "electricity",
"type" : "distribution",
"value" : 24324
},
{
"energy" : "electricity",
"type" : "chiller",
"value" : 24324
},
{
"energy" : "electricity",
"type" : "other",
"value" : 24324
},
{
"energy" : "heating",
"type" : "ventilation",
"value" : 24324
},
{
"energy" : "heating",
"type" : "shell",
"value" : 24324
},
{
"energy" : "heating",
"type" : "tapWater",
"value" : 24324
}
]
}
然后你就可以汇总得到最终title
,description
和totalEnergySaving
如下:
db.collection.aggregate([
{ $unwind: "$energySaving" },
{
$group: {
_id: {
title: '$title',
description: '$description'
},
totalEnergySaving: { $sum: '$energySaving.value' }
}
},
{
$project: {
_id: 0,
title: '$_id.title',
description: '$_id.description',
totalEnergySaving: 1
}
}
]);
结果:
{
"result" : [
{
"totalEnergySaving" : 218916,
"title" : "Change of windows",
"description" : "Detailed breakdown of energy savings"
}
],
"ok" : 1
}
你说得对。这是一个更灵活的模式。谢谢。 – Per 2015-03-02 16:06:57
我也希望包括“标题”和“描述”,就像你在我的问题中看到的一样。你能否更新你的答案,这也包括在内? – Per 2015-03-02 17:42:41
我已经更新了包含该答案的答案 – chridam 2015-03-02 19:49:20