CS383: Lab 9 -- Mongo Queries

In this lab you will be writing a set of queries in Mongo using the mgoactor and mgofilm collections of the sakila database.

All documents in the mgoactor collection have at least the following fields:

_idthe mongo id
actorintthe id number given by sakila
first_nameStringfirst name (all caps)
last_nameStringlast name (all caps)
filmsarrayan array of embedded documents each of which has the following fields
categinteger
catnamestring
filmidinteger
filmnamestring

All documents in the mgofilm collection have at least the following fields:

_idthe mongo id
film_idintthe sakila id
titleStringfilm title
cat_idintthe sakila category id
cat_nameStringthe category name
descriptionStringdescription
release_yearintyear
language_idintsakila language id
languageStringthe name of the language
rental_rateDoubleprice to rent
lengthintminutes
actorsarrayan array of embedded documents each of which has teh following fields: aid: -- integer, first_name, last_name
Recall that the following are special keywords for Mongo queries (field names are from the mgoactor collection)
commandexplanationSample usage
$lt < db.mgoactor.findOne({actor:{$lt:5}})
$lte <= db.mgoactor.findOne({actor:{$lt:5}})
$gt > db.mgoactor.find({actor:{$gt:198}})
$gte >= db.mgoactor.find({"films.filmid":{"$gte":1990}})
$ne ≠ db.mgoactor.find({"actor":{$ne:"BOB"}}, {"films.filmid":0})
$exists a field exists db.spices.find( { saffron: { $exists: true } } )
find all items in the spices collection that have the field saffron
$in { field: { $in: [value1, value2, ...] } } db.mgoactor.find({"first_name":{$in:["BOB", "LUCILLE"]}}).count()
$nin db.mgoactor.find({"first_name":{$nin:["BOB", "LUCILLE"]}}).count()
$and
$or		 db.mgoactor.find({$or: [{first_name: "BOB"}, {first_name:"LUCILLE"}]}).count()
db.mgoactor.find({$and: [{first_name: "BOB"}, {first_name:"LUCILLE"}]}).count()
$regex match using a regular expression db.mgoactor.find({ first_name: {$regex:"^D.*"}}).count()

Queries using the MGO actor collection
  1. number of actors with first name BOB
  2. number of actors with first name BOB or PENELOPE
  3. actor with first name that starts with S and end with R show only first name
  4. actor with a Z in either first or last name
  5. first name of all actors in film with id 513
  6. same as previous, but only showing the name of the film
  7. actor whose first name has an E and has been in a film in category 16
Queries using the mgofilm collection
  1. first name of all actors in film 513 (and title of film)
  2. title of all films in category 2 that have an actor named GENE
  3. all movies in which actor 1 and actor 4 both appear
  4. title and length of all movies in which an actor with the last name starting with G appeared with an actor last name ending with OD
  5. title and first name of actor for all moves in which the film is in category 9 and has an actor with either PA or PE in their first name
  6. find the the first and last name of all actors who have been in a category 2 or category 3 movie with someone whose name does NOT start with PEN

What to hand in

Send the Mongo query commands -- not the results, just the commands -- to gtowell@brynmawr.edu.