mongodb高级操作

shilinkun
2022-05-08 / 0 评论 / 74 阅读 / 正在检测是否收录...
博客网址:www.shicoder.top
微信:kj11011029
欢迎加群聊天 :452380935

上一次我们对mongodb的一些简单命令进行讲解,这一次我们来看下mongodb的高级操作吧

聚合操作

所谓聚合操作,就是类似mysql中的whereorder by等一些操作,MongoDB 的聚合框架就是将文档输入处理管道(pipeline),在管道内完成对文档的操作(包括多个stage)

db.集合.aggregate(
    [
        {stage1},
        {stage2},
        ...
    ]
    {option}
);

img

主要有以下几个阶段

  • $match用于过滤数据,用于聚合阶段的输入
  • $order用指定的键,对文档进行排序
  • $limit用于限制多少个文档作为输入
  • $skip跳过多少个文档
  • $project投影字段,可以理解为查询多少个字段,类似为 select a,b,c 中的 a,b,c
  • $group进行分组操作,其中_id字段用于指定需要分组的字段。
  • $count返回这个聚合管道阶段的文档的数量

数据准备

首先为了后续的案例分析,我们先准备一些数据

var tags = ["nosql","mongodb","document","developer","popular"];
var types = ["technology","sociality","travel","novel","literature"];
var books=[];

for(var i=0;i<50;i++){
    var typeIdx = Math.floor(Math.random()*types.length);
    var tagIdx = Math.floor(Math.random()*tags.length);
    var tagIdx2 = Math.floor(Math.random()*tags.length);
    var favCount = Math.floor(Math.random()*100);
    var username = "xx00"+Math.floor(Math.random()*10);
    var age = 20 + Math.floor(Math.random()*15);
    var book = {
        title: "book-"+i,
        type: types[typeIdx],
        tag: [tags[tagIdx],tags[tagIdx2]],
        favCount: favCount,
        author: {name:username,age:age}
    };
    books.push(book)
}
db.books.insertMany(books);
> use book
switched to db book
> load("book.js")
true

$match

$match用于对文档进行筛选,之后可以在得到的文档子集上做聚合,$match可以使用除了地理空间之 外的所有常规查询操作符,在实际应用中尽可能将\$match放在管道的前面位置。这样有两个好处:一是 可以快速将不需要的文档过滤掉,以减少管道的工作量;二是如果再投射和分组之前执行$match,查询 可以使用索引

比如查询typetechnology的书籍

