CSCI 305: Introduction to Database Systems

Activities on Mongodb IV

In this set of activities, you are to learn how to work with MongoDB to do projection, indexing, sorting, and aggregation. You are going to work with the two collections you created, Address and a Book.

Projection in MongoDB

Unlike SQL where the projection is done through the well-known SFW queries, MongoDB supports projection by using the same function find() with a second parameter. We've learned that if we don't give find() any parameter, the function will return the entire collection. If we give a parameter, find() will return the elements that meet the specified condition(s). For example,

db.books.find({"Pub Year": {$gt:2016}})

returns all attributes of the books that are published after 2016. If we want to return only the title of the books, we could project the output as follows.

db.books.find({"Pub Year": {$gt:2016}}, {"Title":1, _id:0})

which would return a list similar to the following.

{ "Title" : "Autumn" }
{ "Title" : "Exit West" }
{ "Title" : "Pachinko" }
{ "Title" : "The Power" }
{ "Title" : "Sing, Unburied, Sing" }
{ "Title" : "The Evolution of Beauty: How Darwins Forgotten Theory of Mate Choice Shapes the Animal World  and Us" }
{ "Title" : "Grant" }
{ "Title" : "Locking Up Our Own: Crime and Punishment in Black America" }
{ "Title" : "Prairie Fires: The American Dreams of Laura Ingalls Wilder" }
{ "Title" : "Priestdaddy" }

The projection in MongoDB works by providing the function find() a second parameter which specifies which attributes are included in the returned the elements, which ones are not. In the above query, we search for all books that are published after 2016, but we are only interested in the title. Thus in the parameter that specifies the return attributes, the attribute "Title" is true (1) and _id_id. So if we don't want it to be returned, we need to explicitly say so.

Similarly the following query will return all boook titles in the database.

db.books.find({}, {"Title":1, _id:0})

Sorting output

MongoDB supports sorting the results of a search. For example, given the Books collection, one can find all the books published after 2016 sorted by the title with the following query.

db.books.find({"Pub Year": {$gt:2016}}, {"Title":1, _id:0}).sort({"Title": -1})

{ "Title" : "The Power" }
{ "Title" : "The Evolution of Beauty: How Darwins Forgotten Theory of Mate Choice Shapes the Animal World  and Us" }
{ "Title" : "Sing, Unburied, Sing" }
{ "Title" : "Priestdaddy" }
{ "Title" : "Prairie Fires: The American Dreams of Laura Ingalls Wilder" }
{ "Title" : "Pachinko" }
{ "Title" : "Locking Up Our Own: Crime and Punishment in Black America" }
{ "Title" : "Grant" }
{ "Title" : "Exit West" }
{ "Title" : "Autumn" }

Note that the sort order is specified as -1 which is in descending order. A value of 1 will make the sort in ascending order.

Aggregate functions

MongoDB supports a set of aggregate functions. The following is an example how it works. For a complete list of functions, see this TutorialPoint website, or this MongoDB document website.

MongoDB supports a number of aggregation functions, including $sum, $avg, $min, $max, $push, $addToSet, $first, and $last. The general form of using an aggregation function is as follows.

db.collection.aggregate(AGGREGATE_OPERATION)

where AGGREGATE_OPERATION is one of the function name listed above. Here are two examples based on TutorialPoint's example.

Assume we add a price field to our book collection and we have the following sample books.

{
	"_id" : ObjectId("5ad9e2c094cecc08a56773d3"),
	"Title" : "Autumn",
	"Author Last Name" : "Smith",
	"Author First Name" : "Ali",
	"Translated or edited by" : "",
	"Publisher" : "Pantheon Books",
	"Pub Year" : 2017,
        "Price" : 10.97
}
{
	"_id" : ObjectId("5ad9e2c094cecc08a56773d4"),
	"Title" : "Exit West",
	"Author Last Name" : "Hamid",
	"Author First Name" : "Mohsin",
	"Translated or edited by" : "",
	"Publisher" : "Riverhead Books",
	"Pub Year" : 2017,
        "Price" : 10.40
}
{
	"_id" : ObjectId("5ad9e2c094cecc08a56773d5"),
	"Title" : "Pachinko",
	"Author Last Name" : "Lee",
	"Author First Name" : "Min Jin",
	"Translated or edited by" : "",
	"Publisher" : "Grand Central Publishing",
	"Pub Year" : 2017,
        "Price" : 10.87
}

The following aggregation finds all books published in 2017.

db.books.aggregate([{"$match":{"Pub Year":2017}}]).pretty()

The following aggregation finds all books priced > 10.50.

db.books.aggregate([{"$match":{"Price":{"$gt":10.50}}}])

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, only listing the author's last name and the book title.
  2. List all books sorted by the author's last name.
  3. List all books sorted by publishing year.
  4. Find all restaurants sorted by zipcode.

If you have time, go through the exercises listed on this CodeProject website. In particular, do the match, group, and count.

Submission

Submit the commands and the results as a text file.

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.
  12. Good examples of aggregation functions. Accessed 2018-04-25.