MongoDB Aggregation聚集测试

db.alpha.insert({_id:1, region:"NW1", leads:1 , email: "[email protected]"});
db.alpha.insert({_id:2, region:"NW1", leads:1 , email: "[email protected]"});
db.alpha.insert({_id:3, region:"NW1", leads:2 , email: "[email protected]"});
db.alpha.insert({_id:4, region:"SE1", leads:8 , email: "[email protected]"});
db.alpha.insert({_id:5, region:"SE2", leads:4 , email: "[email protected]"});
db.alpha.insert({_id:6, region:"SE2", leads:2 , email: "[email protected]"});
db.alpha.insert({_id:7, region:"SE2", leads:4 , email: "[email protected]"});
db.alpha.insert({_id:8, region:"SW1", leads:1 , email: "[email protected]"});
db.alpha.insert({_id:9, region:"SW1", leads:2 , email: "[email protected]"});
db.alpha.insert({_id:10, region:"SW2", leads:2 , email: "[email protected]"});
db.alpha.insert({_id:11, region:"SW2", leads:5, email: "[email protected]"});


> db.alpha.find();
{ "_id" : 1, "region" : "NW1", "leads" : 1, "email" : "[email protected]" }
{ "_id" : 2, "region" : "NW1", "leads" : 1, "email" : "[email protected]" }
{ "_id" : 3, "region" : "NW1", "leads" : 2, "email" : "[email protected]" }
{ "_id" : 4, "region" : "SE1", "leads" : 8, "email" : "[email protected]" }
{ "_id" : 5, "region" : "SE2", "leads" : 4, "email" : "[email protected]" }
{ "_id" : 6, "region" : "SE2", "leads" : 2, "email" : "[email protected]" }
{ "_id" : 7, "region" : "SE2", "leads" : 4, "email" : "[email protected]" }
{ "_id" : 8, "region" : "SW1", "leads" : 1, "email" : "[email protected]" }
{ "_id" : 9, "region" : "SW1", "leads" : 2, "email" : "[email protected]" }
{ "_id" : 10, "region" : "SW2", "leads" : 2, "email" : "[email protected]" }
{ "_id" : 11, "region" : "SW2", "leads" : 5, "email" : "[email protected]" }


> 
> db.alpha.aggregate ( [ { "$group" : { "_id" : "$region" , leads : { "$sum" :1 }}} , { "$match" : { "leads" : { "$gte" :3 }}} ])
{ "_id" : "SE2", "leads" : 3 }
{ "_id" : "NW1", "leads" : 3 }
> 

 

db.alpha.aggregate ( [ { "$group" : { "_id" : "$region" , leads : { "$sum" :1 }}} , { "$match" : { "leads" : { "$gte" :3 }}} ])
{ "_id" : "SE2", "leads" : 3 }
{ "_id" : "NW1", "leads" : 3 }

其相当于SQL的

select region,count(*) from alpha group by region having count(*)>=3;

db.alpha.aggregate ( [ { "$group" : { "_id" : "$region" , "leads" : { "$sum" : "$leads" }}} , { "$match" : { "leads" : { "$gte" :2 }}} ]);

其相当于SQL的
select region,sum(leads) from alpha group by region having sum(leads)>=3;

db.collection.aggregate 的语法如下:

db.collection.aggregate()

 

New in version 2.2.

 

Definition

 

db.collection.aggregate(pipeline, options)
Calculates aggregate values for the data in a collection.

Type
Description
array

A sequence of data aggregation operations or stages. See theaggregation pipeline operators for details.

 

Changed in version 2.6: The method can still accept the pipeline stages as separate arguments instead of as elements in an array; however, if you do not specify the pipeline as an array, you cannot specify theoptions parameter.

document

Optional. Additional options that aggregate() passes to theaggregate command.

 

New in version 2.6: Available only if you specify the pipeline as an array.
Parameter
pipeline
options

 

db.stuff.insert ( { _id:1 , "x":1, "y" : [1,2,3,4]});
db.stuff.insert ( { _id:2 , "x":2, "y" : [1,2,3,4]});
db.stuff.insert ( { _id:3 , "x":3, "y" : [1,2,3,4]});
db.stuff.insert ( { _id:4 , "x":4, "y" : [1,2,3,4]});
db.stuff.insert ( { _id:5 , "x":5, "y" : [1,2,3,4]});



