Gestion et analyse des données — Tutorial 6

Queries in MongoDB

In this tutorial you’ll learn to write basic and advanced queries in MongoDB.

Get the data

Download the this archive file and extract it. You’ll find a file for each collection to import into the database.

Open MongoDB Compass, create a new database named sakila and create the different collections while importing the data.

Basic queries

Exercise

Exercise 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.

Solution

db.customer.find()
db.customer.find({}, {email:1})
db.customer.find({"store.country": "Canada"}, {email:1});
db.rental.find({"customer.country": "Iran", amount: {$gt: 10}}, {rental_id: 1, _id:0});
db.film.find({film_id: 213}, {"actors.first_name":1, "actors.last_name": 1}).sort({"actors.last_name": -1});

Operations on arrays

Exercise

Exercise 2 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.

Solution

db.film.find({special_features : {$elemMatch: {$eq: "Behind the Scenes"}}}, {film_id: 1, _id:0});
db.film.find({special_features : {$all: ["Commentaries", "Deleted Scenes"]}}, {film_id: 1, _id:0});
db.film.find({"actors.first_name": "BURT", "actors.last_name": "POSEY"}, {film_id: 1, _id:0});
db.film.find({actors: {$size : 15}}, {film_id: 1, _id:0});

Aggregation framework

Exercise

Exercise 3 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”.

Solution

db.rental.aggregate({$match: {"customer.first_name": "TOMMY", "customer.last_name": "COLLAZO"}}, 
                    {$project: {"inventory.film.title": 1, _id:0}})
One can also express this query with the function find() 
db.rental.find({"customer.first_name": "TOMMY", "customer.last_name": "COLLAZO"}, {"inventory.film.title": 1, _id:0});

db.rental.aggregate({$group: {"_id": null, total_amount: {$sum: "$amount"}}})

db.film.aggregate({$project: {nb_actors: {$size: "$actors"}}})

If we don’t put the match condition, we get an error because for some films the field actors is not defined.

db.film.aggregate({$match: {actors: {$elemMatch: {$exists: true}}}}, 
                {$project: {film_id: 1, "nb_actors": {$size: "$actors"}}}, 
                {$sort: {nb_actors: -1}})

db.film.aggregate({$match: {actors: {$elemMatch: {$exists: true}}}}, 
                {$project: {film_id: 1, "nb_actors": {$size: "$actors"}}}, 
                {$group: {_id: null, avg_actors: {$avg: "$nb_actors"}}})

db.rental.aggregate({$group: {_id: "$customer.customer_id", count: {$sum: 1}}}, {$sort: {count: -1}})

db.rental.aggregate({$group: {_id: {cust_id: "$customer.customer_id", cust_first_name: "$customer.first_name", 
                                    cust_last_name: "$customer.last_name"}, count: {$sum: 1}}}, 
                            {$sort: {count: -1}}, {$limit :1})

db.rental.aggregate({\(match: {"inventory.film.categories.category": "Music"}}, {\)group: {_id: "\(customer.country", count: {\)sum: 1}}}, {\(sort:{count: -1}}, {\)limit: 1})

Join Operations

Exercise

Exercise 4 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.

Solution

db.rental.aggregate({$match: {"inventory.film.title": "ACE GOLDFINGER"}}, 
            {$lookup: {from: "film", localField: "inventory.film.film_id", foreignField:"film_id", as:"film"}}, 
            {$project: {"film.language": 1}}, {$limit : 1})

db.rental.aggregate({$match: {rental_id: 2}}, 
                    {$lookup: {from: "staff", localField: "staff.staff_id", 
                    foreignField:"staff_id", as:"staff_member"}}, {$project: {"staff_member": 1}})