db.books.aggregate([{$match:{type:"technology"}}])
{ "_id" : ObjectId("6271102510f566b3b1a8dba5"), "title" : "book-0", "type" : "technology", "tag" : [ "mongodb", "developer" ], "favCount" : 72, "author" : { "name" : "xx002", "age" : 26 } }
{ "_id" : ObjectId("6271102510f566b3b1a8dbb0"), "title" : "book-11", "type" : "technology", "tag" : [ "popular", "nosql" ], "favCount" : 61, "author" : { "name" : "xx000", "age" : 32 } }
{ "_id" : ObjectId("6271102510f566b3b1a8dbb5"), "title" : "book-16", "type" : "technology", "tag" : [ "document", "developer" ], "favCount" : 50, "author" : { "name" : "xx000", "age" : 32 } }
{ "_id" : ObjectId("6271102510f566b3b1a8dbb9"), "title" : "book-20", "type" : "technology", "tag" : [ "document", "developer" ], "favCount" : 42, "author" : { "name" : "xx007", "age" : 20 } }
{ "_id" : ObjectId("6271102510f566b3b1a8dbbc"), "title" : "book-23", "type" : "technology", "tag" : [ "mongodb", "popular" ], "favCount" : 4, "author" : { "name" : "xx005", "age" : 21 } }
{ "_id" : ObjectId("6271102510f566b3b1a8dbbd"), "title" : "book-24", "type" : "technology", "tag" : [ "developer", "mongodb" ], "favCount" : 1, "author" : { "name" : "xx007", "age" : 29 } }
{ "_id" : ObjectId("6271102510f566b3b1a8dbc7"), "title" : "book-34", "type" : "technology", "tag" : [ "developer", "document" ], "favCount" : 80, "author" : { "name" : "xx001", "age" : 20 } }
{ "_id" : ObjectId("6271102510f566b3b1a8dbcc"), "title" : "book-39", "type" : "technology", "tag" : [ "document", "document" ], "favCount" : 48, "author" : { "name" : "xx009", "age" : 25 } }
{ "_id" : ObjectId("6271102510f566b3b1a8dbcd"), "title" : "book-40", "type" : "technology", "tag" : [ "developer", "document" ], "favCount" : 63, "author" : { "name" : "xx000", "age" : 25 } }
{ "_id" : ObjectId("6271102510f566b3b1a8dbce"), "title" : "book-41", "type" : "technology", "tag" : [ "popular", "developer" ], "favCount" : 29, "author" : { "name" : "xx007", "age" : 27 } }
{ "_id" : ObjectId("6271102510f566b3b1a8dbd1"), "title" : "book-44", "type" : "technology", "tag" : [ "document", "mongodb" ], "favCount" : 64, "author" : { "name" : "xx007", "age" : 31 } }
{ "_id" : ObjectId("6271102510f566b3b1a8dbd6"), "title" : "book-49", "type" : "technology", "tag" : [ "mongodb", "nosql" ], "favCount" : 35, "author" : { "name" : "xx009", "age" : 24 } }

$count

计数并返回与查询匹配的结果数

db.books.aggregate([ {$match:{type:"technology"}}, {$count: "type_count"}
])

$match阶段筛选出type匹配technology的文档,并传到下一阶段;

$count阶段返回聚合管道中剩余文档的计数,并将该值分配给type_count

{ "type_count" : 12 }

$group

按指定的表达式对文档进行分组,并将每个不同分组的文档输出到下一个阶段。输出文档包含一个\_id字 段,该字段按键包含不同的组。 输出文档还可以包含计算字段,该字段保存由$group的\_id字段分组的一些accumulator表达式的值。 $group不会输出具体的文档而只是统计信息

{ $group: { _id: <expression>, <field1>: { <accumulator1> : <expression1> }, ...
} }

查询book的数量、收藏总数和平均值

db.books.aggregate([
{$group:{_id:null,count:{$sum:1},pop:{$sum:"$favCount"},avg:{$avg:"$favCount"}}}
])
{ "_id" : null, "count" : 50, "pop" : 2452, "avg" : 49.04 }

统计每个作者的book收藏总数

db.books.aggregate([
{$group:{_id:"$author.name",pop:{$sum:"$favCount"}}}
])
{ "_id" : "xx001", "pop" : 176 }
{ "_id" : "xx006", "pop" : 90 }
{ "_id" : "xx000", "pop" : 531 }
{ "_id" : "xx007", "pop" : 213 }
{ "_id" : "xx008", "pop" : 193 }
{ "_id" : "xx005", "pop" : 353 }
{ "_id" : "xx009", "pop" : 175 }
{ "_id" : "xx003", "pop" : 302 }
{ "_id" : "xx004", "pop" : 200 }
{ "_id" : "xx002", "pop" : 219 }

统计每个作者的每本book的收藏数

