MongoDB_Commands

/*
Exercise 1:
  a) Start the mongod service so that you have a running database .
  b) Open Robo 3T so that we can better visualize what is happening.
  c) Connect to your DB, just accept the defaults and set whatever connection
      name you would like your connection to have, this name does not impact
      the database.
  d) Open a new mongo shell from the command prompt and run the following
      commands to get familiar with MongoDB.
  e) Examine Robo 3T along the way to better visualize what is happening.
*/

// Show all available databases
show dbs

// Verify what DB you are currently using (shoudl be 'test', this is the default)
// this database will also NOT show up in Robo 3T
db

// Use a different database, will create a new DB on execution if one does
// not already exist.
use ourFirstDB
use ourSecondDB
use stockData
use cricketData

// Show all available databases and verify they were created in Robo 3T
show dbs

________________________________________________________________________________

/*
Exercise 2:
  a) Using Robo 3T, right-click on the connection name and select new database.
  b) Create two databases using this GUI method.
  c) Name these databases anything you'd like, and then verify their existence
      using the mongo shell (associates should be able to complete this without
      being instructor led, if they need help the command is 'show dbs').
  d) You should notice that through the Mongo shell there will be no recollection
      that these new databases have been created.  For them to show up in the
      shell, a collection must firt be made inside of them.  We will do this in
      the next exercise. (This is true for ALL databases with Mongo, not just
      those made using Robo 3T)
*/

________________________________________________________________________________

/*
Exercise 3:
  a) Using Robo 3T, expand one of the GUI created databases and right-click on
      the 'collections' folder.
  b) From the drop down menu that appears, select 'create collection'.  Name
      this collection anything you want.
  c) Right-click this new collection and select 'insert document'.  A window
      will pop up, write the following JSON in this window: */
      {
        fname: 'john',
        lname: 'doe',
        salary: 70000,
        departments: ['sales', 'admins'],
        hiredate: '2018-08-29'
      }

/*    use the 'validate' button to ensure it is formatted properly. Once
      properly formatted, click 'save'.
  d) Using the mongo shell use the following commands to view this collection.
*/

// Change to the proper database
use myGUIDbName

// View the recently created collection
db.getCollection("whateverYourCollectionNameIs")

// View the contents of that collection
db.getCollection("whateverYourCollectionNameIs").find()

// A more structured output of the contents of that collection
db.getCollection("whateverYourCollectionNameIs").find().pretty()

/*
  e) From the Mongo shell switch to the other GUI created db by using the 'use'
      command.
  f) We will now create a collection in this database using the mongo shell.
      use the following commands to accomplish this task:
*/

use mySecongGUIDbName

// create a new collection in this database.
db.createCollection('mycollection')

// add a document to this new collection.
db.mycollection.insert({fname: "Jane", lname: 'Doe', salary: 90000,
departments: ['admin', 'IT', 'Data Engineers'], hiredate: '2018-08-29'})

// ensure the document was successfully placed into the collection.
db.getCollection("mycollection").find().pretty()

/*
  g) Using Robo 3T verify that all collections and documents have been added
      successfully.
  h) From the Mongo shell, you can use 'show collections' to view a list of
      collections that are availble in the database.
*/

________________________________________________________________________________

/*
Exercise 4:
  a) Databases can also be dropped once they have been created.  The following
      two methods describe how to drop a database.
  b) In Robo 3T, right-click on the DB name and select 'drop database'.
  c) From the mongo shell use the following to perform a drop.  The drop
      affects the databse that you are currently working in.
*/

// switch to the database you wish to drop
use myRemaingGUIDatabse

// drop the database
db.dropDatabase()

// verify the databse has been dropped
show dbs

________________________________________________________________________________

/*
Exercise 5:
  a) Dropping collections is very similar.  The following steps are designed to
      work from the Mongo shell, although all actions can be performed in
      Robo 3T.
*/

// create a new databse named myDb
use myDb

// create a collection and load a document into it.
db.createCollection('col1')
db.col1.insertOnce({x: 1, y: true, stuff: 40, morestuff: 'strings and stuff'})

// verify collection exists
show collections

