MongoDB的聚合管道操作
在demo库中col集合中,要求以书名title分组,求每种书的likes总数。
db.col.aggregate([{$group:{_id:"$title",likes:{$sum:"$likes"}}}])
在demo库中col集合中,要求按照书籍的标题(title)进行分组,并求字段likes的平均值。
db.col.aggregate([{$group:{_id:"$title",likes:{$avg:"$likes"}}}])
在demo库中的对col集合中,查询likes大于50且按title分组统计各组书籍的册数counter。
db.col.aggregate([{$match:{likes:{$gt:50}}},{$group:{_id:"$title",counter:{$sum:1}}}])
在demo库中对col集合中,查询likes小于600 且按书籍名称title分组,统计各组书籍的册数counter,且结果要按照书籍册数降序排序。
db.col.aggregate([{$match:{likes:{$lt:600}}},{$group:{_id:"$title",counter:{$sum:1}}},{$sort:{counter:-1}}])
在demo库中对col集合中,查询likes大于50且按书籍名称title分组,统计各组书籍的册数,并显示投影为counter字段,结果要按照counter字段升序排序,限制输出3条查询结果,并跳过第1条记录。
db.col.aggregate([{$match:{likes:{$gt:50}}},{$group:{_id:"$title",counter:{$sum:1}}},{$sort:{counter:1}},{$limit:3},{$skip:1}])
在demo库中对col集合中by字段进行拆分。by中数据的格式为:by:[{first:'Obama'},{second:'Trump'}]。(提示:使用$unwind聚合操作符)
db.col.aggregate([{$match:{path:"[{first:'Obama'},{second:'Trump'}]"}}])
对col集合中的前三条文档按照title分组,统计likes值的总和。
db.col.aggregate([{$limit:3},{$group:{_id:"$title",counter:{$sum:'$likes'}}}])
使用mapReduce(),在demo库中对col集合查询likes值在[100,400]之间的文档,并按照title分组统计每种书的册数,并保存在col_counter集合中。
db.col.mapReduce(
function() {emit(this.title,1);},
function(key,values){return Array.sum(values)},
{
query:{$and:[{likes:{$gte:100}},{likes:{$lte:400}}]},
out: 'col_counter'
}
)
使用mapReduce(),在demo库中对col集合查询likes值在[100,400]之间的文档,并按照title分组统计每种书的likes值的总和,并保存在col_total集合中。
db.col.mapReduce(
function() {emit(this.title,this.likes);},
function(key,values){return Array.sum(values)},
{
query:{$and:[{likes:{$gte:100}},{likes:{$lte:400}}]},
out: 'col_total'
}
)
数据库结构和数据:
// ----------------------------
// Collection structure for col
// ----------------------------
db.getCollection("col").drop();
db.createCollection("col");
// ----------------------------
// Documents of col
// ----------------------------
db.getCollection("col").insert([ {
_id: ObjectId("6332627592f1167a86bae2b0"),
title: "MongoDB 教程",
description: "MongoDB是Nosql 数据库的代表之一",
by: "Turing",
url: "‘http://www.baidu.com’",
tags: "['mongodb', database', 'NoSQL']",
likes: NumberInt("100")
} ]);
db.getCollection("col").insert([ {
_id: ObjectId("6332627592f1167a86bae2b1"),
title: "MongoDB 教程1",
description: "本书讲解了MongoDB的基本概念与实际操作,MongoDB是一种 Nosql 数据库",
by: "[{first:'Obama'},{second:'Tom'}]",
url: "'http://www.zzti.edu.cn’",
tags: "['mongodb', 'database', 'NoSQL']",
likes: NumberInt("200")
} ]);
db.getCollection("col").insert([ {
_id: ObjectId("6332636f92f1167a86bae2b2"),
title: "MongoDB 数据库开发基础实践",
description: "这是一本学习MongoDB数据库开发的基础教材",
by: "Smith",
url: "'http://www.tinghua.edu.cn'",
tags: "['mongodb', 'database', 'NoSQL']",
likes: NumberInt("300")
} ]);
db.getCollection("col").insert([ {
_id: ObjectId("6332636f92f1167a86bae2b3"),
title: "MongoDB 数据库开发基础实践",
description: "这是一本关于MongoDB数据库的深入与浅出的书籍",
by: "Turing",
url: "'http://www.tinghua.edu.cn'",
tags: "['mongodb', 'database', 'NoSQL']",
likes: NumberInt("400")
} ]);
db.getCollection("col").insert([ {
_id: ObjectId("633263fd92f1167a86bae2b4"),
title: "MongoDB 高级编程",
description: "这是一本MongoDB高级编程的书籍,适合于有一定基础的读者阅读",
by: "[{first:'Obama'},{second:'Trump'}]",
url: "'http://www.sohu.com'",
tags: "['mongodb', 'database', 'NoSQL']",
likes: NumberInt("450")
} ]);
db.getCollection("col").insert([ {
_id: ObjectId("6344139b4b7f5227ab5d0502"),
by: "Tom"
} ]);
// ----------------------------
// Collection structure for col_counter
// ----------------------------
db.getCollection("col_counter").drop();
db.createCollection("col_counter");
// ----------------------------
// Documents of col_counter
// ----------------------------
db.getCollection("col_counter").insert([ {
_id: "MongoDB 教程",
value: 1
} ]);
db.getCollection("col_counter").insert([ {
_id: "MongoDB 教程1",
value: 1
} ]);
db.getCollection("col_counter").insert([ {
_id: "MongoDB 数据库开发基础实践",
value: 2
} ]);
// ----------------------------
// Collection structure for col_total
// ----------------------------
db.getCollection("col_total").drop();
db.createCollection("col_total");
// ----------------------------
// Documents of col_total
// ----------------------------
db.getCollection("col_total").insert([ {
_id: "MongoDB 教程1",
value: 200
} ]);
db.getCollection("col_total").insert([ {
_id: "MongoDB 数据库开发基础实践",
value: 700
} ]);
db.getCollection("col_total").insert([ {
_id: "MongoDB 教程",
value: 100
} ]);
// ----------------------------
// Collection structure for comment
// ----------------------------
db.getCollection("comment").drop();
db.createCollection("comment");
// ----------------------------
// Documents of comment
// ----------------------------
db.getCollection("comment").insert([ {
_id: "1",
articleid: "100001",
content: "清晨,我们不应该把时间浪费在手机上,健康很重要,喝一杯温水,幸福你我他。",
userid: "1002",
nickname: "相忘与江湖",
age: "25",
phone: {
homephone: "82174911",
mobilephone: "13065840128"
},
createdatetime: ISODate("2020-01-02T01:08:15.000Z"),
likenum: NumberInt("1000"),
state: "1"
} ]);
db.getCollection("comment").insert([ {
_id: "2",
articleid: "100002",
content: "我夏天空腹喝凉开水,冬天喝温开水。",
userid: "1003",
nickname: "伊人憔悴",
age: "22",
phone: "13442031624",
createdatetime: ISODate("2020-01-02T02:20:40.000Z"),
likenum: NumberInt("888"),
state: "1"
} ]);
db.getCollection("comment").insert([ {
_id: "3",
articleid: "100001",
content: "冬天和夏天我都喝凉开水。",
userid: "1004",
nickname: "杰克船长",
age: "28",
phone: {
homephone: "62771541",
mobilephone: "13262984142"
},
createdatetime: ISODate("2020-01-03T07:10:37.000Z"),
likenum: NumberInt("3000"),
state: "1"
} ]);
db.getCollection("comment").insert([ {
_id: "4",
articleid: "100001",
content: "专家说不能空腹喝冰水,影响健康。",
userid: "1005",
nickname: "罗密欧",
age: "18",
phone: "15813134403",
createdatetime: ISODate("2020-01-03T03:26:29.000Z"),
likenum: NumberInt("2000"),
state: "1"
} ]);
db.getCollection("comment").insert([ {
_id: "5",
articleid: "100001",
content: "研究表明,刚烧开的水千万不要喝,因为烫嘴。",
userid: "1005",
nickname: "罗密欧",
age: "18",
phone: "15813134403",
createdatetime: ISODate("2020-01-03T07:10:37.000Z"),
likenum: NumberInt("3000"),
state: "1"
} ]);
db.getCollection("comment").insert([ {
_id: "6",
articleid: "100001",
content: "喝水是生命体通过口腔摄入水分的方式,人体每天通过口腔摄入的液体大约有2升。",
userid: "1006",
nickname: "爱德华",
age: "30",
phone: "15813134403",
createdatetime: ISODate("2020-01-03T07:10:37.000Z"),
likenum: NumberInt("3000"),
state: "1"
} ]);