db.books.aggregate([
{$group:{_id:{name:"$author.name",title:"$title"},pop:{$sum:"$favCount"}}}
])
{ "_id" : { "name" : "xx008", "title" : "book-48" }, "pop" : 16 }
{ "_id" : { "name" : "xx000", "title" : "book-46" }, "pop" : 31 }
{ "_id" : { "name" : "xx007", "title" : "book-44" }, "pop" : 64 }
{ "_id" : { "name" : "xx003", "title" : "book-43" }, "pop" : 30 }
{ "_id" : { "name" : "xx003", "title" : "book-42" }, "pop" : 65 }
{ "_id" : { "name" : "xx007", "title" : "book-41" }, "pop" : 29 }
{ "_id" : { "name" : "xx004", "title" : "book-38" }, "pop" : 51 }
{ "_id" : { "name" : "xx001", "title" : "book-36" }, "pop" : 66 }
{ "_id" : { "name" : "xx000", "title" : "book-47" }, "pop" : 96 }
{ "_id" : { "name" : "xx001", "title" : "book-34" }, "pop" : 80 }
{ "_id" : { "name" : "xx008", "title" : "book-33" }, "pop" : 75 }
{ "_id" : { "name" : "xx005", "title" : "book-32" }, "pop" : 76 }
{ "_id" : { "name" : "xx009", "title" : "book-49" }, "pop" : 35 }
{ "_id" : { "name" : "xx003", "title" : "book-30" }, "pop" : 9 }
{ "_id" : { "name" : "xx003", "title" : "book-28" }, "pop" : 76 }
{ "_id" : { "name" : "xx000", "title" : "book-25" }, "pop" : 37 }
{ "_id" : { "name" : "xx007", "title" : "book-24" }, "pop" : 1 }
{ "_id" : { "name" : "xx002", "title" : "book-45" }, "pop" : 86 }
{ "_id" : { "name" : "xx005", "title" : "book-23" }, "pop" : 4 }
{ "_id" : { "name" : "xx005", "title" : "book-21" }, "pop" : 84 }

$project

投影操作, 将原始字段投影成指定名称, 如将集合中的 title 投影成 name

db.books.aggregate([{$project:{name:"$title"}}])
{ "_id" : ObjectId("6271102510f566b3b1a8dba5"), "name" : "book-0" }
{ "_id" : ObjectId("6271102510f566b3b1a8dba6"), "name" : "book-1" }
{ "_id" : ObjectId("6271102510f566b3b1a8dba7"), "name" : "book-2" }
{ "_id" : ObjectId("6271102510f566b3b1a8dba8"), "name" : "book-3" }
{ "_id" : ObjectId("6271102510f566b3b1a8dba9"), "name" : "book-4" }
{ "_id" : ObjectId("6271102510f566b3b1a8dbaa"), "name" : "book-5" }
{ "_id" : ObjectId("6271102510f566b3b1a8dbab"), "name" : "book-6" }
{ "_id" : ObjectId("6271102510f566b3b1a8dbac"), "name" : "book-7" }
{ "_id" : ObjectId("6271102510f566b3b1a8dbad"), "name" : "book-8" }
{ "_id" : ObjectId("6271102510f566b3b1a8dbae"), "name" : "book-9" }
{ "_id" : ObjectId("6271102510f566b3b1a8dbaf"), "name" : "book-10" }
{ "_id" : ObjectId("6271102510f566b3b1a8dbb0"), "name" : "book-11" }
{ "_id" : ObjectId("6271102510f566b3b1a8dbb1"), "name" : "book-12" }
{ "_id" : ObjectId("6271102510f566b3b1a8dbb2"), "name" : "book-13" }
{ "_id" : ObjectId("6271102510f566b3b1a8dbb3"), "name" : "book-14" }
{ "_id" : ObjectId("6271102510f566b3b1a8dbb4"), "name" : "book-15" }
{ "_id" : ObjectId("6271102510f566b3b1a8dbb5"), "name" : "book-16" }
{ "_id" : ObjectId("6271102510f566b3b1a8dbb6"), "name" : "book-17" }
{ "_id" : ObjectId("6271102510f566b3b1a8dbb7"), "name" : "book-18" }
{ "_id" : ObjectId("6271102510f566b3b1a8dbb8"), "name" : "book-19" }

$project 可以灵活控制输出文档的格式,也可以剔除不需要的字段

