插入数据
> db.dev.insertMany([
{"title":"北京尚学堂","description":"程序员的摇篮","url":"www.bjsxt.com","tags":["Java","大数据","Python"]},
{"title":"百战程序员","description":"身经百战,高薪相伴","url":"www.itbaizhan.cn","tags":["JavaWeb实战","数据库实战","微服务实战"]},
{"title":"尚学堂大数据","description":"培养大数据人才的摇篮","url":"www.bjsxt.com","tags":["Hadoop","Spark","Hbase"]},
{"title":"Java","description":"程序员的摇篮","url":"www.bjsxt.com","tags":["Java","大数据","Python"]},
{"title":"Java","tags":["JavaSE","JavaEE","JavaME"]},
{"title":"ORM","tags":["MyBatis","Hibernate"]},
{"title":"Spring","tags":["SpringMVC","Spring Data","Spring Cloud"]},
{"title":"Web","tags":["Jsp","Servlet"]},
{"title":"RPC","tags":["RMI","Dubbo"]},
{"title":"DataBase","tags":["Oracle","MySQL"]}
])
查询dev集合中的所有记录
> db.dev.find()
{ "_id" : ObjectId("5fc4e460c07669d59be84edc"), "title" : "北京尚学堂", "description" : "程序员的摇篮", "url" : "www.bjsxt.com", "tags" : [ "Java", "大数据", "Python" ] }
{ "_id" : ObjectId("5fc4e460c07669d59be84edd"), "title" : "百战程序员", "description" : "身经百战,高薪相伴", "url" : "www.itbaizhan.cn", "tags" : [ "JavaWeb实战", "数据库实战", "微服务实战" ] }
{ "_id" : ObjectId("5fc4e460c07669d59be84ede"), "title" : "尚学堂大数据", "description" : "培养大数据人才的摇篮", "url" : "www.bjsxt.com", "tags" : [ "Hadoop", "Spark", "Hbase" ] }
{ "_id" : ObjectId("5fc4e460c07669d59be84edf"), "title" : "Java", "description" : "程序员的摇篮", "url" : "www.bjsxt.com", "tags" : [ "Java", "大数据", "Python" ] }
{ "_id" : ObjectId("5fc4e460c07669d59be84ee0"), "title" : "Java", "tags" : [ "JavaSE", "JavaEE", "JavaME" ] }
{ "_id" : ObjectId("5fc4e460c07669d59be84ee1"), "title" : "ORM", "tags" : [ "MyBatis", "Hibernate" ] }
{ "_id" : ObjectId("5fc4e460c07669d59be84ee2"), "title" : "Spring", "tags" : [ "SpringMVC", "Spring Data", "Spring Cloud" ] }
{ "_id" : ObjectId("5fc4e664c07669d59be84ee3"), "title" : "Web", "tags" : [ "Jsp", "Servlet" ] }
{ "_id" : ObjectId("5fc4e664c07669d59be84ee4"), "title" : "RPC", "tags" : [ "RMI", "Dubbo" ] }
{ "_id" : ObjectId("5fc4e664c07669d59be84ee5"), "title" : "DataBase", "tags" : [ "Oracle", "MySQL" ] }
> db.dev.find().pretty() #pretty()函数以格式化的方式来显示文档
{
"_id" : ObjectId("5fc4e460c07669d59be84edc"),
"title" : "北京尚学堂",
"description" : "程序员的摇篮",
"url" : "www.bjsxt.com",
"tags" : [
"Java",
"大数据",
"Python"
]
}
{
"_id" : ObjectId("5fc4e460c07669d59be84edd"),
"title" : "百战程序员",
"description" : "身经百战,高薪相伴",
"url" : "www.itbaizhan.cn",
"tags" : [
"JavaWeb实战",
"数据库实战",
"微服务实战"
]
}
{
"_id" : ObjectId("5fc4e460c07669d59be84ede"),
"title" : "尚学堂大数据",
"description" : "培养大数据人才的摇篮",
"url" : "www.bjsxt.com",
"tags" : [
"Hadoop",
"Spark",
"Hbase"
]
}
{
"_id" : ObjectId("5fc4e460c07669d59be84edf"),
"title" : "Java",
"description" : "程序员的摇篮",
"url" : "www.bjsxt.com",
"tags" : [
"Java",
"大数据",
"Python"
]
}
{
"_id" : ObjectId("5fc4e460c07669d59be84ee0"),
"title" : "Java",
"tags" : [
"JavaSE",
"JavaEE",
"JavaME"
]
}
{
"_id" : ObjectId("5fc4e460c07669d59be84ee1"),
"title" : "ORM",
"tags" : [
"MyBatis",
"Hibernate"
]
}
{
"_id" : ObjectId("5fc4e460c07669d59be84ee2"),
"title" : "Spring",
"tags" : [
"SpringMVC",
"Spring Data",
"Spring Cloud"
]
}
{
"_id" : ObjectId("5fc4e664c07669d59be84ee3"),
"title" : "Web",
"tags" : [
"Jsp",
"Servlet"
]
}
{
"_id" : ObjectId("5fc4e664c07669d59be84ee4"),
"title" : "RPC",
"tags" : [
"RMI",
"Dubbo"
]
}
{
"_id" : ObjectId("5fc4e664c07669d59be84ee5"),
"title" : "DataBase",
"tags" : [
"Oracle",
"MySQL"
]
}
查询title为DataBase的文档并格式化显示
> db.dev.find({title:"DataBase"}).pretty()
{
"_id" : ObjectId("5fc4e664c07669d59be84ee5"),
"title" : "DataBase",
"tags" : [
"Oracle",
"MySQL"
]
}
findOne()函数只返回满足条件的第一条数据。如果未作投影操作,该方法则自带格式化功能
> db.dev.findOne()
{
"_id" : ObjectId("5fc4e460c07669d59be84edc"),
"title" : "北京尚学堂",
"description" : "程序员的摇篮",
"url" : "www.bjsxt.com",
"tags" : [
"Java",
"大数据",
"Python"
]
}
> db.dev.insertMany([
... {title:"dev",desc:"test1"},
... {title:"dev",desc:"test2"},
... {title:"dev",desc:"test3"}
... ])
> db.dev.findOne({title:"dev"})
{
"_id" : ObjectId("5fc4e9edc07669d59be84ee6"),
"title" : "dev",
"desc" : "test1"
}
1. 模糊查询
查询文档中title的值含有a的所有记录
> db.dev.find({title:/a/})
{ "_id" : ObjectId("5fc4e460c07669d59be84edf"), "title" : "Java", "description" : "程序员的摇篮", "url" : "www.bjsxt.com", "tags" : [ "Java", "大数据", "Python" ] }
{ "_id" : ObjectId("5fc4e460c07669d59be84ee0"), "title" : "Java", "tags" : [ "JavaSE", "JavaEE", "JavaME" ] }
{ "_id" : ObjectId("5fc4e664c07669d59be84ee5"), "title" : "DataBase", "tags" : [ "Oracle", "MySQL" ] }
使用^表示查询内容的起始位置
查询文档中title的值以S开头的所有内容
> db.dev.find({title:/^S/})
{ "_id" : ObjectId("5fc4e460c07669d59be84ee2"), "title" : "Spring", "tags" : [ "SpringMVC", "Spring Data", "Spring Cloud" ] }
使用$表示结尾
查询文档title的值以g结尾的所有内容
> db.dev.find({title:/g$/})
{ "_id" : ObjectId("5fc4e460c07669d59be84ee2"), "title" : "Spring", "tags" : [ "SpringMVC", "Spring Data", "Spring Cloud" ] }
2. 投影操作
查询dev集合中所有记录的title
> db.dev.find({},{title:1})
{ "_id" : ObjectId("5fc4e460c07669d59be84edc"), "title" : "北京尚学堂" }
{ "_id" : ObjectId("5fc4e460c07669d59be84edd"), "title" : "百战程序员" }
{ "_id" : ObjectId("5fc4e460c07669d59be84ede"), "title" : "尚学堂大数据" }
{ "_id" : ObjectId("5fc4e460c07669d59be84edf"), "title" : "Java" }
{ "_id" : ObjectId("5fc4e460c07669d59be84ee0"), "title" : "Java" }
{ "_id" : ObjectId("5fc4e460c07669d59be84ee1"), "title" : "ORM" }
{ "_id" : ObjectId("5fc4e460c07669d59be84ee2"), "title" : "Spring" }
{ "_id" : ObjectId("5fc4e664c07669d59be84ee3"), "title" : "Web" }
{ "_id" : ObjectId("5fc4e664c07669d59be84ee4"), "title" : "RPC" }
{ "_id" : ObjectId("5fc4e664c07669d59be84ee5"), "title" : "DataBase" }
{ "_id" : ObjectId("5fc4e9edc07669d59be84ee6"), "title" : "dev" }
{ "_id" : ObjectId("5fc4e9edc07669d59be84ee7"), "title" : "dev" }
{ "_id" : ObjectId("5fc4e9edc07669d59be84ee8"), "title" : "dev" }
查询dev集合中所有记录的title,不显示_id
> db.dev.find({},{title:1,_id:0})
{ "title" : "北京尚学堂" }
{ "title" : "百战程序员" }
{ "title" : "尚学堂大数据" }
{ "title" : "Java" }
{ "title" : "Java" }
{ "title" : "ORM" }
{ "title" : "Spring" }
{ "title" : "Web" }
{ "title" : "RPC" }
{ "title" : "DataBase" }
{ "title" : "dev" }
{ "title" : "dev" }
{ "title" : "dev" }
查询title的值为dev的第一条记录,这条记录只显示desc字段,不显示_id
可以看到,findOne()做投影以后,就不再格式化了
> db.dev.findOne({title:"dev"},{desc:1,_id:0})
{ "desc" : "test1" }
3. 条件操作符
语法格式:
find({键:{操作符:条件}})
或者
findOne({键:{操作符:条件}})
大于操作符$gt,该操作符可以对数字或日期进行判断
> db.dev.insertMany([
... {title:"test1",size:100},
... {title:"test2",size:200},
... {title:"test3",size:300},
... {title:"test4",size:400},
... {title:"test5",size:500}
... ])
查询size大于300的文章的所有记录
> db.dev.find({size:{$gt:300}})
{ "_id" : ObjectId("5fc4f11ec07669d59be84eec"), "title" : "test4", "size" : 400 }
{ "_id" : ObjectId("5fc4f11ec07669d59be84eed"), "title" : "test5", "size" : 500 }
小于操作符$lt
查询size小于300的文章的所有记录
> db.dev.find({size:{$lt:300}})
{ "_id" : ObjectId("5fc4f11ec07669d59be84ee9"), "title" : "test1", "size" : 100 }
{ "_id" : ObjectId("5fc4f11ec07669d59be84eea"), "title" : "test2", "size" : 200 }
等于操作符$eq
不等于操作符$ne
查询size等于300的文档记录
> db.dev.find({size:{$eq:300}})
{ "_id" : ObjectId("5fc4f11ec07669d59be84eeb"), "title" : "test3", "size" : 300 }
$and操作符表示多条件间的并且关系
语法格式:find({$and:[{条件1},{条件2},…]})
# 再插入一条数据
> db.dev.insert({title:"test6",size:500})
如果在查询中给定了多个查询条件,条件之间的关系默认是and关系
查询size大于100并且小于300的记录
> db.dev.find({size:{$gt:100,$lt:300}})
{ "_id" : ObjectId("5fc4f11ec07669d59be84eea"), "title" : "test2", "size" : 200 }
查询size大于100并且小于300的记录,使用$and指定多条件关系
> db.dev.find({$and:[{size:{$gt:100}},{size:{$lt:300}}]})
{ "_id" : ObjectId("5fc4f11ec07669d59be84eea"), "title" : "test2", "size" : 200 }
$or表示多条件间的或者关系
语法格式:find({$or:[{条件1},{条件2},…]})
查询title的值为test2或者size的值大于300的文档
> db.dev.find({$or:[{title:{$eq:"test2"}},{size:{$gt:300}}]})
{ "_id" : ObjectId("5fc4f11ec07669d59be84eea"), "title" : "test2", "size" : 200 }
{ "_id" : ObjectId("5fc4f11ec07669d59be84eec"), "title" : "test4", "size" : 400 }
{ "_id" : ObjectId("5fc4f11ec07669d59be84eed"), "title" : "test5", "size" : 500 }
{ "_id" : ObjectId("5fc4f40fc07669d59be84eee"), "title" : "test6", "size" : 500 }
条件操作 $and 与 $or 联合使用
查询title为test5并且size等于500,或者size小于400的文档
> db.dev.find({$or:[{$and:[{title:{$eq:"test5"}},{size:500}]},{size:{$lt:400}}]})
{ "_id" : ObjectId("5fc4f11ec07669d59be84ee9"), "title" : "test1", "size" : 100 }
{ "_id" : ObjectId("5fc4f11ec07669d59be84eea"), "title" : "test2", "size" : 200 }
{ "_id" : ObjectId("5fc4f11ec07669d59be84eeb"), "title" : "test3", "size" : 300 }
{ "_id" : ObjectId("5fc4f11ec07669d59be84eed"), "title" : "test5", "size" : 500 }
$type 操作符是基于BSON类型来检索集合中匹配的数据类型,并返回结果
# 插入一条记录
> db.dev.insert({title:200,size:400})
> typeof 123
number
> typeof "xiaoliu"
string
查询title的值的类型为number的记录
> db.dev.find({title:{$type:"number"}})
{ "_id" : ObjectId("5fc77a105a5617876fcd23c8"), "title" : 200, "size" : 400 }
limit()函数读取指定数量的数据记录
查询前两条记录的title
> db.dev.find({},{title:1}).limit(2)
{ "_id" : ObjectId("5fc4e460c07669d59be84edc"), "title" : "北京尚学堂" }
{ "_id" : ObjectId("5fc4e460c07669d59be84edd"), "title" : "百战程序员" }
跳过第1条,取5条
> db.dev.find({},{title:1}).skip(1).limit(5)
{ "_id" : ObjectId("5fc4e460c07669d59be84edd"), "title" : "百战程序员" }
{ "_id" : ObjectId("5fc4e460c07669d59be84ede"), "title" : "尚学堂大数据" }
{ "_id" : ObjectId("5fc4e460c07669d59be84edf"), "title" : "Java" }
{ "_id" : ObjectId("5fc4e460c07669d59be84ee0"), "title" : "Java" }
{ "_id" : ObjectId("5fc4e460c07669d59be84ee1"), "title" : "ORM" }
4. MongoDB排序
sort()函数对查询到的文档进行排序,可以通过参数指定排序的字段。并使用1和-1来指定排序的方式
语法格式:db.集合名.find().sort({排序键:1})
查询size的值为number类型的文档,显示title,size的内容,并对size做升序排序
> db.dev.find({size:{$type:"number"}},{title:1,size:1,_id:0}).sort({size:1})
{ "title" : "test1", "size" : 100 }
{ "title" : "test2", "size" : 200 }
{ "title" : "test3", "size" : 300 }
{ "title" : "test4", "size" : 400 }
{ "title" : 200, "size" : 400 }
{ "title" : "test5", "size" : 500 }
{ "title" : "test6", "size" : 500 }
查询size的值为number类型的文档,显示title,size的内容,并对size做降序排序
> db.dev.find({size:{$type:"number"}},{title:1,size:1,_id:0}).sort({size:-1})
{ "title" : "test5", "size" : 500 }
{ "title" : "test6", "size" : 500 }
{ "title" : "test4", "size" : 400 }
{ "title" : 200, "size" : 400 }
{ "title" : "test3", "size" : 300 }
{ "title" : "test2", "size" : 200 }
{ "title" : "test1", "size" : 100 }
5. 索引
在MongoDB中会自动为文档中的_id(文档的主键)键创建索引,与关系型数据的主键索引类似
可以使用createIndex()函数来为其他的键创建索引。在创建索引时需要指定排序规则
语法格式:db.集合名.createIndex({创建索引的键:排序规则,…},{创建索引的参数(可选参数)})
为dev集合中的title键创建索引,并让创建工作在后台运行
> db.dev.createIndex({title:1},{background:true})
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 1,
"numIndexesAfter" : 2,
"ok" : 1
}
查看索引
可以通过getIndexes()或者getIndexSpecs()函数查看集合中的所有索引信息
语法格式:db.集合名.getIndexes()
语法格式:db.集合名.getIndexSpecs()
使用getIndexes()函数查看当前dev集合中的索引
> db.dev.getIndexes()
[
{
"v" : 2,
"key" : {
"_id" : 1
},
"name" : "_id_",
"ns" : "bjsxt.dev"
},
{
"v" : 2,
"key" : {
"title" : 1
},
"name" : "title_1",
"ns" : "bjsxt.dev",
"background" : true
}
]
> db.dev.getIndexSpecs()
[
{
"v" : 2,
"key" : {
"_id" : 1
},
"name" : "_id_",
"ns" : "bjsxt.dev"
},
{
"v" : 2,
"key" : {
"title" : 1
},
"name" : "title_1",
"ns" : "bjsxt.dev",
"background" : true
}
]
我们只为title创建了索引,为什么返回了两条信息呢?
因为第一条是_id,MongoDB会自动为_id键创建索引
可以通过使用getIndexKeys()函数查看集合的索引键
语法格式:db.集合名.getIndexKeys()
查看dev集合中的索引键
> db.dev.getIndexKeys()
[ { "_id" : 1 }, { "title" : 1 } ]
可以通过totalIndexSize()函数来查看当前集合中索引的大小,单位为字节
语法格式:db.集合名.totalIndexSize( [detail] (可选参数))
参数解释:detail可选参数,如果传入除0或false外的任意数据,那么会显示该集合中每个索引的大小及集合中索引的总大小。如果传入0或false则只显示该集合中所有索引的总大小。默认值false。
查看dev集合中所有索引的总大小
> db.dev.totalIndexSize()
53248
> db.dev.totalIndexSize([1])
_id_ 36864
title_1 16384
53248
删除索引
可以通过dropIndex()函数来删除指定索引
语法格式db.集合名.dropIndex(“索引名称”)
删除title键的索引
> db.dev.dropIndex("title_1")
{ "nIndexesWas" : 2, "ok" : 1 }
> db.dev.getIndexes()
[
{
"v" : 2,
"key" : {
"_id" : 1
},
"name" : "_id_",
"ns" : "bjsxt.dev"
}
]
可以使用dropIndexes()函数删除集合中的全部索引,_id键的索引除外
语法格式:db.集合名.dropIndexes()
> db.dev.dropIndexes()
{
"nIndexesWas" : 1,
"msg" : "non-_id indexes dropped for collection",
"ok" : 1
}
> db.dev.getIndexes()
[
{
"v" : 2,
"key" : {
"_id" : 1
},
"name" : "_id_",
"ns" : "bjsxt.dev"
}
]
5.1 单字段索引
单字段索引是指在索引中只包含了一个键。查询时,可加速对该字段的各种查询请求,是最常见的索引形式。MongoDB默认创建的_id索引也是这种类型。我们可以使用createIndexes({索引键:排序规则})函数来创建单字段索引。
语法格式:db.集合名.createIndexes({索引键名:排序规则})
为dev集合中的title键创建单字段索引
> db.dev.createIndex({title:1},{background:true})
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 1,
"numIndexesAfter" : 2,
"ok" : 1
}
5.2 交叉索引
交叉索引就是为一个集合的多个字段分别建立索引,在查询的时候通过多个字段作为查询条件,这种情况称为交叉索引
在查询文档时,在查询条件中包含一个交叉索引键或者在一次查询中使用多个交叉索引键作为查询条件都会触发交叉索引
为dev集合中的size键创建交叉索引
> db.dev.createIndex({size:1})
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 2,
"numIndexesAfter" : 3,
"ok" : 1
}
> db.dev.getIndexes()
[
{
"v" : 2,
"key" : {
"_id" : 1
},
"name" : "_id_",
"ns" : "bjsxt.dev"
},
{
"v" : 2,
"key" : {
"title" : 1
},
"name" : "title_1",
"ns" : "bjsxt.dev",
"background" : true
},
{
"v" : 2,
"key" : {
"size" : 1
},
"name" : "size_1",
"ns" : "bjsxt.dev"
}
]
title索引和size索引就是一个典型的交叉索引
5.3 复合索引
复合索引针对多个字段联合创建索引,先按第一个字段排序,第一个字段相同的文档按第二个字段排序,以此类推
语法格式:db.集合名.createIndex({索引键名:排序规则,索引键名:排序规则,…})
删除dev中的交叉索引
> db.dev.dropIndexes()
{
"nIndexesWas" : 3,
"msg" : "non-_id indexes dropped for collection",
"ok" : 1
}
创建title与size的复合索引
> db.dev.createIndex({title:1,size:1})
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 1,
"numIndexesAfter" : 2,
"ok" : 1
}
5.4 多key索引
当索引的字段为数组时,创建处的索引为多key索引,多key索引会为数组的每个元素建立一条索引。
语法格式:db.集合名.createIndex({数组键名:排序规则})
为dev集合中tags键创建多key索引
> db.dev.createIndex({tags:1})
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 2,
"numIndexesAfter" : 3,
"ok" : 1
}
> db.dev.getIndexes()
[
{
"v" : 2,
"key" : {
"_id" : 1
},
"name" : "_id_",
"ns" : "bjsxt.dev"
},
{
"v" : 2,
"key" : {
"title" : 1,
"size" : 1
},
"name" : "title_1_size_1",
"ns" : "bjsxt.dev"
},
{
"v" : 2,
"key" : {
"tags" : 1
},
"name" : "tags_1",
"ns" : "bjsxt.dev"
}
]
5.5 唯一索引
唯一索引会保证索引对应的键不会出现相同的值,_id索引就是唯一索引
语法格式:db.集合名.createIndex({索引键名:排序规则},{unique:true})
如果唯一索引所在字段有重复数据写入时,抛出异常
删除dev集合中的索引,为dev集合中的title键建立唯一索引
#删除索引
> db.dev.dropIndexes()
{
"nIndexesWas" : 3,
"msg" : "non-_id indexes dropped for collection",
"ok" : 1
}
#删除title字段的重复值的记录
> db.dev.deleteMany({$or:[{title:"dev"},{title:"Java"}]})
# 为dev集合中的title键建立唯一索引
> db.dev.createIndex({title:1},{background:true,unique:true})
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 1,
"numIndexesAfter" : 2,
"ok" : 1
}
#因为title字段中已经有test1了,再插入test1会抛出异常
> db.dev.insert({title:"test1"})
WriteResult({
"nInserted" : 0,
"writeError" : {
"code" : 11000,
"errmsg" : "E11000 duplicate key error collection: bjsxt.dev index: title_1 dup key: { : \"test1\" }"
}
})
5.6 部分索引
部分索引是只针对符合某个特定条件的文档建立索引
语法格式:db.集合名.createIndex({索引键名:排序规则},{partialFilterExpression:{键名:{匹配条件:条件值}}})
为dev集合中的size键创建部分索引,条件为大于300
> db.dev.createIndex({size:1},{partialFilterExpression:{size:{$gt:300}}})
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 2,
"numIndexesAfter" : 3,
"ok" : 1
}
> db.dev.getIndexes()
[
{
"v" : 2,
"key" : {
"_id" : 1
},
"name" : "_id_",
"ns" : "bjsxt.dev"
},
{
"v" : 2,
"unique" : true,
"key" : {
"title" : 1
},
"name" : "title_1",
"ns" : "bjsxt.dev",
"background" : true
},
{
"v" : 2,
"key" : {
"size" : 1
},
"name" : "size_1",
"ns" : "bjsxt.dev",
"partialFilterExpression" : {
"size" : {
"$gt" : 300
}
}
}
]
创建一个索引,这个索引既指定了部分索引,同时也给定了唯一约束索引。
对于一个这样的索引而言,唯一索引只对什么样的索引生效呢?
只对满足了查询条件的内容生效。也就是说,在满足了查询条件的数据当中是不允许有重复的,但是对于那些不满足这些条件的数据,唯一性索引是不生效的。也就是说,小于300的数据是允许有重复的,大于300的数据是不允许有重复的
6. 聚合查询
在MongoDB中可以通过aggregate()函数来完成一些聚合查询,aggregate()函数主要用于处理诸如统计,平均值,求和等,并返回计算后的数据结果。
语法格式:db.集合名.aggregate([{$group:{_id:"$分组键名","$分组键名",…,别名:{聚合运算:"$运算列"}}},{条件筛选:{键名:{运算条件:运算类}}}])
常见的mongo的聚合操作与mysql的查询做类比
SQL操作 | mongodb聚合操作 |
where | $match |
group by | $group |
having | $match |
select | $project |
order by | $sort |
limit | $limit |
sum() | $sum |
count() | $sum |
join | $lookup |
6.1 $sum
查询dev集合中一共有多少个文档
> db.dev.aggregate([{$group:{_id:null,count:{$sum:1}}}])
{ "_id" : null, "count" : 15 }
sql:select count(*) as count from dev
mongodb: db.dev.aggregate([{$group:{_id:null,count:{$sum:1}}}])
$group:分组,代表聚合的分组条件
_id:分组的字段。相当于SQL分组语法group by column_name中的column_name部分。如果根据某字段的值分组,则定义为_id:"$字段名"
查询dev集合中的所有size键中的值的总和
> db.dev.aggregate([{$group:{_id:null,totalSize:{$sum:"$size"}}}])
{ "_id" : null, "totalSize" : 2400 }
对每一个title进行分组并计算每组中的size的总和
> db.dev.aggregate([{$group:{_id:"$title",countNum:{$sum:"$size"}}}])
{ "_id" : "test6", "countNum" : 500 }
{ "_id" : "test5", "countNum" : 500 }
{ "_id" : 200, "countNum" : 400 }
{ "_id" : "test4", "countNum" : 400 }
{ "_id" : "test3", "countNum" : 300 }
{ "_id" : "ORM", "countNum" : 0 }
{ "_id" : "尚学堂大数据", "countNum" : 0 }
{ "_id" : "Spring", "countNum" : 0 }
{ "_id" : "百战程序员", "countNum" : 0 }
{ "_id" : "北京尚学堂", "countNum" : 0 }
{ "_id" : "Web", "countNum" : 0 }
{ "_id" : "RPC", "countNum" : 0 }
{ "_id" : "test1", "countNum" : 100 }
{ "_id" : "DataBase", "countNum" : 0 }
{ "_id" : "test2", "countNum" : 200 }
6.2 条件筛选 - $match
查询dev集合有多少文档的size大于200
> db.dev.aggregate([{$match:{size:{$gt:200}}},{$group:{_id:null,totalSize:{$sum:1}}}])
{ "_id" : null, "totalSize" : 5 }
查询dev集合,根据title分组计算出每组的size的总和,并过滤掉总和小于等于200的文档
> db.dev.aggregate([{$group:{_id:"$title",totalSize:{$sum:"$size"}}},{$match:{totalSize:{$gt:200}}}])
{ "_id" : "test6", "totalSize" : 500 }
{ "_id" : "test5", "totalSize" : 500 }
{ "_id" : 200, "totalSize" : 400 }
{ "_id" : "test4", "totalSize" : 400 }
{ "_id" : "test3", "totalSize" : 300 }
6.3 $max
查询dev集合中的最大size的值
> db.dev.aggregate([{$group:{_id:null,maxSize:{$max:"$size"}}}])
{ "_id" : null, "maxSize" : 500 }
6.4 $min
查询dev集合中的最小size的值
> db.dev.aggregate([{$group:{_id:null,minSize:{$min:"$size"}}}])
{ "_id" : null, "minSize" : 100 }
6.5 $avg
查询dev集合中size的平均值
> db.dev.aggregate([{$group:{_id:null,avgSize:{$avg:"$size"}}}])
{ "_id" : null, "avgSize" : 342.85714285714283 }
6.6 统计结果返回数组 $push
查询dev集合,按照size分组并返回他们的title,如果size相同则使用数组返回他们的title
> db.dev.aggregate([{$group:{_id:"$size",titleArray:{$push:"$title"}}}])
{ "_id" : 500, "titleArray" : [ "test5", "test6" ] }
{ "_id" : 400, "titleArray" : [ "test4", 200 ] }
{ "_id" : null, "titleArray" : [ "北京尚学堂", "百战程序员", "尚学堂大数据", "ORM", "Spring", "Web", "RPC", "DataBase" ] }
{ "_id" : 100, "titleArray" : [ "test1" ] }
{ "_id" : 200, "titleArray" : [ "test2" ] }
{ "_id" : 300, "titleArray" : [ "test3" ] }
6.7 数组字段拆分 $unwind
查询dev集合,将数组中的内容拆分显示
> db.dev.aggregate([{$unwind:"$tags"}])
{ "_id" : ObjectId("5fc4e460c07669d59be84edc"), "title" : "北京尚学堂", "description" : "程序员的摇篮", "url" : "www.bjsxt.com", "tags" : "Java" }
{ "_id" : ObjectId("5fc4e460c07669d59be84edc"), "title" : "北京尚学堂", "description" : "程序员的摇篮", "url" : "www.bjsxt.com", "tags" : "大数据" }
{ "_id" : ObjectId("5fc4e460c07669d59be84edc"), "title" : "北京尚学堂", "description" : "程序员的摇篮", "url" : "www.bjsxt.com", "tags" : "Python" }
{ "_id" : ObjectId("5fc4e460c07669d59be84edd"), "title" : "百战程序员", "description" : "身经百战,高薪相伴", "url" : "www.itbaizhan.cn", "tags" : "JavaWeb实战" }
{ "_id" : ObjectId("5fc4e460c07669d59be84edd"), "title" : "百战程序员", "description" : "身经百战,高薪相伴", "url" : "www.itbaizhan.cn", "tags" : "数据库实战" }
{ "_id" : ObjectId("5fc4e460c07669d59be84edd"), "title" : "百战程序员", "description" : "身经百战,高薪相伴", "url" : "www.itbaizhan.cn", "tags" : "微服务实战" }
{ "_id" : ObjectId("5fc4e460c07669d59be84ede"), "title" : "尚学堂大数据", "description" : "培养大数据人才的摇篮", "url" : "www.bjsxt.com", "tags" : "Hadoop" }
{ "_id" : ObjectId("5fc4e460c07669d59be84ede"), "title" : "尚学堂大数据", "description" : "培养大数据人才的摇篮", "url" : "www.bjsxt.com", "tags" : "Spark" }
{ "_id" : ObjectId("5fc4e460c07669d59be84ede"), "title" : "尚学堂大数据", "description" : "培养大数据人才的摇篮", "url" : "www.bjsxt.com", "tags" : "Hbase" }
{ "_id" : ObjectId("5fc4e460c07669d59be84ee1"), "title" : "ORM", "tags" : "MyBatis" }
{ "_id" : ObjectId("5fc4e460c07669d59be84ee1"), "title" : "ORM", "tags" : "Hibernate" }
{ "_id" : ObjectId("5fc4e460c07669d59be84ee2"), "title" : "Spring", "tags" : "SpringMVC" }
{ "_id" : ObjectId("5fc4e460c07669d59be84ee2"), "title" : "Spring", "tags" : "Spring Data" }
{ "_id" : ObjectId("5fc4e460c07669d59be84ee2"), "title" : "Spring", "tags" : "Spring Cloud" }
{ "_id" : ObjectId("5fc4e664c07669d59be84ee3"), "title" : "Web", "tags" : "Jsp" }
{ "_id" : ObjectId("5fc4e664c07669d59be84ee3"), "title" : "Web", "tags" : "Servlet" }
{ "_id" : ObjectId("5fc4e664c07669d59be84ee4"), "title" : "RPC", "tags" : "RMI" }
{ "_id" : ObjectId("5fc4e664c07669d59be84ee4"), "title" : "RPC", "tags" : "Dubbo" }
{ "_id" : ObjectId("5fc4e664c07669d59be84ee5"), "title" : "DataBase", "tags" : "Oracle" }
{ "_id" : ObjectId("5fc4e664c07669d59be84ee5"), "title" : "DataBase", "tags" : "MySQL" }
如图所示:
7.1 聚合投影约束
查询dev集合,将数组中的内容拆分显示,并只显示title键与tags键的值
> db.dev.aggregate([{$unwind:"$tags"},{$project:{_id:0,tags:"$tags",title:"$title"}}])
{ "tags" : "Java", "title" : "北京尚学堂" }
{ "tags" : "大数据", "title" : "北京尚学堂" }
{ "tags" : "Python", "title" : "北京尚学堂" }
{ "tags" : "JavaWeb实战", "title" : "百战程序员" }
{ "tags" : "数据库实战", "title" : "百战程序员" }
{ "tags" : "微服务实战", "title" : "百战程序员" }
{ "tags" : "Hadoop", "title" : "尚学堂大数据" }
{ "tags" : "Spark", "title" : "尚学堂大数据" }
{ "tags" : "Hbase", "title" : "尚学堂大数据" }
{ "tags" : "MyBatis", "title" : "ORM" }
{ "tags" : "Hibernate", "title" : "ORM" }
{ "tags" : "SpringMVC", "title" : "Spring" }
{ "tags" : "Spring Data", "title" : "Spring" }
{ "tags" : "Spring Cloud", "title" : "Spring" }
{ "tags" : "Jsp", "title" : "Web" }
{ "tags" : "Servlet", "title" : "Web" }
{ "tags" : "RMI", "title" : "RPC" }
{ "tags" : "Dubbo", "title" : "RPC" }
{ "tags" : "Oracle", "title" : "DataBase" }
{ "tags" : "MySQL", "title" : "DataBase" }
查询dev集合,将数组中的内容拆分显示,并只显示title键与tags键的值,并将title键修改为Title
> db.dev.aggregate([{$unwind:"$tags"},{$project:{_id:0,Title:"$title",tags:"$tags"}}])
{ "Title" : "北京尚学堂", "tags" : "Java" }
{ "Title" : "北京尚学堂", "tags" : "大数据" }
{ "Title" : "北京尚学堂", "tags" : "Python" }
{ "Title" : "百战程序员", "tags" : "JavaWeb实战" }
{ "Title" : "百战程序员", "tags" : "数据库实战" }
{ "Title" : "百战程序员", "tags" : "微服务实战" }
{ "Title" : "尚学堂大数据", "tags" : "Hadoop" }
{ "Title" : "尚学堂大数据", "tags" : "Spark" }
{ "Title" : "尚学堂大数据", "tags" : "Hbase" }
{ "Title" : "ORM", "tags" : "MyBatis" }
{ "Title" : "ORM", "tags" : "Hibernate" }
{ "Title" : "Spring", "tags" : "SpringMVC" }
{ "Title" : "Spring", "tags" : "Spring Data" }
{ "Title" : "Spring", "tags" : "Spring Cloud" }
{ "Title" : "Web", "tags" : "Jsp" }
{ "Title" : "Web", "tags" : "Servlet" }
{ "Title" : "RPC", "tags" : "RMI" }
{ "Title" : "RPC", "tags" : "Dubbo" }
{ "Title" : "DataBase", "tags" : "Oracle" }
{ "Title" : "DataBase", "tags" : "MySQL" }
7.2 字符串处理
查询dev集合,将数组中的内容拆分显示。将title中的值转换为小写并命名为New_Title,将tags的值转换为大写并命名为New_Tags
> db.dev.aggregate([{$unwind:"$tags"},{$project:{_id:0,New_Title:{$toLower:"$title"},New_Tags:{$toUpper:"$tags"}}}])
{ "New_Title" : "北京尚学堂", "New_Tags" : "JAVA" }
{ "New_Title" : "北京尚学堂", "New_Tags" : "大数据" }
{ "New_Title" : "北京尚学堂", "New_Tags" : "PYTHON" }
{ "New_Title" : "百战程序员", "New_Tags" : "JAVAWEB实战" }
{ "New_Title" : "百战程序员", "New_Tags" : "数据库实战" }
{ "New_Title" : "百战程序员", "New_Tags" : "微服务实战" }
{ "New_Title" : "尚学堂大数据", "New_Tags" : "HADOOP" }
{ "New_Title" : "尚学堂大数据", "New_Tags" : "SPARK" }
{ "New_Title" : "尚学堂大数据", "New_Tags" : "HBASE" }
{ "New_Title" : "orm", "New_Tags" : "MYBATIS" }
{ "New_Title" : "orm", "New_Tags" : "HIBERNATE" }
{ "New_Title" : "spring", "New_Tags" : "SPRINGMVC" }
{ "New_Title" : "spring", "New_Tags" : "SPRING DATA" }
{ "New_Title" : "spring", "New_Tags" : "SPRING CLOUD" }
{ "New_Title" : "web", "New_Tags" : "JSP" }
{ "New_Title" : "web", "New_Tags" : "SERVLET" }
{ "New_Title" : "rpc", "New_Tags" : "RMI" }
{ "New_Title" : "rpc", "New_Tags" : "DUBBO" }
{ "New_Title" : "database", "New_Tags" : "ORACLE" }
{ "New_Title" : "database", "New_Tags" : "MYSQL" }
查询dev集合,将数组中的内容拆分显示。将title字段和tags字段的值拼接为一个完整字符串并在Title_Tags字段中显示
> db.dev.aggregate([{$unwind:"$tags"},{$project:{_id:0,Title_Tags:{$concat:["$title","-","$tags"]}}}])
{ "Title_Tags" : "北京尚学堂-Java" }
{ "Title_Tags" : "北京尚学堂-大数据" }
{ "Title_Tags" : "北京尚学堂-Python" }
{ "Title_Tags" : "百战程序员-JavaWeb实战" }
{ "Title_Tags" : "百战程序员-数据库实战" }
{ "Title_Tags" : "百战程序员-微服务实战" }
{ "Title_Tags" : "尚学堂大数据-Hadoop" }
{ "Title_Tags" : "尚学堂大数据-Spark" }
{ "Title_Tags" : "尚学堂大数据-Hbase" }
{ "Title_Tags" : "ORM-MyBatis" }
{ "Title_Tags" : "ORM-Hibernate" }
{ "Title_Tags" : "Spring-SpringMVC" }
{ "Title_Tags" : "Spring-Spring Data" }
{ "Title_Tags" : "Spring-Spring Cloud" }
{ "Title_Tags" : "Web-Jsp" }
{ "Title_Tags" : "Web-Servlet" }
{ "Title_Tags" : "RPC-RMI" }
{ "Title_Tags" : "RPC-Dubbo" }
{ "Title_Tags" : "DataBase-Oracle" }
{ "Title_Tags" : "DataBase-MySQL" }