// notice how this time we didn't use getCollection(), both do the same job.
db.col1.find()
db.col1.find().pretty()

// drop the collection named col1
db.col1.drop()

// verify only the collection was dropped, and not the entire database
show collections
show dbs

// remove the current database
db.dropDatabase()

________________________________________________________________________________

/*
Exercise 6:
  a) We can also query documents in a more specific manner than just using find().
  b) Follow along to create a new database, add collections, load multiple
      documents at once, and then query those documents in a more complex manner.
*/

// create a new database
use employees

// add an employees collection
db.createCollection('emps')

// add a three documents with varying complexity to the collection.
// the second document has a nested document inside of it named 'previouspositions'
db.emps.insert([{
    fname: 'john',
    lname: 'doe',
    groups: ['brokers', 'bonds', 'stocks', 'mutal funds'],
    salary: 75000,
    role: 'broker',
    previouspositions: null
  },
  {
    fname: 'jane',
    lname: 'doe',
    groups: ['managers', 'IT', 'operations'],
    role: 'manager',
    salary: 173000,
    previouspositions: [
      {
        previousrole: "IT operations",
        previoussalary: 90000
      },
      {
        previousrole: "IT help desk",
        previoussalary: 65000
      }
    ]
  },
  {
    fname: 'john',
    lname: 'smith',
    groups: ["managers", 'brokers', 'operations'],
    role: 'manager',
    salary: 150000,
    previouspositions: null
}])

// Query the docuemnt for specific items
db.emps.find().pretty()

// where a 'key' is equal to a 'value'
db.emps.find({role:"manager"}).pretty()
db.emps.find({lname:'doe'}).pretty()

// where a 'key' is greater than a 'value'
db.emps.find({salary:{$gt:150000}}).pretty()

// where a 'key' is greater than or equal to a 'value'
db.emps.find({salary:{$gte:150000}}).pretty()

// where a 'key' is less than or equal to a 'value'
db.emps.find({salary:{{$lte: 150000}}}).pretty()

// where a two 'keys' are eqaul to two 'values' (and)
db.emps.find({$and:[{role:'manager'}, {previouspositions:null}]}).pretty()

// other operators include or {$or: []} not equal {$ne: } less than {$lt: }

// filtering by a specific value is also possible
db.emps.find({},{_id:0, "fname":1, "lname":1}).pretty()

// limit the number of documents returned by adding limit()
db.emps.find().limit(2).pretty()

// combine limit() and skip() to filter queries
db.emps.find().limit(2).skip(1).pretty()

// sort results in ascending order by using sort() with 1 as an argument
db.mycol.find({},{"lname":1}).sort({"lname":1})

// sort results in descending order by using sort() with -1
db.mycol.find({},{"lname":1}).sort({"lname":-1})

________________________________________________________________________________

/*
Exercise 7:
  a) Updating documents once they are added to a collection is also possible
  b) Updating takes place in two ways, either ammending an existing docuemnt,
      or by completely overwriting the docuemnt.
  c)  Use the Mongo shell to accomplish the following.
*/

// update a field in an existing docuement
// change the FIRST occurance of "role":"broker" to "role":"sales"
db.emps.update({"role":"broker"}, {$set:{"role":"sales"}})
db.emps.find().pretty()

// what if there are multiple documents that share the same field information
// let's try to run the following update.  There are two docuemnts that This
// update could apply to, let's see which is affected.
db.emps.update({"role":"manager"}, {$set:{"role":"leader"}})
db.emps.find().pretty()

// change multiple docuemnts in a colleciton by adding multi:true
db.emps.update({"previouspositions":null},{$set:{"previouspositions":"empty"}}, {multi:true})
db.emps.find().pretty()

// completely overwrite a specific document in a collection
db.emps.save({"_id":ObjectId("your_hex_id"), "fname":"Clark", "lname":"Kent"})
db.emps.find().pretty()

________________________________________________________________________________

/*
Exercise 8:
  a) Data can also be imported from external files.
  b) Exit the mongo shell, or open another command prompt and nagivate to the
      directory that contains the .json and .csv data files.
  c) Use the Mongo import command to load these files into Mongo.
  d) You can check to see this visually in Robo 3T.
*/