db.books.aggregate([{$project:{name:"$title",_id:0,type:1,author:1}}])
{ "type" : "technology", "author" : { "name" : "xx002", "age" : 26 }, "name" : "book-0" }
{ "type" : "travel", "author" : { "name" : "xx003", "age" : 26 }, "name" : "book-1" }
{ "type" : "novel", "author" : { "name" : "xx002", "age" : 30 }, "name" : "book-2" }
{ "type" : "sociality", "author" : { "name" : "xx009", "age" : 31 }, "name" : "book-3" }
{ "type" : "literature", "author" : { "name" : "xx004", "age" : 27 }, "name" : "book-4" }
{ "type" : "novel", "author" : { "name" : "xx008", "age" : 26 }, "name" : "book-5" }
{ "type" : "literature", "author" : { "name" : "xx002", "age" : 29 }, "name" : "book-6" }
{ "type" : "novel", "author" : { "name" : "xx007", "age" : 24 }, "name" : "book-7" }
{ "type" : "sociality", "author" : { "name" : "xx005", "age" : 32 }, "name" : "book-8" }
{ "type" : "novel", "author" : { "name" : "xx005", "age" : 31 }, "name" : "book-9" }
{ "type" : "literature", "author" : { "name" : "xx000", "age" : 27 }, "name" : "book-10" }
{ "type" : "technology", "author" : { "name" : "xx000", "age" : 32 }, "name" : "book-11" }
{ "type" : "novel", "author" : { "name" : "xx003", "age" : 23 }, "name" : "book-12" }
{ "type" : "travel", "author" : { "name" : "xx006", "age" : 29 }, "name" : "book-13" }
{ "type" : "novel", "author" : { "name" : "xx004", "age" : 25 }, "name" : "book-14" }
{ "type" : "literature", "author" : { "name" : "xx005", "age" : 34 }, "name" : "book-15" }
{ "type" : "technology", "author" : { "name" : "xx000", "age" : 32 }, "name" : "book-16" }
{ "type" : "novel", "author" : { "name" : "xx003", "age" : 33 }, "name" : "book-17" }
{ "type" : "novel", "author" : { "name" : "xx001", "age" : 24 }, "name" : "book-18" }
{ "type" : "travel", "author" : { "name" : "xx004", "age" : 27 }, "name" : "book-19" }

从嵌套文档中排除字段

比如想从author中只看name

db.books.aggregate([
{$project:{name:"$title",_id:0,type:1,"author.name":1}}
])
或者
db.books.aggregate([
{$project:{name:"$title",_id:0,type:1,author:{name:1}}}
])

$limit

限制传递到管道中下一阶段的文档数

如仅返回管道传递给它的前5个文档

db.books.aggregate([
{$limit : 5 }
])
{ "_id" : ObjectId("6271102510f566b3b1a8dba5"), "title" : "book-0", "type" : "technology", "tag" : [ "mongodb", "developer" ], "favCount" : 72, "author" : { "name" : "xx002", "age" : 26 } }
{ "_id" : ObjectId("6271102510f566b3b1a8dba6"), "title" : "book-1", "type" : "travel", "tag" : [ "mongodb", "document" ], "favCount" : 20, "author" : { "name" : "xx003", "age" : 26 } }
{ "_id" : ObjectId("6271102510f566b3b1a8dba7"), "title" : "book-2", "type" : "novel", "tag" : [ "nosql", "mongodb" ], "favCount" : 6, "author" : { "name" : "xx002", "age" : 30 } }
{ "_id" : ObjectId("6271102510f566b3b1a8dba8"), "title" : "book-3", "type" : "sociality", "tag" : [ "popular", "nosql" ], "favCount" : 92, "author" : { "name" : "xx009", "age" : 31 } }
{ "_id" : ObjectId("6271102510f566b3b1a8dba9"), "title" : "book-4", "type" : "literature", "tag" : [ "document", "mongodb" ], "favCount" : 10, "author" : { "name" : "xx004", "age" : 27 } }