> db.stuff.aggregate([ { $unwind : "$y" }]);
{ "_id" : 1, "x" : 1, "y" : 1 }
{ "_id" : 1, "x" : 1, "y" : 2 }
{ "_id" : 1, "x" : 1, "y" : 3 }
{ "_id" : 1, "x" : 1, "y" : 4 }
{ "_id" : 2, "x" : 2, "y" : 1 }
{ "_id" : 2, "x" : 2, "y" : 2 }
{ "_id" : 2, "x" : 2, "y" : 3 }
{ "_id" : 2, "x" : 2, "y" : 4 }
{ "_id" : 3, "x" : 3, "y" : 1 }
{ "_id" : 3, "x" : 3, "y" : 2 }
{ "_id" : 3, "x" : 3, "y" : 3 }
{ "_id" : 3, "x" : 3, "y" : 4 }
{ "_id" : 4, "x" : 4, "y" : 1 }
{ "_id" : 4, "x" : 4, "y" : 2 }
{ "_id" : 4, "x" : 4, "y" : 3 }
{ "_id" : 4, "x" : 4, "y" : 4 }
{ "_id" : 5, "x" : 5, "y" : 1 }
{ "_id" : 5, "x" : 5, "y" : 2 }
{ "_id" : 5, "x" : 5, "y" : 3 }
{ "_id" : 5, "x" : 5, "y" : 4 }


> db.stuff.aggregate([ { $unwind : "$y" }  , { $project : { x:1, y:1, _id:0}}] );
{ "x" : 1, "y" : 1 }
{ "x" : 1, "y" : 2 }
{ "x" : 1, "y" : 3 }
{ "x" : 1, "y" : 4 }
{ "x" : 2, "y" : 1 }
{ "x" : 2, "y" : 2 }
{ "x" : 2, "y" : 3 }
{ "x" : 2, "y" : 4 }
{ "x" : 3, "y" : 1 }
{ "x" : 3, "y" : 2 }
{ "x" : 3, "y" : 3 }
{ "x" : 3, "y" : 4 }
{ "x" : 4, "y" : 1 }
{ "x" : 4, "y" : 2 }
{ "x" : 4, "y" : 3 }
{ "x" : 4, "y" : 4 }
{ "x" : 5, "y" : 1 }
{ "x" : 5, "y" : 2 }
{ "x" : 5, "y" : 3 }
{ "x" : 5, "y" : 4 }
> 

> db.stuff.aggregate([ { $unwind : "$y" }  , { $project : { x:1, y:1, _id:0}}, {$out:"otherStuff"}]);
> 
> db.otherStuff.find();
{ "_id" : ObjectId("5551b7566f1f9528e8ba7209"), "x" : 1, "y" : 1 }
{ "_id" : ObjectId("5551b7566f1f9528e8ba720a"), "x" : 1, "y" : 2 }
{ "_id" : ObjectId("5551b7566f1f9528e8ba720b"), "x" : 1, "y" : 3 }
{ "_id" : ObjectId("5551b7566f1f9528e8ba720c"), "x" : 1, "y" : 4 }
{ "_id" : ObjectId("5551b7566f1f9528e8ba720d"), "x" : 2, "y" : 1 }
{ "_id" : ObjectId("5551b7566f1f9528e8ba720e"), "x" : 2, "y" : 2 }
{ "_id" : ObjectId("5551b7566f1f9528e8ba720f"), "x" : 2, "y" : 3 }
{ "_id" : ObjectId("5551b7566f1f9528e8ba7210"), "x" : 2, "y" : 4 }
{ "_id" : ObjectId("5551b7566f1f9528e8ba7211"), "x" : 3, "y" : 1 }
{ "_id" : ObjectId("5551b7566f1f9528e8ba7212"), "x" : 3, "y" : 2 }
{ "_id" : ObjectId("5551b7566f1f9528e8ba7213"), "x" : 3, "y" : 3 }
{ "_id" : ObjectId("5551b7566f1f9528e8ba7214"), "x" : 3, "y" : 4 }
{ "_id" : ObjectId("5551b7566f1f9528e8ba7215"), "x" : 4, "y" : 1 }
{ "_id" : ObjectId("5551b7566f1f9528e8ba7216"), "x" : 4, "y" : 2 }
{ "_id" : ObjectId("5551b7566f1f9528e8ba7217"), "x" : 4, "y" : 3 }
{ "_id" : ObjectId("5551b7566f1f9528e8ba7218"), "x" : 4, "y" : 4 }
{ "_id" : ObjectId("5551b7566f1f9528e8ba7219"), "x" : 5, "y" : 1 }
{ "_id" : ObjectId("5551b7566f1f9528e8ba721a"), "x" : 5, "y" : 2 }
{ "_id" : ObjectId("5551b7566f1f9528e8ba721b"), "x" : 5, "y" : 3 }
{ "_id" : ObjectId("5551b7566f1f9528e8ba721c"), "x" : 5, "y" : 4 }


Posted

in

by

Tags:

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *