Today, we will again look into
mongodb but on the specific topic of
aggregation.
Aggregations operations process data records and return computed results. Aggregation operations group values from multiple documents together, and can perform a variety of operations on the grouped data to return a single result. MongoDB provides three ways to perform aggregation: the aggregation pipeline, the map-reduce function, and single purpose aggregation methods.
Let's start with a sample aggregation from the
zip code.Importing 29353 objects within second. Blazing fast, maybe because I'm using ssd, heh.
user@localhost:~$ mongoimport --collection zipcodes < ~/Desktop/zips.json
connected to: 127.0.0.1
Tue Mar 8 20:21:37.950 check 9 29353
Tue Mar 8 20:21:38.099 imported 29353 objects
> db.zipcodes.aggregate( [ { $group: { _id: "$state", totalPop: { $sum: "$pop" } } }, { $match: { totalPop: { $gte: 10*1000*1000 } } } ] )
{
"result" : [
{
"_id" : "IL",
"totalPop" : 11427576
},
{
"_id" : "OH",
"totalPop" : 10846517
},
{
"_id" : "FL",
"totalPop" : 12686644
},
{
"_id" : "NY",
"totalPop" : 17990402
},
{
"_id" : "PA",
"totalPop" : 11881643
},
{
"_id" : "TX",
"totalPop" : 16984601
},
{
"_id" : "CA",
"totalPop" : 29754890
}
],
"ok" : 1
}
> db.zipcodes.aggregate( [ { $group: { _id: { state: "$state", city: "$city" }, pop: { $sum: "$pop" } } }, { $group: { _id: "$_id.state", avgCityPop: { $avg: "$pop" } } } ] )
{
"result" : [
{
"_id" : "NH",
"avgCityPop" : 5232.320754716981
},
{
"_id" : "MA",
"avgCityPop" : 14855.37037037037
},
{
"_id" : "ME",
"avgCityPop" : 3006.4901960784314
},
{
"_id" : "NY",
"avgCityPop" : 13131.680291970803
},
{
"_id" : "VT",
"avgCityPop" : 2315.8765432098767
},
{
"_id" : "PA",
"avgCityPop" : 8679.067202337472
},
{
"_id" : "DE",
"avgCityPop" : 14481.91304347826
},
{
"_id" : "DC",
"avgCityPop" : 303450
},
{
"_id" : "VA",
"avgCityPop" : 8526.177931034483
},
{
"_id" : "SC",
"avgCityPop" : 11139.626198083068
},
{
"_id" : "FL",
"avgCityPop" : 27400.958963282937
},
{
"_id" : "AL",
"avgCityPop" : 7907.2152641878665
},
{
"_id" : "NJ",
"avgCityPop" : 15775.89387755102
},
{
"_id" : "WV",
"avgCityPop" : 2771.4775888717154
},
{
"_id" : "TN",
"avgCityPop" : 9656.350495049504
},
{
"_id" : "OH",
"avgCityPop" : 12700.839578454332
},
{
"_id" : "MD",
"avgCityPop" : 12615.775725593667
},
{
"_id" : "MN",
"avgCityPop" : 5372.21375921376
},
{
"_id" : "ND",
"avgCityPop" : 1645.0309278350514
},
{
"_id" : "NC",
"avgCityPop" : 10622.815705128205
},
{
"_id" : "MT",
"avgCityPop" : 2593.987012987013
},
{
"_id" : "IL",
"avgCityPop" : 9954.334494773519
},
{
"_id" : "MO",
"avgCityPop" : 5672.195338512764
},
{
"_id" : "KS",
"avgCityPop" : 3819.884259259259
},
{
"_id" : "LA",
"avgCityPop" : 10465.496277915632
},
{
"_id" : "AR",
"avgCityPop" : 4175.355239786856
},
{
"_id" : "CO",
"avgCityPop" : 9981.075757575758
},
{
"_id" : "IN",
"avgCityPop" : 9271.130434782608
},
{
"_id" : "KY",
"avgCityPop" : 4767.164721141375
},
{
"_id" : "OK",
"avgCityPop" : 6155.743639921722
},
{
"_id" : "ID",
"avgCityPop" : 4320.811158798283
},
{
"_id" : "WY",
"avgCityPop" : 3384.5373134328356
},
{
"_id" : "UT",
"avgCityPop" : 9518.508287292818
},
{
"_id" : "NV",
"avgCityPop" : 18209.590909090908
},
{
"_id" : "NE",
"avgCityPop" : 3034.882692307692
},
{
"_id" : "RI",
"avgCityPop" : 19292.653846153848
},
{
"_id" : "NM",
"avgCityPop" : 5872.360465116279
},
{
"_id" : "CA",
"avgCityPop" : 27756.42723880597
},
{
"_id" : "AZ",
"avgCityPop" : 20591.16853932584
},
{
"_id" : "HI",
"avgCityPop" : 15831.842857142858
},
{
"_id" : "IA",
"avgCityPop" : 3123.0821147356583
},
{
"_id" : "MS",
"avgCityPop" : 7524.023391812865
},
{
"_id" : "WI",
"avgCityPop" : 7323.00748502994
},
{
"_id" : "TX",
"avgCityPop" : 13775.02108678021
},
{
"_id" : "SD",
"avgCityPop" : 1839.6746031746031
},
{
"_id" : "MI",
"avgCityPop" : 12087.512353706112
},
{
"_id" : "GA",
"avgCityPop" : 11547.62210338681
},
{
"_id" : "OR",
"avgCityPop" : 8262.561046511628
},
{
"_id" : "CT",
"avgCityPop" : 14674.625
},
{
"_id" : "WA",
"avgCityPop" : 12258.670025188916
},
{
"_id" : "AK",
"avgCityPop" : 2976.4918032786886
}
],
"ok" : 1
}
> db.zipcodes.aggregate( [ { $group: { _id: { state: "$state", city: "$city" }, pop: { $sum: "$pop" } } }, { $sort: { pop: 1 } }, { $group: { _id : "$_id.state", biggestCity: { $last: "$_id.city" }, biggestPop: { $last: "$pop" }, smallestCity: { $first: "$_id.city" }, smallestPop: { $first: "$pop" } } }, { $project: { _id: 0, state: "$_id", biggestCity: { name: "$biggestCity", pop: "$biggestPop" }, smallestCity: { name: "$smallestCity", pop: "$smallestPop" } } } ] )
{
"result" : [
{
"biggestCity" : {
"name" : "NEWARK",
"pop" : 111674
},
"smallestCity" : {
"name" : "BETHEL",
"pop" : 108
},
"state" : "DE"
},
{
"biggestCity" : {
"name" : "SAINT LOUIS",
"pop" : 397802
},
"smallestCity" : {
"name" : "BENDAVIS",
"pop" : 44
},
"state" : "MO"
},
{
"biggestCity" : {
"name" : "CHICAGO",
"pop" : 2452177
},
"smallestCity" : {
"name" : "ANCONA",
"pop" : 38
},
"state" : "IL"
},
{
"biggestCity" : {
"name" : "CLEVELAND",
"pop" : 536759
},
"smallestCity" : {
"name" : "ISLE SAINT GEORG",
"pop" : 38
},
"state" : "OH"
},
{
"biggestCity" : {
"name" : "MANCHESTER",
"pop" : 106452
},
"smallestCity" : {
"name" : "WEST NOTTINGHAM",
"pop" : 27
},
"state" : "NH"
},
{
"biggestCity" : {
"name" : "WASHINGTON",
"pop" : 606879
},
"smallestCity" : {
"name" : "PENTAGON",
"pop" : 21
},
"state" : "DC"
},
{
"biggestCity" : {
"name" : "GRAND FORKS",
"pop" : 59527
},
"smallestCity" : {
"name" : "TROTTERS",
"pop" : 12
},
"state" : "ND"
},
{
"biggestCity" : {
"name" : "BALTIMORE",
"pop" : 733081
},
"smallestCity" : {
"name" : "ANNAPOLIS JUNCTI",
"pop" : 32
},
"state" : "MD"
},
{
"biggestCity" : {
"name" : "MINNEAPOLIS",
"pop" : 344719
},
"smallestCity" : {
"name" : "JOHNSON",
"pop" : 12
},
"state" : "MN"
},
{
"biggestCity" : {
"name" : "SALT LAKE CITY",
"pop" : 186346
},
"smallestCity" : {
"name" : "MODENA",
"pop" : 9
},
"state" : "UT"
},
{
"biggestCity" : {
"name" : "CHEYENNE",
"pop" : 70185
},
"smallestCity" : {
"name" : "LOST SPRINGS",
"pop" : 6
},
"state" : "WY"
},
{
"biggestCity" : {
"name" : "PHOENIX",
"pop" : 890853
},
"smallestCity" : {
"name" : "HUALAPAI",
"pop" : 2
},
"state" : "AZ"
},
{
"biggestCity" : {
"name" : "BRIDGEPORT",
"pop" : 141638
},
"smallestCity" : {
"name" : "EAST KILLINGLY",
"pop" : 25
},
"state" : "CT"
},
{
"biggestCity" : {
"name" : "SEATTLE",
"pop" : 520096
},
"smallestCity" : {
"name" : "BENGE",
"pop" : 2
},
"state" : "WA"
},
{
"biggestCity" : {
"name" : "BIRMINGHAM",
"pop" : 242606
},
"smallestCity" : {
"name" : "ALLEN",
"pop" : 0
},
"state" : "AL"
},
{
"biggestCity" : {
"name" : "LAS VEGAS",
"pop" : 597557
},
"smallestCity" : {
"name" : "TUSCARORA",
"pop" : 1
},
"state" : "NV"
},
{
"biggestCity" : {
"name" : "OMAHA",
"pop" : 358930
},
"smallestCity" : {
"name" : "LAKESIDE",
"pop" : 5
},
"state" : "NE"
},
{
"biggestCity" : {
"name" : "MIAMI",
"pop" : 825232
},
"smallestCity" : {
"name" : "CECIL FIELD NAS",
"pop" : 0
},
"state" : "FL"
},
{
"biggestCity" : {
"name" : "SIOUX FALLS",
"pop" : 102046
},
"smallestCity" : {
"name" : "ZEONA",
"pop" : 8
},
"state" : "SD"
},
{
"biggestCity" : {
"name" : "HOUSTON",
"pop" : 2095918
},
"smallestCity" : {
"name" : "FULTON",
"pop" : 0
},
"state" : "TX"
},
{
"biggestCity" : {
"name" : "MILWAUKEE",
"pop" : 597324
},
"smallestCity" : {
"name" : "CLAM LAKE",
"pop" : 2
},
"state" : "WI"
},
{
"biggestCity" : {
"name" : "JACKSON",
"pop" : 204788
},
"smallestCity" : {
"name" : "CHUNKY",
"pop" : 79
},
"state" : "MS"
},
{
"biggestCity" : {
"name" : "DES MOINES",
"pop" : 148155
},
"smallestCity" : {
"name" : "DOUDS",
"pop" : 15
},
"state" : "IA"
},
{
"biggestCity" : {
"name" : "HONOLULU",
"pop" : 396643
},
"smallestCity" : {
"name" : "NINOLE",
"pop" : 0
},
"state" : "HI"
},
{
"biggestCity" : {
"name" : "CHARLOTTE",
"pop" : 465833
},
"smallestCity" : {
"name" : "GLOUCESTER",
"pop" : 0
},
"state" : "NC"
},
{
"biggestCity" : {
"name" : "BILLINGS",
"pop" : 78805
},
"smallestCity" : {
"name" : "MOSBY",
"pop" : 7
},
"state" : "MT"
},
{
"biggestCity" : {
"name" : "TULSA",
"pop" : 389072
},
"smallestCity" : {
"name" : "SOUTHARD",
"pop" : 8
},
"state" : "OK"
},
{
"biggestCity" : {
"name" : "BOISE",
"pop" : 165522
},
"smallestCity" : {
"name" : "KEUTERVILLE",
"pop" : 0
},
"state" : "ID"
},
{
"biggestCity" : {
"name" : "INDIANAPOLIS",
"pop" : 348868
},
"smallestCity" : {
"name" : "WESTPOINT",
"pop" : 145
},
"state" : "IN"
},
{
"biggestCity" : {
"name" : "LOUISVILLE",
"pop" : 288058
},
"smallestCity" : {
"name" : "BROWDER",
"pop" : 0
},
"state" : "KY"
},
{
"biggestCity" : {
"name" : "BURLINGTON",
"pop" : 39127
},
"smallestCity" : {
"name" : "UNIV OF VERMONT",
"pop" : 0
},
"state" : "VT"
},
{
"biggestCity" : {
"name" : "PHILADELPHIA",
"pop" : 1610956
},
"smallestCity" : {
"name" : "HAMILTON",
"pop" : 0
},
"state" : "PA"
},
{
"biggestCity" : {
"name" : "NEW ORLEANS",
"pop" : 496937
},
"smallestCity" : {
"name" : "FORDOCHE",
"pop" : 0
},
"state" : "LA"
},
{
"biggestCity" : {
"name" : "COLUMBIA",
"pop" : 269521
},
"smallestCity" : {
"name" : "QUINBY",
"pop" : 0
},
"state" : "SC"
},
{
"biggestCity" : {
"name" : "WICHITA",
"pop" : 295115
},
"smallestCity" : {
"name" : "ARNOLD",
"pop" : 0
},
"state" : "KS"
},
{
"biggestCity" : {
"name" : "NEWARK",
"pop" : 275572
},
"smallestCity" : {
"name" : "IMLAYSTOWN",
"pop" : 17
},
"state" : "NJ"
},
{
"biggestCity" : {
"name" : "HUNTINGTON",
"pop" : 75343
},
"smallestCity" : {
"name" : "MOUNT CARBON",
"pop" : 0
},
"state" : "WV"
},
{
"biggestCity" : {
"name" : "MEMPHIS",
"pop" : 632837
},
"smallestCity" : {
"name" : "ALLRED",
"pop" : 2
},
"state" : "TN"
},
{
"biggestCity" : {
"name" : "VIRGINIA BEACH",
"pop" : 385080
},
"smallestCity" : {
"name" : "WALLOPS ISLAND",
"pop" : 0
},
"state" : "VA"
},
{
"biggestCity" : {
"name" : "ANCHORAGE",
"pop" : 183987
},
"smallestCity" : {
"name" : "CHEVAK",
"pop" : 0
},
"state" : "AK"
},
{
"biggestCity" : {
"name" : "BROOKLYN",
"pop" : 2300504
},
"smallestCity" : {
"name" : "RAQUETTE LAKE",
"pop" : 0
},
"state" : "NY"
},
{
"biggestCity" : {
"name" : "DENVER",
"pop" : 451182
},
"smallestCity" : {
"name" : "CHEYENNE MTN AFB",
"pop" : 0
},
"state" : "CO"
},
{
"biggestCity" : {
"name" : "DETROIT",
"pop" : 963243
},
"smallestCity" : {
"name" : "LELAND",
"pop" : 0
},
"state" : "MI"
},
{
"biggestCity" : {
"name" : "PORTLAND",
"pop" : 518543
},
"smallestCity" : {
"name" : "KENT",
"pop" : 0
},
"state" : "OR"
},
{
"biggestCity" : {
"name" : "ATLANTA",
"pop" : 609591
},
"smallestCity" : {
"name" : "FORT STEWART",
"pop" : 0
},
"state" : "GA"
},
{
"biggestCity" : {
"name" : "CRANSTON",
"pop" : 176404
},
"smallestCity" : {
"name" : "CLAYVILLE",
"pop" : 45
},
"state" : "RI"
},
{
"biggestCity" : {
"name" : "ALBUQUERQUE",
"pop" : 449584
},
"smallestCity" : {
"name" : "ALGODONES",
"pop" : 0
},
"state" : "NM"
},
{
"biggestCity" : {
"name" : "LOS ANGELES",
"pop" : 2102295
},
"smallestCity" : {
"name" : "TWIN BRIDGES",
"pop" : 0
},
"state" : "CA"
},
{
"biggestCity" : {
"name" : "LITTLE ROCK",
"pop" : 192895
},
"smallestCity" : {
"name" : "TOMATO",
"pop" : 0
},
"state" : "AR"
},
{
"biggestCity" : {
"name" : "WORCESTER",
"pop" : 169856
},
"smallestCity" : {
"name" : "BUCKLAND",
"pop" : 16
},
"state" : "MA"
},
{
"biggestCity" : {
"name" : "PORTLAND",
"pop" : 63268
},
"smallestCity" : {
"name" : "BUSTINS ISLAND",
"pop" : 0
},
"state" : "ME"
}
],
"ok" : 1
}
>
All query in the examples works. It's amazing all three queries quickly bring results within second! Amazing. Whilst this is an short article to convince you to use aggregation on mongodb, and if you have been convince, you should really try on the following useful links too.
https://docs.mongodb.org/manual/core/map-reduce/
https://docs.mongodb.org/manual/reference/aggregation/
good luck!
