MongoDB的聚合命令

一  MongoDB的聚合命令

数据库database中的集合collection名称为test

插入文档document

db.test.insert({name:'hua',gender:'male',age:20,address:'wh'})

WriteResult({ "nInserted" : 1 })

> db.test.insert({name:'yh',gender:'male',age:30,address:'wh'})

WriteResult({ "nInserted" : 1 })

> db.test.insert({name:'zxy',gender:'female',age:35,address:'wh'})

WriteResult({ "nInserted" : 1 })

> db.test.insert({name:'yzz',gender:'female',age:18,address:'bj'})

WriteResult({ "nInserted" : 1 })

> db.test.insert({name:'yzz',gender:'male',age:28,address:'bj'})

WriteResult({ "nInserted" : 1 })

> db.test.insert({name:'zys',gender:'male',age:28,address:'bj'})

WriteResult({ "nInserted" : 1 })

> db.test.insert({name:'ljy',gender:'male',age:40,address:'sh'})

WriteResult({ "nInserted" : 1 })

> db.test.insert({name:'jxx',gender:'female',age:38,address:'sh'})

WriteResult({ "nInserted" : 1 })

db.test.aggregate({$match:{gender:'male'}})

{ "_id" : ObjectId("5f95179e1221b7ffcd13dfd8"), "name" : "hua", "gender" : "male", "age" : 20, "address" : "wh" }

{ "_id" : ObjectId("5f9517ac1221b7ffcd13dfd9"), "name" : "yh", "gender" : "male", "age" : 30, "address" : "wh" }

{ "_id" : ObjectId("5f9517fa1221b7ffcd13dfdd"), "name" : "zys", "gender" : "male", "age" : 28, "address" : "bj" }

> db.test.aggregate({$group:{_id:'$gender',count:{$sum:1}}})

{ "_id" : "female", "count" : 2 }

{ "_id" : null, "count" : 1 }

{ "_id" : "male", "count" : 3 }

> db.test.aggregate({$group:{_id:'$gender',count:{$sum:1}}},{$project:{_id:0}})

{ "count" : 2 }

{ "count" : 1 }

{ "count" : 3 }

> db.test.aggregate({$match:{gender:'male'}})

{ "_id" : ObjectId("5f95179e1221b7ffcd13dfd8"), "name" : "hua", "gender" : "male", "age" : 20, "address" : "wh" }

{ "_id" : ObjectId("5f9517ac1221b7ffcd13dfd9"), "name" : "yh", "gender" : "male", "age" : 30, "address" : "wh" }

{ "_id" : ObjectId("5f9517fa1221b7ffcd13dfdd"), "name" : "zys", "gender" : "male", "age" : 28, "address" : "bj" }

> db.test.aggregate({$group:{_id:{name:'$name',gender:'$gender',address:'$address'}}},{$group:{_id:{name:'$id.name',gender:'$_id.gender',counter:{$sum:1}}}})

{ "_id" : { "counter" : 1 } }

{ "_id" : { "gender" : "male", "counter" : 1 } }

{ "_id" : { "gender" : "female", "counter" : 1 } }

> db.test.aggregate({$group:{_id:{name:'$name',gender:'$gender',age:'$age'}}},{$group:{_id:{name:'$id.name',gender:'$_id.gender',counter:{$sum:1}}}})

{ "_id" : { "counter" : 1 } }

{ "_id" : { "gender" : "male", "counter" : 1 } }

{ "_id" : { "gender" : "female", "counter" : 1 } }

> db.test.aggregate(

{$group:{_id:{name:'$name',gender:'$gender',address:'$address',age:'$age'

}}})

{ "_id" : { "name" : "jxx", "gender" : "female", "address" : "sh", "age" : 38 } }

{ "_id" : { "name" : "ljy" } }

{ "_id" : { "name" : "zys", "gender" : "male", "address" : "bj", "age" : 28 } }

{ "_id" : { "name" : "zxy", "gender" : "female", "address" : "wh", "age" : 35 } }

{ "_id" : { "name" : "yh", "gender" : "male", "address" : "wh", "age" : 30 } }

{ "_id" : { "name" : "hua", "gender" : "male", "address" : "wh", "age" : 20 } }

> db.test.aggregate({$group:{_id:{name:'$name',gender:'$gender',address:'$address',age:'$age'}}},{$group:{_id:{name:'$_id.name',gender:'$_id.gender',address:'$_id.address'},count:{$sum:1}}})

{ "_id" : { "name" : "yh", "gender" : "male", "address" : "wh" }, "count" : 1 }

{ "_id" : { "name" : "zys", "gender" : "male", "address" : "bj" }, "count" : 1 }

{ "_id" : { "name" : "hua", "gender" : "male", "address" : "wh" }, "count" : 1 }

{ "_id" : { "name" : "zxy", "gender" : "female", "address" : "wh" }, "count" : 1 }

{ "_id" : { "name" : "ljy" }, "count" : 1 }

{ "_id" : { "name" : "jxx", "gender" : "female", "address" : "sh" }, "count" : 1 }

聚合方法中$group注意点

集合中的其余方法示例

1.聚合命令中的$sort排序方法

> db.test.aggregate({$sort:{age:-1}},{$project:{_id:0,name:1,gender:1,address:1}})

{ "name" : "jxx", "gender" : "female", "address" : "sh" }

{ "name" : "zxy", "gender" : "female", "address" : "wh" }