// load people.json into an EXISTING database, while creating a new Collection
mongoimport -d myExistingDatabase -c myNewCollectionName --file people.json

// verify the data has been imported properly
use myExistingDatabase
show collections
db.myNewCollectionName.find().pretty()

// load people.json into a NEW database and create a collection at the same time
mongoimport -d myNewDatabase -c myNewCollectionName --file people.json

// verify the data has been imoorted properly
use myNewDatabase
show collections
db.myNewCollectionName.find().pretty()

// import a .csv into a new database and new collection
mongoimport -d cricketplayers -c players --type csv --headerline --file player.csv
use cricketplayers
show collections
db.players.find.pretty()

// We have a lot of data in multiple databases now, using count() we can see
// just how many documents per collection we have.
// using the Mongo shell lets count cricket players
use cricketplayers
db.players.count()

________________________________________________________________________________

/*
Exercise 9:
  a) Removing documents from a collection.
  b) use the database that was created in the previous exercise by importing
      the people.json file.
  c) Removing documents from a collection happens in two ways.
    1) All docuemnts that match the criteria are removed.
    2) Only one docuemtn that matches the criteria is removed.
  d) We will look at both, from the mongo shell use the following.
*/

// use the proper database and view the documents in the collection
use myDbContainingPeople
show collections
db.collectionname.find().pretty()

// let's delete all the managers
db.collectionname.remove({"role":"manager"})
db.collectionname.find().pretty()

// by adding a parameter we can delete just one document in a collection, even
// if more than once document matches the criteria.
db.collectionname.remove({"role":"team lead"},1)
db.collectionname.find().pretty()

// be careful when using remove.  if no criteria is specified then Mongo deletes
// everything in the collection
db.collectionname.remove()
db.collectionname.find().pretty()

________________________________________________________________________________
/*
Exercise 10:
  a) Using the Mongo shell we are going to take a look at aggregate functions.
  b) use the cricketplayers database for the following exercises.
*/

// count players by batting hand
db.players.aggregate([{$group : {_id : "$Batting_Hand", player_count : {$sum: 1}}}])

// count players by batting hand with no null values
db.players.aggregate([{$match: {Batting_Hand:{$ne: "NULL"}}},{$group : {_id : "$Batting_Hand", Number_per : {$sum: 1}}}])

// count number of players per country
db.players.aggregate([{$group : {_id : "$Country", Number_per : {$sum: 1}}}])

// count number of players per country with no null values
db.players.aggregate([{$match: {Country:{$ne: "NULL"}}},{$group : {_id : "$Country", Number_per : {$sum: 1}}}])

// count number of player of each country that bat with a given hand.
db.players.aggregate([{$group:{_id:{"Country":"$Country", "Batting_Hand":"$Batting_Hand"}, numberper:{$sum:1}}}])

// count number of players of each country that bar with a given hand and sort alphabetically by country
db.players.aggregate([{$group:{_id:{"Country":"$Country", "Batting_Hand":"$Batting_Hand"}, numberper:{$sum:1}}}, {$sort:{_id:1}}])

// count number of players of each country that bar with a given hand and sort alphabetically
// by country and remove null values
db.players.aggregate([{
  $match:{Batting_Hand:{$ne:"NULL"}}},
  {$group:{_id:{"Country":"$Country", "Batting_Hand":"$Batting_Hand"}, numberper:{$sum:1}}},
  {$sort:{_id:1}}])
________________________________________________________________________________

/*
Mini Project 1:

Inside the 'datasets' folder you will find multiple sets of data, in multiple
formats.  Look through this data and design database names and collection names
that will make sense for datasets.

Use what you have learned to migrate this data into Mongo using the design you
created above.  Be prepared to discuss why you chose your design and demonstrate
that your data was properly imported.

Create 5 queries per database that will show some things that your group finds
interesting about the data.  No... seriously... whatever you find interesting,
write queries and show us.  Be prepared to talk about why you found it interesting
and how your query relates to that interest.
*/

________________________________________________________________________________