Cookies help us deliver our services. By using our services, you agree to our use of cookies. More information

Difference between revisions of "AGGREGATE Tutorial"

From NoSQLZoo
Jump to: navigation, search
Line 188: Line 188:
 
         area:{$gt:25000000}
 
         area:{$gt:25000000}
 
     }}
 
     }}
 +
])
 +
</div>
 +
</div>
 +
 +
 +
==First and last country by continent==
 +
<div class=q data-lang="mongo">
 +
For each continent show the first and last country alphabetically like this:
 +
{ "_id" : "Africa", "from" : "Algeria", "to" : "Zimbabwe" }
 +
{ "_id" : "Asia", "from" : "Afghanistan", "to" : "Yemen" }
 +
{ "_id" : "Caribbean", "from" : "Antigua and Barbuda", "to" : "Trinidad and Tobago" }
 +
{ "_id" : "Eurasia", "from" : "Armenia", "to" : "Russia" }
 +
{ "_id" : "Europe", "from" : "Albania", "to" : "Vatican City" }
 +
{ "_id" : "North America", "from" : "Belize", "to" : "United States" }
 +
{ "_id" : "Oceania", "from" : "Australia", "to" : "Vanuatu" }
 +
{ "_id" : "South America", "from" : "Argentina", "to" : "Venezuela" }
 +
<pre class=def>
 +
db.world.aggregate([
 +
  {$group:{
 +
      _id:"$continent"
 +
  }},
 +
  {$sort:{
 +
      _id:1
 +
  }}
 +
])
 +
</pre>
 +
<div class=ans>
 +
db.world.aggregate([
 +
    {$sort:{name:1}},
 +
    {$group:{
 +
      _id:'$continent',
 +
      from:{$first:'$name'},
 +
      to:{$last:'$name'}
 +
      },
 +
    },
 +
    {$sort:{_id:1}}
 
])
 
])
 
</div>
 
</div>

Revision as of 18:38, 28 September 2016

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]}
    }}
])
db.world.aggregate([{"$match":{"population":{"$gte":200000000}}},{"$project":{"_id":0,"name":1,"per capita GDP": {"$divide": ["$gdp","$population"]}}}])

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 for "V"

Give the name and the population density of all countries with name after V in the alphabet.

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. {$match:{area:{"$ne":0}}} 

db.world.aggregate([
    {$match:{name:{$gt:'V'}}},
    {$project:{
        _id:0,
        name:1,
        area:1
    }},
])

db.world.aggregate([ 

   {$match:{name:{$gt:'V'}}},
   {$match:{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]}}}])

Population density

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"]}}}])

Continents by 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
       }}
   ])

Big Continents

Show the only two continents with total area greater than 25000000 

db.world.aggregate([
  {$match:{
      continent:"North America"
  }},
  {$project:{
      _id:0,
      name:1
  }}
])

db.world.aggregate([ 

   {$group:{
       _id:"$continent",
       area:{$sum: "$area"}
   }},
   {$sort:{
       area: -1
   }},
   {$match:{
       area:{$gt:25000000}
   }}

])


First and last country by continent

For each continent show the first and last country alphabetically like this: 

{ "_id" : "Africa", "from" : "Algeria", "to" : "Zimbabwe" }
{ "_id" : "Asia", "from" : "Afghanistan", "to" : "Yemen" }
{ "_id" : "Caribbean", "from" : "Antigua and Barbuda", "to" : "Trinidad and Tobago" }
{ "_id" : "Eurasia", "from" : "Armenia", "to" : "Russia" }
{ "_id" : "Europe", "from" : "Albania", "to" : "Vatican City" }
{ "_id" : "North America", "from" : "Belize", "to" : "United States" }
{ "_id" : "Oceania", "from" : "Australia", "to" : "Vanuatu" }
{ "_id" : "South America", "from" : "Argentina", "to" : "Venezuela" }

db.world.aggregate([
  {$group:{
      _id:"$continent"
  }},
  {$sort:{
      _id:1
  }}
])

db.world.aggregate([ 

   {$sort:{name:1}},
   {$group:{
      _id:'$continent',
      from:{$first:'$name'},
      to:{$last:'$name'}
      },
   },
   {$sort:{_id:1}}

])

Harder Questions

Messing with continent names

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}}])

Messing with continent names 2

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"]}}}])

Messing with continent names 3

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"]}
           ]}
   }}



])







Messing with continent names 4




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"
                                       ]
                                   }
                               ]
                           }
                       ]
                  }
               ]
           }
       }}
   ])









					

						Retrieved from ‘http://nosqlzoo.net/mw/index.php?title=AGGREGATE_Tutorial&oldid=902’