Difference between revisions of "AGGREGATE Tutorial"
| Line 4: | Line 4: | ||
You may find these [[AGGREGATE examples]] useful. | You may find these [[AGGREGATE examples]] useful. | ||
<div class='extra_space' style='width:1em; height:6em;'></div> | <div class='extra_space' style='width:1em; height:6em;'></div> | ||
| + | ==Per Capita GDP== | ||
<div class=q data-lang="mongo"> | <div class=q data-lang="mongo"> | ||
Give the <code>name</code> and the <code>per capita GDP</code> for those countries with a <code>population</code> of at least 200 million. | Give the <code>name</code> and the <code>per capita GDP</code> for those countries with a <code>population</code> of at least 200 million. | ||
| Line 24: | Line 25: | ||
</div> | </div> | ||
</div> | </div> | ||
| − | + | ==Population Density in South America== | |
<div class=q data-lang="mongo"> | <div class=q data-lang="mongo"> | ||
| − | Give the <code>name</code> and the <code>population density</code> of all countries | + | Give the <code>name</code> and the <code>population density</code> of all countries in South America. |
<div class="hint" title="How to calculate population density"> | <div class="hint" title="How to calculate population density"> | ||
population density is the population divided by the area | population density is the population divided by the area | ||
| Line 35: | Line 36: | ||
<pre class=def> | <pre class=def> | ||
db.world.aggregate([ | db.world.aggregate([ | ||
| + | {$match:{continent:'Asia'}}, | ||
{$project:{ | {$project:{ | ||
_id:0, | _id:0, | ||
| Line 40: | Line 42: | ||
density: {$divide: ["$population","$area"]} | density: {$divide: ["$population","$area"]} | ||
}}, | }}, | ||
| − | {$match:{ | + | ]) |
| − | density: {$ | + | </pre> |
| + | <div class=ans> | ||
| + | db.world.aggregate([ | ||
| + | {$match:{continent:'South America'}}, | ||
| + | {$project:{ | ||
| + | _id:0, | ||
| + | name:1, | ||
| + | density: {$divide: ["$population","$area"]} | ||
}} | }} | ||
| + | ]) | ||
| + | </div> | ||
| + | </div> | ||
| + | |||
| + | ==Population Density in Europe== | ||
| + | <div class=q data-lang="mongo"> | ||
| + | Give the <code>name</code> and the <code>population density</code> of all countries in Europe. | ||
| + | <p>Note that because Vatican City (with area 0) is in Europe you will get a divide by zero error unless you filter first. | ||
| + | </p> | ||
| + | <div class="hint" title="Division by 0 error?"> | ||
| + | Use a <code>$match</code>. <code>{area:{"$ne":0}}</code> | ||
| + | </div> | ||
| + | <pre class=def> | ||
| + | db.world.aggregate([ | ||
| + | {$match:{continent:'Asia'}}, | ||
| + | {$project:{ | ||
| + | _id:0, | ||
| + | name:1, | ||
| + | density: {$divide: ["$population","$area"]} | ||
| + | }}, | ||
]) | ]) | ||
</pre> | </pre> | ||
<div class=ans> | <div class=ans> | ||
| − | db.world.aggregate([{ | + | db.world.aggregate([ |
| + | {$match:{continent:'Europe'}}, | ||
| + | {"area":{"$ne":0}}, | ||
| + | {$project:{ | ||
| + | _id:0, | ||
| + | name:1, | ||
| + | density: {$divide: ["$population","$area"]} | ||
| + | }} | ||
| + | ]) | ||
</div> | </div> | ||
</div> | </div> | ||
| + | ==Population in millions== | ||
<div class=q data-lang="mongo"> | <div class=q data-lang="mongo"> | ||
Show the <code>name</code> and <code>population</code> in millions for the countries of the continent <b>South America</b>. Divide the population by 1000000 to get population in millions. | Show the <code>name</code> and <code>population</code> in millions for the countries of the continent <b>South America</b>. Divide the population by 1000000 to get population in millions. | ||
Revision as of 13:48, 22 July 2016
Contents
Country Profile
For these questions you should use aggregate([]) on the collection world
You may find these AGGREGATE examples useful.
Per Capita GDP
Give the name and the per capita GDP for those countries with a population of at least 200 million.
per capita GDP is the GDP divided by the population.
db.world.aggregate([
{$match:{
population:{$gte:250000000}
}},
{$project:{
_id:0,
name:1,
"per capita GDP": {$divide: ['$gdp',1000000]}
}}
])
Population Density in South America
Give the name and the population density of all countries in South America.
population density is the population divided by the area
Use a $match. {"area":{"$ne":0}}
db.world.aggregate([
{$match:{continent:'Asia'}},
{$project:{
_id:0,
name:1,
density: {$divide: ["$population","$area"]}
}},
])
db.world.aggregate([
{$match:{continent:'South America'}},
{$project:{
_id:0,
name:1,
density: {$divide: ["$population","$area"]}
}}
])
Population Density in Europe
Give the name and the population density of all countries in Europe.
Note that because Vatican City (with area 0) is in Europe you will get a divide by zero error unless you filter first.
Use a $match. {area:{"$ne":0}}
db.world.aggregate([
{$match:{continent:'Asia'}},
{$project:{
_id:0,
name:1,
density: {$divide: ["$population","$area"]}
}},
])
db.world.aggregate([
{$match:{continent:'Europe'}},
{"area":{"$ne":0}},
{$project:{
_id:0,
name:1,
density: {$divide: ["$population","$area"]}
}}
])
Population in millions
Show the name and population in millions for the countries of the continent South America. Divide the population by 1000000 to get population in millions.
db.world.aggregate([
{$match:{
}},
{$project:{
_id:0,
name:1
}}
])
db.world.aggregate([{"$match":{"continent":{"$eq":"South America"}}},{"$project":{"_id":0,"name":1,"population":{"$divide":["$population",1000000]}}}])
Show the name and population density for France, Germany, and Italy
db.world.aggregate([
{$match:{
name: {$in:['United Kingdom','United States','Brazil']},
population: {$ne: null},
area: {$ne: 0}
}},
{$project:{
_id:0,
name:1
}}
])
db.world.aggregate([{"$match":{"name":{"$in":['France','Germany','Italy']},"population":{"$ne":null},"area":{"$ne":0}}},{"$project":{"_id":0,"name":1,"population density":{"$divide":["$population","$area"]}}}])
Order the continents by area from most to least.
db.world.aggregate([
{$group:{
_id:"$name",
area:{$max: "$area"}
}},
{$sort:{
area: -1
}},
{$project:{
_id:1,
area:1
}}
])
db.world.aggregate([
{"$group":{
"_id":"$continent",
"area":{"$sum": "$area"}
}},
{"$sort":{
"area": -1
}},
{"$project":{
"_id":1,
"area":1
}}
])
Harder Questions
Print a list of names for countries in the continent of "North America" change United States to USA
db.world.aggregate([
{$match:{
continent:"North America"
}},
{$project:{
_id:0,
name:1
}}
])
db.world.aggregate([{"$match":{"continent":"North America"}},{"$project":{"_id":0,"name":{"$cond": [{"$eq":["$name","United States"]},"USA","$name"]}}}])
Combine North America and South America to America, and then list the continents by area. Biggest first.
db.world.aggregate([
{$group:{
_id:{
$cond: [{$eq:["$continent","North America"]},"America",
{$cond: [{$eq:["$continent","Asia"]},"The East","$continent"]}]
},
area:{$sum: "$area"}
}},
{$sort:{
area: -1
}},
{$project:{
_id:1,
area:1
}}
])
db.world.aggregate([{"$group":{"_id":{"$cond":[{"$eq":["$continent","South America"]},"America",{"$cond":[{"$eq":["$continent","North America"]},"America","$continent"]}]},"area":{"$sum":"$area"}}},{"$sort":{"area":-1}},{"$project":{"_id":1,"area":1}}])
Show the name and the continent for countries beginning with N - but replace the continent Oceania with Australasia.
db.world.aggregate([
{$match:{
name:{$regex:"^N"}
}},
{$project:{
_id:0,
name:1
}}
])
db.world.aggregate([{"$match":{"name":{"$regex":"^N"}}},{"$project":{"_id":0,"name":1,"continent":{"$cond":[{"$eq":["$continent","Oceania"]},"Australasia","$continent"]}}}])
Show the name and the continent but:
substitute Eurasia for Europe and Asia.
substitute America - for each country in North America or South America or Caribbean.
Only show countries beginning with A or B
If you're struggling you may want to experiment with $and,$or, etc.
db.world.aggregate([
{$match:{
name:{$regex:"^A|^B"}
}},
{$project:{
_id:0,
name:1,
continent: {
$cond: [{
$or:[
{$eq:["$continent","Europe"]},
{$eq:["$continent","Asia"]}
]},"Eurasia",{
$cond: [
{$or:[
{$eq:["$continent","North America"]},
{$eq:["$continent","South America"]},
{$eq:["$continent","Caribbean"]}
]},"America","$continent"]}
]}
}}
])
Put the continents right...
Oceania becomes Australasia
Countries in Eurasia and Turkey go to Europe/Asia
Caribbean islands starting with 'B' go to North America, other Caribbean islands go to South America
Show the name, the original continent and the new continent of all countries.
db.world.aggregate([
{"$project":{
"_id":0,
"name":1,
"original": "$continent",
"new": {
"$cond": [
{"$or":[
{"$eq":["$continent","Eurasia"]},
{"$eq":["$name","Turkey"]}
]},"Europe/Asia",{
"$cond":[
{"$eq":["$continent","Oceania"]},"Australasia",{
"$cond":[
{"$and":[
{"$eq":["$continent","Caribbean"]},
{"$eq":[{"$substr":["$name",0,1]}, "B"]}
]},"North America",{
"$cond":[
{"$and":[
{"$eq":["$continent","Caribbean"]},
{"$ne":[{"$substr":["$name",0,1]}, "B"]}
]},"South America","$continent"
]
}
]
}
]
}
]
}
}}
])