6. Tutorial_MongoDB Query Language (MQL)

CPSC 571 – Design and Implementation of Database Systems CPSC 671 – Database Management Systems Winter 2023 Tutorial 06 - 30 March 2023

CONTENTS • Examples on • JSON Objects • MongoDB Query Language (MQL)

Consider the following database. ISBN is the key of Books and SIN is the key of Readers. Further, ReadsISBN is a foreign key in Readers. Same domain shows books which have the same domain by linking their ISBN numbers. Readers SIN Name Gender ReadsISBN 612304568 Adnan Male 1234087443 682908123 Steve Male 1256813078 653511678 Jackie Female 1279538713 624115233 Nancy Female 1302264348 694718788 Tina Female 1324989983 665322343 Lama Female 1256813078 635925898 Steiman Male 1324989983 SameDomain ISBN1 ISBN2 1234087443 1256813078 1234087443 1279538713 1256813078 1279538713 Books ISBN Title FirstAuthor SecondAuthor Publisher YearPublished 1234087443 Data Processing Alex Mark ACM 2003 1256813078 Big Data Analysis Ken Lama IEEE 2013 1279538713 Data Management Mary Nick ACM 2011 1302264348 Data Collection Josh Tamer Springer 1985 1324989983 Computer Architecture Ali Steiman Springer 2013 1347715618 Financial Analysis Adams Mike ACM 2015 1370441253 Systems Biology Adnan Jalal IEEE 1999

JSON Objects • Write the JSON objects which correspond to the rows of these tables. You should have at least 2 objects in nested format and at least 2 objects in flat format. Books ISBN Title FirstAuthor SecondAuthor Publisher YearPublished 1234087443 Data Processing Alex Mark ACM 2003 1256813078 Big Data Analysis Ken Lama IEEE 2013 1279538713 Data Management Mary Nick ACM 2011 1302264348 Data Collection Josh Tamer Springer 1985 1324989983 Computer Architecture Ali Steiman Springer 2013 1347715618 Financial Analysis Adams Mike ACM 2015 1370441253 Systems Biology Adnan Jalal IEEE 1999 Books = [ { “ISBN”: 1234087443, “Title”: “Data Processing”, “ FirstAuthor ”: “Alex”, “ SecondAuthor ”: “Mark”, “Publisher”: “ACM”, “ YearPublished ”: “2003” }, { “ISBN”: 1256813078, “Title”: “ Big Data Analysis ”, “ FirstAuthor ”: “Ken”, “ SecondAuthor ”: “Lama”, “Publisher”: “IEEE”, “ YearPublished ”: “2013” }, …… ]

Readers = [ { “SIN” : “ 612304568 ”, “Name” : “Adnan”, “Gender” : “Male”, ReadsISBN: { “Title”: “Data Processing”, “ FirstAuthor ”: “Alex”, “ SecondAuthor ”: “Mark”, “Publisher”: “ACM”, “ YearPublished ”: “2003” } }, { ….. } ] Readers SIN Name Gender ReadsISBN 612304568 Adnan Male 1234087443 682908123 Steve Male 1256813078 653511678 Jackie Female 1279538713 624115233 Nancy Female 1302264348 694718788 Tina Female 1324989983 665322343 Lama Female 1256813078 635925898 Steiman Male 1324989983 “Embedded/Nested” Technique

SameDomain = [ { “ISBN1”: “ 1234087443 ”, “ISBN2”: “ 1256813078 ” }, { ….. } ] SameDomain ISBN1 ISBN2 1234087443 1256813078 1234087443 1279538713 1256813078 1279538713 “Linking by Keys/Referenced” Technique

MongoDB Query Language (MQL)

Assume you have a Suppliers database stored in mongoDB. • Solve the following queries using MQL 1. Write a MongoDB query to display all the documents in the collection Suppliers. Solution: db.Suppliers.find();

4. Write a MongoDB query to display the fields Supplier_id , name , Location and zip code , but exclude the field _id for all the documents in the collection Supplier. Solution: db.Suppliers.find({},{"Supplier_id" : 1, "name":1, "Location":1, "address.zipcode":1, "_id":0}); 5. Write a MongoDB query to display all the Supplier which is in the Location Calgary . Solution: db.Suppliers.find({"Location": "Calgary"});

6. Write a MongoDB query to display the first 5 Suppliers which is in the Location Calgary . Solution: db.Suppliers.find({"Location": "Calgary"}).limit(5); 7.Write a MongoDB query to display the next 5 Suppliers after skipping first 5 which are in the Location Calgary . Solution: db.Suppliers.find({"Location": "Calgary"}).skip(5).limit(5);

10. Write a MongoDB query to find the Suppliers which locate in latitude value less than -95.754168 . Solution: db.Suppliers.find({"address.coord" : {$lt : -95.754168}}); 11. Write a MongoDB query to find the Suppliers that do not prepare any Capacity of 'Water tank' and their grade score more than 70 and latitude less than -65.754168 . Solution: db.Suppliers.find( {$and: [ {“Capacity” : {$ne :"Water tank "}}, {"grades.score" : {$gt : 70}}, {"address.coord" : {$lt : -65.754168}} ] } );

12. Write a MongoDB query to find the Suppliers which do not prepare any Capacity of 'Water tank' and achieved a score more than 70 and located in the longitude less than -65.754168 . Note : Do this query without using $and operator. Solution: db.Suppliers.find( { “Capacity” : {$ne : "Water tank "}, "grades.score" :{$gt: 70}, "address.coord" : {$lt : -65.754168} } ); 13. Write a MongoDB query to find the Suppliers which do not prepare any Capacity of 'Water tank ' and achieved a grade point 'A ' not belongs to the Location Edmonton . The document must be displayed according to the cuisine in descending order . Solution: db.Suppliers.find( { “Capacity” : {$ne : "Water tank "}, "grades.grade" :"A", "Location": "Edmonton" } ).sort({"cuisine":-1});

15. Write a MongoDB query to find the Supplier Id, name, Location and Capacity for those Suppliers which contain 'ces' as last three letters for its name . Solution: db.Suppliers.find( {name: /ces$/}, { "Supplier_id" : 1, "name":1, "Location":1, “Capacity” :1 } );

16. Write a MongoDB query to find the Supplier Id, name, Location and Capacity for those Suppliers which contain 'Reg' as three letters somewhere in its name . Solution: db.Suppliers.find( {"name": /.*Reg.*/}, { "Supplier_id" : 1, "name":1, "Location":1, “Capacity” :1 } );