{ "name" : "yh", "gender" : "male", "address" : "wh" }

{ "name" : "zys", "gender" : "male", "address" : "bj" }

{ "name" : "hua", "gender" : "male", "address" : "wh" }

{ "name" : "ljy" }

> db.test.aggregate({$group:{_id:'$gender',counter:{$sum:1}}},{$sort:{counter:1}})

{ "_id" : null, "counter" : 1 }

{ "_id" : "female", "counter" : 2 }

{ "_id" : "male", "counter" : 3 }

2.聚合命令中的$limit $skip 方法

db.test.aggregate({$limit:2})

{ "_id" : ObjectId("5f95179e1221b7ffcd13dfd8"), "name" : "hua", "gender" : "male", "age" : 20, "address" : "wh" }

{ "_id" : ObjectId("5f9517ac1221b7ffcd13dfd9"), "name" : "yh", "gender" : "male", "age" : 30, "address" : "wh" }

db.test.aggregate({$skip:3})

{ "_id" : ObjectId("5f9517fa1221b7ffcd13dfdd"), "name" : "zys", "gender" : "male", "age" : 28, "address" : "bj" }

{ "_id" : ObjectId("5f9518091221b7ffcd13dfde"), "name" : "ljy" }

{ "_id" : ObjectId("5f9518181221b7ffcd13dfdf"), "name" : "jxx", "gender" : "female", "age" : 38, "address" : "sh" }

db.test.aggregate({$limit:5},{$skip:3})

{ "_id" : ObjectId("5f9517fa1221b7ffcd13dfdd"), "name" : "zys", "gender" : "male", "age" : 28, "address" : "bj" }

{ "_id" : ObjectId("5f9518091221b7ffcd13dfde"), "name" : "ljy" }

聚合命令中的$push方法

> db.test.aggregate({$unwind:'$country'})

{ "_id" : ObjectId("5f9542af1221b7ffcd13dfe0"), "name" : "car", "type" : "hf", "country" : "china" }

{ "_id" : ObjectId("5f9542af1221b7ffcd13dfe0"), "name" : "car", "type" : "hf", "country" : "japan" }

{ "_id" : ObjectId("5f9542af1221b7ffcd13dfe0"), "name" : "car", "type" : "hf", "country" : "asia" }

{ "_id" : ObjectId("5f9542af1221b7ffcd13dfe0"), "name" : "car", "type" : "hf", "country" : "england" }

> db.test.aggregate({$group:{_id:'$gender',name:{$push:'$name'}}})

{ "_id" : "female", "name" : [ "zxy", "jxx" ] }

{ "_id" : null, "name" : [ "ljy", "car" ] }

{ "_id" : "male", "name" : [ "hua", "yh", "zys" ] }

> db.test.aggregate({$group:{_id:'$gender',name:{$push:'$$ROOT'}}})

{ "_id" : "female", "name" : [ { "_id" : ObjectId("5f9517bc1221b7ffcd13dfda"), "name" : "zxy", "gender" : "female", "age" : 35, "address" : "wh" }, { "_id" : ObjectId("5f9518181221b7ffcd13dfdf"), "name" : "jxx", "gender" : "female", "age" : 38, "address" : "sh" } ] }

{ "_id" : null, "name" : [ { "_id" : ObjectId("5f9518091221b7ffcd13dfde"), "name" : "ljy" }, { "_id" : ObjectId("5f9542af1221b7ffcd13dfe0"), "name" : "car", "type" : "hf", "country" : [ "china", "japan", "asia", "england" ] } ] }

{ "_id" : "male", "name" : [ { "_id" : ObjectId("5f95179e1221b7ffcd13dfd8"), "name" : "hua", "gender" : "male", "age" : 20, "address" : "wh" }, { "_id" : ObjectId("5f9517ac1221b7ffcd13dfd9"), "name" : "yh", "gender" : "male", "age" : 30, "address" : "wh" }, { "_id" : ObjectId("5f9517fa1221b7ffcd13dfdd"), "name" : "zys", "gender" : "male", "age" : 28, "address" : "bj" } ] }

> db.test.insert({name:'car',type:'hf',country:['china','japan','asia','england']})

WriteResult({ "nInserted" : 1 })

>  db.test.aggregate({$unwind:'$country'})

{ "_id" : ObjectId("5f9542af1221b7ffcd13dfe0"), "name" : "car", "type" : "hf", "country" : "china" }

{ "_id" : ObjectId("5f9542af1221b7ffcd13dfe0"), "name" : "car", "type" : "hf", "country" : "japan" }

{ "_id" : ObjectId("5f9542af1221b7ffcd13dfe0"), "name" : "car", "type" : "hf", "country" : "asia" }

{ "_id" : ObjectId("5f9542af1221b7ffcd13dfe0"), "name" : "car", "type" : "hf", "country" : "england" }

{ "_id" : ObjectId("5f9543421221b7ffcd13dfe1"), "name" : "car", "type" : "hf", "country" : "china" }

{ "_id" : ObjectId("5f9543421221b7ffcd13dfe1"), "name" : "car", "type" : "hf", "country" : "japan" }

{ "_id" : ObjectId("5f9543421221b7ffcd13dfe1"), "name" : "car", "type" : "hf", "country" : "asia" }

{ "_id" : ObjectId("5f9543421221b7ffcd13dfe1"), "name" : "car", "type" : "hf", "country" : "england" }