DATA ANALYTICS REFERENCE DOCUMENT |
|
---|---|
Document Title: | Applied Databases Mongo DB II Worksheet |
Document No.: | 1553017429 |
Author(s): | Gerhard van der Linde |
Contributor(s): |
REVISION HISTORY
Revision | Details of Modification(s) | Reason for modification | Date | By |
---|---|---|---|---|
0 | Draft release | Applied Databases Mongo DB II Worksheet | 2019/03/19 17:43 | Gerhard van der Linde |
mongoimport --db w7 --collection cityinfo --type json --file ../../../documents/lab7.json 2019-03-21T11:31:23.436+0000 connected to: localhost 2019-03-21T11:31:23.455+0000 imported 19 documents
//Query db.cityinfo.find({$and:[{city:{$exists:true}},{pop:{$gt:10000}}]})
//Result { "_id" : "02906", "city" : "PROVIDENCE", "pop" : 31069, "state" : "RI", "capital" : { "name" : "Providence", "electoralCollege" : 4 } } { "_id" : "02907", "city" : "CRANSTON", "pop" : 25668, "state" : "RI", "capital" : { "name" : "Providence", "electoralCollege" : 4 } } { "_id" : "10001", "city" : "NEW YORK", "pop" : 18913, "state" : "NY", "capital" : { "name" : "Albany", "electoralCollege" : 29 } } { "_id" : "01001", "city" : "AGAWAM", "pop" : 15338, "state" : "MA", "capital" : { "name" : "Boston", "electoralCollege" : 11 } } { "_id" : "33125", "city" : "MIAMI", "pop" : 47761, "state" : "FL", "capital" : { "name" : "Tallahassee", "electoralCollege" : 29 } }
db.cityinfo.find({city:{$exists:true}},{_id:false,state:1, pop:1})
{ "pop" : 31069, "state" : "RI" } { "pop" : 177, "state" : "MA" } { "pop" : 2867, "state" : "NY" } { "pop" : 25668, "state" : "RI" } { "pop" : 943, "state" : "NY" } { "pop" : 18913, "state" : "NY" } { "pop" : 958, "state" : "NY" } { "pop" : 15338, "state" : "MA" } { "pop" : 47761, "state" : "FL" } { "pop" : 9275, "state" : "FL" } { "pop" : 7655, "state" : "FL" } { "pop" : 3697, "state" : "MA" }
or an aggregated interpretation
db.cityinfo.aggregate([{$match:{city:{$exists:true}}},{$group:{_id:"$state", "Total by State":{$sum:"$pop"}}}])
{ "_id" : "NY", "Total by State" : 23681 } { "_id" : "FL", "Total by State" : 64691 } { "_id" : "MA", "Total by State" : 19212 } { "_id" : "RI", "Total by State" : 56737 }
db.cityinfo.aggregate([{$match:{state:"NY"}},{$group:{_id:"$state","Population":{$sum:"$pop"}}}])
{ "_id" : "NY", "Population" : 23681 }
db.cityinfo.find({$and:[{city:{$exists:true}},{pop:{$gt:20000}}]},{city:1,"capital.name":1})
{ "_id" : "02906", "city" : "PROVIDENCE", "capital" : { "name" : "Providence" } } { "_id" : "02907", "city" : "CRANSTON", "capital" : { "name" : "Providence" } } { "_id" : "33125", "city" : "MIAMI", "capital" : { "name" : "Tallahassee" } }
db.cityinfo.aggregate([{$match:{name:"Tom"}},{$lookup:{from:"cityinfo", localField:"addresses", foreignField: "_id", as:"Details"}}]).pretty()
{ "_id" : "1", "name" : "Tom", "addresses" : [ "01001", "12997" ], "Details" : [ { "_id" : "01001", "city" : "AGAWAM", "pop" : 15338, "state" : "MA", "capital" : { "name" : "Boston", "electoralCollege" : 11 } }, { "_id" : "12997", "city" : "WILMINGTON", "pop" : 958, "state" : "NY", "capital" : { "name" : "Albany", "electoralCollege" : 29 } } ] }
HINT: Use the $project aggregation pipeline stage.
db.cityinfo.aggregate([{$match:{city:"CHESTERFIELD"}},{$lookup:{from:"cityinfo", localField:"state", foreignField:"_id", as:"StateInfo"}},{$project:{capital:0}}]).pretty()
{ "_id" : "01012", "city" : "CHESTERFIELD", "pop" : 177, "state" : "MA", "StateInfo" : [ { "_id" : "MA", "name" : "Massachusetts", "pop" : 6868000, "state" : 1790 } ] }