Big Data for IA — Tutorial 6

MongoDB - Queries

We learn how to write queries in MongoDB on the Sakila database.

1 Initialization

  • Launch a MongoDB server on your computer.

  • Open MongoDB Compass.

  • Connect to the MongoDB server with the following URI: mongodb://localhost:27017.

  • Download the this archive file and extract it. Each file corresponds to a collection.

  • In MongoDB Compass create a new database named sakila.

  • Create the collections customer, film, rental, staff and store.

  • Import the data from the downloaded JSON files.

2 Basic queries

We might want to see a list of common operators in this page.

Exercise

Exercise 2.1 Write the following queries in MongoDB:

  1. Return all the information on all customers.

  2. Return the email of all customers.

  3. Return the email of the customers of Canadian stores.

  4. Return the identifier of all rentals made by customers from Iran, where the amount paid is strictly greater than 10 dollars.

  5. Return the first and last names of the actors who played a role in film 213.

3 Operations on arrays

Useful array operators are listed here.

Exercise

Exercise 3.1 Write the following queries in MongoDB:

  1. Return the identifier of the films that have “Behind the Scenes” as special features.

  2. Return the identifier of the films that have as special features all of the following: “Commentaries” and “Deleted Scenes”.

  3. Return the identifier of all the films where BURT POSEY played a role.

  4. Return the identifier of the film that has exactly 15 actors.

4 Aggregation framework

A useful reference for the aggregation pipeline can be found here here.

Exercise

Exercise 4.1 Write the following queries in MongoDB using the aggregation framework:

  1. Return the title of the films rented by TOMMY COLLAZO (can you also express this query with the function find()?)

  2. Count the total amount of payments across all rentals.

  3. Return the number of actors of each film.

  4. Sort the films by the number of actors (decreasing order).

  5. Return the average number of actors for each film.

  6. Return the identifier of the customer who made the most of rentals.

  7. Return the first and last name of the customer who made the most of rentals.

  8. Return the country where the customers have rented the most of the films in the category “Music”.

5 Join Operations

The join operation is explained here.

Exercise

Exercise 5.1 Write the following queries in MongoDB using the aggregation framework:

  1. Return the language of the film with title “ACE GOLDFINGER”.

  2. Return all the information about the staff member who took care of rental 2.