$skip

跳过进入stage的指定数量的文档,并将其余文档传递到管道中的下一个阶段

如跳过管道传递给它的前5个文档

db.books.aggregate([
{$skip : 5 }
])
{ "_id" : ObjectId("6271102510f566b3b1a8dbaa"), "title" : "book-5", "type" : "novel", "tag" : [ "developer", "nosql" ], "favCount" : 67, "author" : { "name" : "xx008", "age" : 26 } }
{ "_id" : ObjectId("6271102510f566b3b1a8dbab"), "title" : "book-6", "type" : "literature", "tag" : [ "mongodb", "developer" ], "favCount" : 55, "author" : { "name" : "xx002", "age" : 29 } }
{ "_id" : ObjectId("6271102510f566b3b1a8dbac"), "title" : "book-7", "type" : "novel", "tag" : [ "mongodb", "developer" ], "favCount" : 24, "author" : { "name" : "xx007", "age" : 24 } }
{ "_id" : ObjectId("6271102510f566b3b1a8dbad"), "title" : "book-8", "type" : "sociality", "tag" : [ "popular", "nosql" ], "favCount" : 70, "author" : { "name" : "xx005", "age" : 32 } }
{ "_id" : ObjectId("6271102510f566b3b1a8dbae"), "title" : "book-9", "type" : "novel", "tag" : [ "nosql", "nosql" ], "favCount" : 32, "author" : { "name" : "xx005", "age" : 31 } }
{ "_id" : ObjectId("6271102510f566b3b1a8dbaf"), "title" : "book-10", "type" : "literature", "tag" : [ "developer", "nosql" ], "favCount" : 99, "author" : { "name" : "xx000", "age" : 27 } }
{ "_id" : ObjectId("6271102510f566b3b1a8dbb0"), "title" : "book-11", "type" : "technology", "tag" : [ "popular", "nosql" ], "favCount" : 61, "author" : { "name" : "xx000", "age" : 32 } }
{ "_id" : ObjectId("6271102510f566b3b1a8dbb1"), "title" : "book-12", "type" : "novel", "tag" : [ "popular", "nosql" ], "favCount" : 51, "author" : { "name" : "xx003", "age" : 23 } }
{ "_id" : ObjectId("6271102510f566b3b1a8dbb2"), "title" : "book-13", "type" : "travel", "tag" : [ "developer", "popular" ], "favCount" : 3, "author" : { "name" : "xx006", "age" : 29 } }

$sort

对所有输入文档进行排序,并按排序顺序将它们返回到管道

{ $sort: { <field1>: <sort order>, <field2>: <sort order> ... } }

要对字段进行排序,请将排序顺序设置为1或-1,以分别指定升序或降序排序,如下例所示

