~~CLOSETOC~~
|<100% 25% - >|
^  \\ 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**
|< 100% 10% - - 10% 17% 10% >|
^  \\  Revision\\  \\  ^\\ Details of Modification(s)^\\ Reason for modification^  \\ Date  ^  \\ By  ^
|  [[:doku.php?id=submissions:worksheet:databases:topic7&do=revisions|0]]  |Draft release|Applied Databases Mongo DB II Worksheet|  2019/03/19 17:43  |  Gerhard van der Linde  |


----

====== Applied Databases - Topic 7 ======

===== 1. Import lab7.json to MongoDB. =====

<code bash>
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
</code>
===== 2. Show the name and population of all cities where the population is over 10,000. =====


<code mongo>
//Query
db.cityinfo.find({$and:[{city:{$exists:true}},{pop:{$gt:10000}}]})
</code>


<code mongo>
//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 } }
</code>
===== 3. Show the name and population of each state based on the cities shown. =====

<code mongo>
db.cityinfo.find({city:{$exists:true}},{_id:false,state:1, pop:1})
</code>
<code mongo>
{ "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" }
</code>

or an aggregated interpretation

<code mongo>
db.cityinfo.aggregate([{$match:{city:{$exists:true}}},{$group:{_id:"$state", "Total by State":{$sum:"$pop"}}}])
</code>
<code mongo>
{ "_id" : "NY", "Total by State" : 23681 }
{ "_id" : "FL", "Total by State" : 64691 }
{ "_id" : "MA", "Total by State" : 19212 }
{ "_id" : "RI", "Total by State" : 56737 }
</code>

===== 4. Show the total population of cities in NY as “Population”. =====
<code mongo>
db.cityinfo.aggregate([{$match:{state:"NY"}},{$group:{_id:"$state","Population":{$sum:"$pop"}}}])
</code>
<code mongo>
{ "_id" : "NY", "Population" : 23681 }
</code>
===== 5. Show the _id, city and name of the capital city of each state for cities with a population greater than 20,000. =====

<code mongo>
db.cityinfo.find({$and:[{city:{$exists:true}},{pop:{$gt:20000}}]},{city:1,"capital.name":1})
</code>
<code mongo>
{ "_id" : "02906", "city" : "PROVIDENCE", "capital" : { "name" : "Providence" } }
{ "_id" : "02907", "city" : "CRANSTON", "capital" : { "name" : "Providence" } }
{ "_id" : "33125", "city" : "MIAMI", "capital" : { "name" : "Tallahassee" } }
</code>
===== 6. Show all details for “Tom” including full details of his addresses. =====
<code mongo>
db.cityinfo.aggregate([{$match:{name:"Tom"}},{$lookup:{from:"cityinfo", localField:"addresses", foreignField: "_id", as:"Details"}}]).pretty()
</code>
<code mongo>
{
        "_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
                        }
                }
        ]
}
</code>

===== 7. Show all details for “Chesterfield” including full details of the state, but do not show details relating to its capital. =====

**HINT:** Use the $project aggregation pipeline stage.

<code mongo chesterfield.txt>
db.cityinfo.aggregate([{$match:{city:"CHESTERFIELD"}},{$lookup:{from:"cityinfo", localField:"state", foreignField:"_id", as:"StateInfo"}},{$project:{capital:0}}]).pretty()
</code>
<code mongo>
{
        "_id" : "01012",
        "city" : "CHESTERFIELD",
        "pop" : 177,
        "state" : "MA",
        "StateInfo" : [
                {
                        "_id" : "MA",
                        "name" : "Massachusetts",
                        "pop" : 6868000,
                        "state" : 1790
                }
        ]
}
</code>