CSCI 305: Introduction to Database Systems

Activities on Mongodb III

In this set of activities, you are to learn how to issue various queries in MongoDB and how to use Python programs to execute various MongoDB commands.

In our last activity, you loaded two collections to your MongoDB database, an Address collection and a Book collection. In this exercise, you will practice more on querying these collections and on how to use Python program to implement some of the MongoDB command line functionality.

Saving output into a text file

You are asked to save the output of operations into a file. This can help you keep a record what you have done. It can also make submission of your work easier. In general, saving output can be realized using output redirect. One is to use the following form if you are programming in Python.

python your-program.py > output.txt

However the above method won't work directly if you are within the MongoDB shell. To redirect the output of MongoDB shell, you can use the method discussed by this StackOverflow post as follows with revisions needed for authentication.

$ mongo --host eg-mongodb.bucknell.edu  -u username -p --authenticationDatabase  dbname dbname << EOF > output.json
password
db.collection.find().pretty()
EOF

Note that the username and the password should be as we discussed in class. The double < characters indicates that the terminal input will end when the characters EOF is seen as the input on its own line. This is a standard way of redirecting Linux input and output.

Another way of saving output is simply using copy-and-paste. You can try this as well. The main draw-back of this method is it is hard to select all contents when the amount of output is large.

The last method is to use the Linux script command. Consult a Linux manual page to see how this works.

You are asked to use any of the above method (or anything else you can think of) to save your output for submission.

MongoDB queries

In MongoDB, queries are done through the find() function. MongoDB supports common logic and relation operations. Here is a list of logical operators and their comparison to SQL based to TutorialPoint, applied to the Books collection you loaded in our last exercise.

Operation Syntax Example SQL equivalent
Equality {<key>:<value>} db.books.find({"Title":"Exit West"}) select * from books where "Title" == 'Exit West'
Less than {<key>:{$lt:<value>}} db.books.find({"Pub Year":{$lt:2017}}) select * from books where "Pub Year" < 2017
Less than or equal to {<key>:{$le:<value>}} db.books.find({"Pub Year":{$le:2017}}) select * from books where "Pub Year" <= 2017
Greater than {<key>:{$gt:<value>}} db.books.find({"Pub Year":{$gt:2017}}) select * from books where "Pub Year" > 2017
Greater than or equal to {<key>:{$ge:<value>}} db.books.find({"Pub Year":{$ge:2017}}) select * from books where "Pub Year" >= 2017
Not equal to {<key>:{$ne:<value>}} db.books.find({"Pub Year":{$ne:2017}}) select * from books where "Pub Year" != 2017

Table 1: Logical operations in MongoDB

Note that in MongoDB when the comparison is other than equal, the operation is expressed as a nested key:value element.

The relational operations supported by MongoDB are listed in the following table.

Operation Syntax Example SQL equivalent
AND $and: [
{<key1>:<value1>}, {<key2>:<value2>}
]		 db.books.find({$and: [{"Title":"Exit West"}, {"Pub Year" : 2017}]}) select * from books where "Title" == 'Exit West' and "Pub Year" == 2017
Or $or: [
{<key1>:<value1>}, {<key2>:<value2>}
]		 db.books.find({$or: [{"Title":"Exit West"}, {"Pub Year" : 2017}]}) select * from books where "Title" == 'Exit West' or "Pub Year" == 2017

Table 2: Relational operations in MongoDB

As any programming languages or databases languages, MongoDB supports complex relational and logical operations. For example, if one wants to find all books that are a) published after 2016; and b) authored by "Smith" or titled "Exit West", a MongoDB query would look like as follows.

db.books.find({$and: [{"Pub Year":{$gt:2016}}, {$or:[{"Author Last Name":"Smith"}, {"Title":"Exit West"}]}]})

An equivalent SQL for the above query would look like as follows.

select * from books
where
"Pub Year" > 2016 and ("Author Last Name" == 'Smith' or "Title" == 'Exit West')

As one can see, MongoDB logical and relational operations are not as expressive as those in SQL or many other higher level programming languages.

In addition, MongoDB supports searching by wild card through regular express, similar to the like phrase in SQL. The following examples from StackOverflow shows how wild card search is used

MongoDB shell supports pretty-printing that will make the output a bit more readable. For example, instead of simply using the command to search as in the following,

db.books.find({"Title":/m/})

you can try the following to make the output look nice.

db.books.find({"Title":/m/}).pretty()

Your query exercises

Complete the following queries using relational and logical operations in MongoDB shell. All searches are case-insensitive.

  1. Find all books published after 2016.
  2. Find all books a) published after 2016 and b) authored by "Smith" or titled "Exit West".
  3. Find all books of which the author's last name contains the letter "m".
  4. Find all restaurants that are a) bakery or b) any in the zip code area that contains "112", e.g., "11225", "11218", or any whose name contains letter "m".

Updating MongoDB databases

In our last exercise, we learned how to load data into MongoDB in bulk using a Python program. In this exercise, you will learn how to update or add more information to an existing MongoDB

Exercise: Adding a single document to an existing database

MongoDB shell allows various common operations, including insert new documents into an existing database. Read this example from MongoDB website first. Understand how it works, then execute an insertion of a new document at the MongoDB shell.

Exercise: Adding bulk data to an existing database

This Python example from last exercise demonstrate how to insert a collection of entries into a MongoDB database. Use the example to insert a collection of more Address entries into the same Address database you created last time. Here is the raw data. Insert all new entries into your existing Address database.

Exercise: Updating an existing document

MongoDB document does its updating by searching for the document first, then updating the document. The following example is quoted from MongoDB official website

db.people.update(
   { name: "Andy" },
   {
      name: "Andy",
      rating: 1,
      score: 1
   },
   { upsert: true }
)

The above operation assumes a people collection and tries to update the document whose name field is "Andy" to have the new values. The field {upsert:true} indicates that exactly one update operation will succeed if multiple requests from different clients are received. In addition, when updating a document, the complete new document must be listed as seen in the example. If only a partial information is presented, e.g., 

db.people.update(
   { name: "Andy" },
   {
      name: "Andy",
      score: 1
   },
   { upsert: true }
)

then the new document will not have the filed of rating any more.

Following the above pattern, update the document of James Comey's book to have a complete title, A Higher Loyalty: Truth, Lies, and Leadership.

Extra: using Python programs to do all the work

If you have time, study this example Python program. You can actually do all the above work using Python directly. You don't need to submit this part of the work.

Submission

Submit the revised program pymongo-load-db.py as well as the output of all operations in this set of exercises.

References

  1. AskLIT. Accessed 2018-04-18.
  2. MongoDB tutorial by TutorialPoint. Accessed 2018-04-18.
  3. MongoDB query by TutorialPoint. Accessed 2018-04-22.
  4. MongoDB commonly-used command list
  5. An Introduction to JSON by DigitalOcean. Accessed 2018-04-19.
  6. A full list of MongoDB commands by MongoDB. Accessed 2018-04-20.
  7. https://docs.mongodb.com/manual/tutorial/insert-documents/ by MongoDB. Accessed 2018-04-23.
  8. read_json.py A Python example program to read and print JSON objects.
  9. https://www.csvjson.com/csv2json Online tool to convert a CSV file into JSON. Accessed 2018-04-16.
  10. https://stackoverflow.com/questions/3305561/how-to-query-mongodb-with-like Query MongoDB using wild card. Accessed 2018-04-22.
  11. Redirecting MongoDB input/output. Accessed 2018-04-23.