db.books.aggregate([
{$sort : {favCount:-1,title:1}}
])
{ "_id" : ObjectId("6271102510f566b3b1a8dbaf"), "title" : "book-10", "type" : "literature", "tag" : [ "developer", "nosql" ], "favCount" : 99, "author" : { "name" : "xx000", "age" : 27 } }
{ "_id" : ObjectId("6271102510f566b3b1a8dbd4"), "title" : "book-47", "type" : "literature", "tag" : [ "popular", "developer" ], "favCount" : 96, "author" : { "name" : "xx000", "age" : 24 } }
{ "_id" : ObjectId("6271102510f566b3b1a8dba8"), "title" : "book-3", "type" : "sociality", "tag" : [ "popular", "nosql" ], "favCount" : 92, "author" : { "name" : "xx009", "age" : 31 } }
{ "_id" : ObjectId("6271102510f566b3b1a8dbb4"), "title" : "book-15", "type" : "literature", "tag" : [ "nosql", "mongodb" ], "favCount" : 87, "author" : { "name" : "xx005", "age" : 34 } }
{ "_id" : ObjectId("6271102510f566b3b1a8dbc2"), "title" : "book-29", "type" : "sociality", "tag" : [ "nosql", "developer" ], "favCount" : 87, "author" : { "name" : "xx006", "age" : 21 } }
{ "_id" : ObjectId("6271102510f566b3b1a8dbd2"), "title" : "book-45", "type" : "sociality", "tag" : [ "mongodb", "popular" ], "favCount" : 86, "author" : { "name" : "xx002", "age" : 23 } }
{ "_id" : ObjectId("6271102510f566b3b1a8dbba"), "title" : "book-21", "type" : "sociality", "tag" : [ "popular", "developer" ], "favCount" : 84, "author" : { "name" : "xx005", "age" : 27 } }
{ "_id" : ObjectId("6271102510f566b3b1a8dbca"), "title" : "book-37", "type" : "literature", "tag" : [ "developer", "popular" ], "favCount" : 83, "author" : { "name" : "xx000", "age" : 23 } }
{ "_id" : ObjectId("6271102510f566b3b1a8dbc7"), "title" : "book-34", "type" : "technology", "tag" : [ "developer", "document" ], "favCount" : 80, "author" : { "name" : "xx001", "age" : 20 } }
{ "_id" : ObjectId("6271102510f566b3b1a8dbc1"), "title" : "book-28", "type" : "novel", "tag" : [ "developer", "document" ], "favCount" : 76, "author" : { "name" : "xx003", "age" : 25 } }
{ "_id" : ObjectId("6271102510f566b3b1a8dbc5"), "title" : "book-32", "type" : "sociality", "tag" : [ "mongodb", "document" ], "favCount" : 76, "author" : { "name" : "xx005", "age" : 30 } }
{ "_id" : ObjectId("6271102510f566b3b1a8dbc6"), "title" : "book-33", "type" : "novel", "tag" : [ "document", "developer" ], "favCount" : 75, "author" : { "name" : "xx008", "age" : 29 } }
{ "_id" : ObjectId("6271102510f566b3b1a8dba5"), "title" : "book-0", "type" : "technology", "tag" : [ "mongodb", "developer" ], "favCount" : 72, "author" : { "name" : "xx002", "age" : 26 } }
{ "_id" : ObjectId("6271102510f566b3b1a8dbad"), "title" : "book-8", "type" : "sociality", "tag" : [ "popular", "nosql" ], "favCount" : 70, "author" : { "name" : "xx005", "age" : 32 } }
{ "_id" : ObjectId("6271102510f566b3b1a8dbaa"), "title" : "book-5", "type" : "novel", "tag" : [ "developer", "nosql" ], "favCount" : 67, "author" : { "name" : "xx008", "age" : 26 } }
{ "_id" : ObjectId("6271102510f566b3b1a8dbc9"), "title" : "book-36", "type" : "literature", "tag" : [ "nosql", "popular" ], "favCount" : 66, "author" : { "name" : "xx001", "age" : 21 } }
{ "_id" : ObjectId("6271102510f566b3b1a8dbcf"), "title" : "book-42", "type" : "literature", "tag" : [ "document", "nosql" ], "favCount" : 65, "author" : { "name" : "xx003", "age" : 29 } }
{ "_id" : ObjectId("6271102510f566b3b1a8dbd1"), "title" : "book-44", "type" : "technology", "tag" : [ "document", "mongodb" ], "favCount" : 64, "author" : { "name" : "xx007", "age" : 31 } }
{ "_id" : ObjectId("6271102510f566b3b1a8dbcd"), "title" : "book-40", "type" : "technology", "tag" : [ "developer", "document" ], "favCount" : 63, "author" : { "name" : "xx000", "age" : 25 } }
{ "_id" : ObjectId("6271102510f566b3b1a8dbb0"), "title" : "book-11", "type" : "technology", "tag" : [ "popular", "nosql" ], "favCount" : 61, "author" : { "name" : "xx000", "age" : 32 } }
0

评论 (0)

取消