Mongodb和mongoose聚合查询
mongdb查询某一字段sum值
需求说明:articles有一个字段pv记录了该文章的访问量,现在要统计所有文章访问量,类似于sql中的sum统计
mongodb中查询
select sum(pv) from articles;
db.articles.aggregate([{$group:{_id:null,pv:{$sum:"$pv"}}}]);
结果:{ "_id" : null, "pv" : 2 }
select sum(pv) from articles where createDate <= '2016-10-20';
db.articles.aggregate([{$match:{createDate:{$lte:"2016-10-20"}}},{$group:{_id:null,pv:{$sum:"$pv"}}}]);
结果:{ "_id" : null, "pv" : 9 }
select sum(pv) from articles where category = 'Nodejs';
db.articles.aggregate([{$match:{category:"Nodejs"}},{$group:{_id:null,pv:{$sum:"$pv"}}}]);
结果:{ "_id" : null, "pv" : 7 }
需要注意$match
和$group
的顺序,反了是不行的,因为这是Aggregation Pipeline(管道流)
mongoose实现方式,与上面sql的顺序对应:
Article.aggregate({ $group: { _id: null, pvCount: { $sum: '$pv' }}}, function(err, doc) {
console.log("1", doc);
});
Article.aggregate([{$match:{createDate:{$lte:"2016-10-20"}}},{$group:{_id:null, pvCount:{$sum:"$pv"}}}], function(err, doc) {
console.log("2", doc);
});
Article.aggregate([{$match:{category:"Nodejs"}},{$group:{_id:null, pvCount:{$sum:"$pv"}}}], function(err, doc) {
console.log("3", doc);
});
1 [ { _id: null, pv: 25 } ]
2 [ { _id: null, pv: 9 } ]
3 [ { _id: null, pv: 7 } ]
参考文档:
https://docs.mongodb.com/manual/aggregation/
https://docs.mongodb.com/manual/reference/operator/aggregation/#aggregation-expression-operators