Friday, June 17, 2016

trying out aggregations on mongodb

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!

No comments:

Post a Comment