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
arrayA 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 }
Leave a Reply