1 / 85

COMP4332/RMBI4310

COMP4332/RMBI4310. NoSQL (Part 2). Prepared by Raymond Wong Presented by Raymond Wong. Outline. NoSQL (Previous Examples) NoSQL (New Examples). 1. NoSQL (Previous Examples). We focus on Version 1 for illustration of different queries. 1. NoSQL (Previous Examples).

tonyareid
Download Presentation

COMP4332/RMBI4310

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. COMP4332/RMBI4310 NoSQL (Part 2) Prepared by Raymond Wong Presented by Raymond Wong

  2. Outline • NoSQL (Previous Examples) • NoSQL (New Examples)

  3. 1. NoSQL (Previous Examples) • We focus on Version 1 for illustration of different queries

  4. 1. NoSQL (Previous Examples) • Query 1: Show the student IDs of all students cname cid sid byear teacher sname take student course student (sid, sname, byear)course (cid, cname, teacher)take (sid, cid) NoSQL db.student.find({}, {sid:1, _id:0}) Output { sid : "12345678" } { sid : "87654321" } { sid : "12341234" } { sid : "56785678" } { sid : "88888888" }

  5. 1. NoSQL (Previous Examples) • Query 2: Find the student IDs of all students whose birth years are 1998 cname cid sid byear teacher sname take student course student (sid, sname, byear)course (cid, cname, teacher)take (sid, cid) This example shows an “equality” operation. NoSQL db.student.find({byear:1998}, {sid:1, _id:0}) Output { sid : "12345678" } { sid : "56785678" } { sid : "88888888" }

  6. 1. NoSQL (Previous Examples) = NoSQL db.student.find({byear:1998}, {sid:1, _id:0}) < NoSQL db.student.find({byear:{$lt:1998}}, {sid:1, _id:0}) NoSQL db.student.find({byear:{$lte:1998}}, {sid:1, _id:0}) <= > NoSQL db.student.find({byear:{$gt:1998}}, {sid:1, _id:0}) NoSQL db.student.find({byear:{$gte:1998}}, {sid:1, _id:0}) >= NoSQL db.student.find({byear:{$ne:1998}}, {sid:1, _id:0}) <>

  7. 1. NoSQL (Previous Examples) and NoSQL db.student.find({$and: [ {byear:{$gte: 1997}}, {byear:{$lte: 1998}}]}, {sid:1, _id:0}) or NoSQL db.student.find({$or: [ {byear:{$gte: 1997}}, {byear:{$lte: 1998}}]}, {sid:1, _id:0})

  8. 1. NoSQL (Previous Examples) • Query 3: Find the student IDs of all students whose birth years are 1998. Please show them in ascending order of student IDs cname cid sid byear teacher sname take student course student (sid, sname, byear)course (cid, cname, teacher)take (sid, cid) NoSQL db.student.find({byear:1998}, {sid:1, _id:0}).sort({sid:1}) Output { sid : "12345678" } { sid : "56785678" } { sid : "88888888" }

  9. 1. NoSQL (Previous Examples) • Query 4: Find the name and student IDs of all students whose birth years are 1998. Please show them in ascending order of names and then in descending order of student IDs cname cid sid byear teacher sname take student course student (sid, sname, byear)course (cid, cname, teacher)take (sid, cid) NoSQL db.student.find({byear:1998}, {sname:1, sid:1, _id:0}).sort({sname:1, sid:-1})

  10. 1. NoSQL (Previous Examples) Output { sid : "56785678", sname : "David Lee" } { sid : "12345678", sname : "Raymond" } { sid : "88888888", sname : "Test Test" } In the output, the order of showing the fields is (“sid”, “sname”) (not (“sname”, “sid”)). It could be regarded as “correct” since we do not require the order of showing the fields. Suppose that we require this order. What should we do?

  11. 1. NoSQL (Previous Examples) cname cid sid byear teacher sname take student course student (sid, sname, byear)course (cid, cname, teacher)take (sid, cid) • Query 4: Find the name and student IDs of all students whose birth years are 1998. Please show them in ascending order of names and then in descending order of student IDs NoSQL This version shows the order of showing the fields. db.student.aggregate([ {$match: { byear: 1998} }, {$sort: {sname:1, sid:-1} }, {$project: {_sname: "$sname", _sid: "$sid", _id: 0 } } ]) Note that we should have a double-quote here. If we miss the double-quote, there is a syntax error.

  12. 1. NoSQL (Previous Examples) Output { _sname : "David Lee", _sid : "56785678" } { _sname : "Raymond", _sid : "12345678" } { _sname : "Test Test", _sid : "88888888" }

  13. 1. NoSQL (Previous Examples) • Query 5: Find the names of all students whose birth years are 1998 where duplicate names should be removed. cname cid sid byear teacher sname take student course student (sid, sname, byear)course (cid, cname, teacher)take (sid, cid) db.student.distinct("sname", {byear:1998}) NoSQL Output [ "Raymond", "David Lee", "Test Test" ]

  14. 1. NoSQL (Previous Examples) • Query 6: Find the average birth years of all students cname cid sid byear teacher sname take student course student (sid, sname, byear)course (cid, cname, teacher)take (sid, cid) Note that we should have a double-quote here. If we miss the double-quote, there is a syntax error. NoSQL db.student.aggregate([ { $group: { _id: null, avg_byear: { $avg: "$byear" } } }, { $project: {avg_byear: 1, _id: 0 } } ]) $avg is an aggregate function. There are other aggregate functions. 1. $max 2. $min 3. $sum Output { avg_byear : 1998 }

  15. 1. NoSQL (Previous Examples) • Query 7: Find the student IDs of all students whose names are “Raymond” cname cid sid byear teacher sname take student course student (sid, sname, byear)course (cid, cname, teacher)take (sid, cid) db.student.find({sname:"Raymond"}, {sid:1, _id:0}) NoSQL Output { sid : "12345678" }

  16. 1. NoSQL (Previous Examples) • Query 8: Find the student IDs of all students whose names start with “Ray” cname cid sid byear teacher sname take student course student (sid, sname, byear)course (cid, cname, teacher)take (sid, cid) NoSQL db.student.find({sname: /^Ray/}, {sid:1, _id:0}) “^” means “starts with” “/<sth>/” means a regular expression (which is a form of “patterns” we want to match).

  17. 1. NoSQL (Previous Examples) Output { sid : "12345678" }

  18. 1. NoSQL (Previous Examples) • Query 9: Find the student IDs of all students whose names end with “mond” cname cid sid byear teacher sname take student course student (sid, sname, byear)course (cid, cname, teacher)take (sid, cid) NoSQL db.student.find({sname: /mond$/}, {sid:1, _id:0}) “$” means “end with”. Output { sid : "12345678" }

  19. 1. NoSQL (Previous Examples) • Query 10: Find the student IDs of all students whose names have 3 characters where the first character as “R” and the third character as “y” cname cid sid byear teacher sname take student course student (sid, sname, byear)course (cid, cname, teacher)take (sid, cid) NoSQL db.student.find({sname: /^R.y$/}, {sid:1, _id:0}) “.” means “any character”. If we need to specify “full-stop”, we need to type “\.”. Output <no result>

  20. 1. NoSQL (Previous Examples) cname cid sid byear teacher sname take student course student (sid, sname, byear)course (cid, cname, teacher)take (sid, cid) • Query 11: Find the student IDs of all students whose birth years are 1998 and names are “Raymond” db.student.find({byear:1998, sname:"Raymond"}, {sid:1, _id:0}) NoSQL or db.student.find({$and: [{byear:1998}, {sname:"Raymond"}] }, {sid:1, _id:0}) or R1 = db.student.find({byear:1998}, {sid:1, _id:0}).toArray() R2 = db.student.find({sname: "Raymond"}, {sid:1, _id:0}).toArray() db.temp.insert({col1: R1, col2: R2}) db.temp.aggregate( [ { $project: {answer:{$setIntersection:["$col1", "$col2"]}, _id: 0} } ] ) db.temp.drop() Set intersection

  21. 1. NoSQL (Previous Examples) Output { sid : "12345678" }

  22. 1. NoSQL (Previous Examples) cname cid sid byear teacher sname take student course student (sid, sname, byear)course (cid, cname, teacher)take (sid, cid) • Query 12: Find the student IDs of all students whose birth years are 1998 or names are “Raymond” NoSQL db.student.find({$or: [{byear: 1998}, {sname:"Raymond"} ] }, {sid:1, _id:0}) or R1 = db.student.find({byear:1998}, {sid:1, _id:0}).toArray() R2 = db.student.find({sname: "Raymond"}, {sid:1, _id:0}).toArray() db.temp.insert({col1: R1, col2: R2}) db.temp.aggregate( [ { $project: {answer:{$setUnion:["$col1", "$col2"]}, _id: 0} } ] ) db.temp.drop() Set union

  23. 1. NoSQL (Previous Examples) Output { sid : "12345678" } { sid : "56785678" } { sid : "88888888" }

  24. 1. NoSQL (Previous Examples) cname cid sid byear teacher sname take student course student (sid, sname, byear)course (cid, cname, teacher)take (sid, cid) • Query 13: Find the student IDs of all students whose birth years are 1998 and names are not “Raymond” NoSQL db.student.find({byear: 1998, sname:{$ne:"Raymond"} }, {sid:1, _id:0}) or R1 = db.student.find({byear:1998}, {sid:1, _id:0}).toArray() R2 = db.student.find({sname: "Raymond"}, {sid:1, _id:0}).toArray() db.temp.insert({col1: R1, col2: R2}) db.temp.aggregate( [ { $project: {answer:{$setDifference:["$col1", "$col2"]}, _id: 0} } ] ) db.temp.drop() Set minus

  25. 1. NoSQL (Previous Examples) Output { sid : "56785678" } { sid : "88888888" }

  26. 1. NoSQL (Previous Examples) • Query 14: Find the course IDs of all courses taken by the student with the student ID = “12345678” cname cid sid byear teacher sname take student course student (sid, sname, byear)course (cid, cname, teacher)take (sid, cid) NoSQL db.take.find({sid:"12345678"}, {cid:1, _id:0}) Output { cid : "COMP4332" } { cid : "COMP5331" } { cid : "COMP2711" }

  27. 1. NoSQL (Previous Examples) cname cid sid byear teacher sname take student course student (sid, sname, byear)course (cid, cname, teacher)take (sid, cid) • Query 15: Find the course IDs of all courses taken by the student with the name “Raymond” It involves a “join” operation between two collections. db.take.aggregate( [ { $lookup: { localField: "sid", from: "student", foreignField: "sid", as: "student_info" } }, ] ) Natural Join NoSQL { $unwind: "$student_info"}, { $match: { "student_info.sname": "Raymond" } }, { $project: { cid: 1, _id: 0 } }

  28. 1. NoSQL (Previous Examples) Output { cid : "COMP4332" } { cid : "COMP5331" } { cid : "COMP2711" } In the output, there can be multiple occurrences of the same course ID. It could be regarded as “correct” since we do not require “distinct” course IDs in the output. Suppose that we require the “distinct” requirement. What should we do?

  29. 1. NoSQL (Previous Examples) cname cid sid byear teacher sname take student course student (sid, sname, byear)course (cid, cname, teacher)take (sid, cid) • Query 15: Find the course IDs of all courses taken by the student with the name “Raymond” This version require “distinct” course IDs in the output. NoSQL db.take.aggregate( [ { $lookup: { localField: "sid", from: "student", foreignField: "sid", as: "student_info" } }, { $unwind: "$student_info"}, { $match: { "student_info.sname": "Raymond" } }, { $project: { cid: 1, _id: 0 } }, { $group: { _id: "$cid" } } ] )

  30. 1. NoSQL (Previous Examples) cname cid sid byear teacher sname take student course student (sid, sname, byear)course (cid, cname, teacher)take (sid, cid) • Query 16: Find the student IDs of all students who take the course taught by “Prof. Wong” db.take.aggregate( [ { $lookup: { localField: "cid", from: "course", foreignField: "cid", as: "course_info" } }, ] ) NoSQL { $unwind: "$course_info"}, { $match: { "course_info.teacher": "Prof. Wong" } }, { $project: { sid: 1, _id: 0 } }

  31. 1. NoSQL (Previous Examples) Output { sid : "12345678" } { sid : "12345678" } { sid : "12341234" } { sid : "56785678" } In the output, there can be multiple occurrences of the same student ID. It could be regarded as “correct” since we do not require “distinct” student IDs in the output. Suppose that we require the “distinct” requirement. What should we do?

  32. 1. NoSQL (Previous Examples) cname cid sid byear teacher sname take student course student (sid, sname, byear)course (cid, cname, teacher)take (sid, cid) • Query 16: Find the student IDs of all students who take the course taught by “Prof. Wong” NoSQL This version require “distinct” student IDs in the output. db.take.aggregate( [ { $lookup: { localField: "cid", from: "course", foreignField: "cid", as: "course_info" } }, { $unwind: "$course_info"}, { $match: { "course_info.teacher": "Prof. Wong" } }, { $project: { sid: 1, _id: 0 } }, { $group: {_id: "$sid"}} ] )

  33. 1. NoSQL (Previous Examples) Output { _id : "56785678" } { _id : "12341234" } { _id : "12345678" }

  34. 1. NoSQL (Previous Examples) 1. NoSQL (Previous Examples) cname cid sid byear teacher sname take student course student (sid, sname, byear)course (cid, cname, teacher)take (sid, cid) • Query 17: Find the names of all students who take the course taught by “Prof. Wong” db.student.aggregate([ { $lookup: { localField: "sid", from: "take", foreignField: "sid", as: "student_take" } }, { $unwind: "$student_take" }, ]) NoSQL { $lookup: { localField: "student_take.cid", from: "course", foreignField: "cid", as: "student_take_course", } }, { $unwind: "$student_take_course" }, { $match: { "student_take_course.teacher": "Prof. Wong" } }, { $project: { sname: 1, _id: 0} }

  35. 1. NoSQL (Previous Examples) Output { sname : "Raymond" } { sname : "Raymond" } { sname : "Mary Lau" } { sname : "David Lee" } In the output, there can be multiple occurrences of the same student names. It could be regarded as “correct” since we do not require “distinct” student names in the output. Suppose that we want the “distinct” requirement. What should we do?

  36. 1. NoSQL (Previous Examples) 1. NoSQL (Previous Examples) cname cid sid byear teacher sname take student course student (sid, sname, byear)course (cid, cname, teacher)take (sid, cid) • Query 17: Find the names of all students who take the course taught by “Prof. Wong” This version require “distinct” student names in the output. NoSQL db.student.aggregate([ { $lookup: { from: "take", localField: "sid", foreignField: "sid", as: "student_take" } }, { $unwind: "$student_take" }, { $lookup: { from: "course", localField: "student_take.cid", foreignField: "cid", as: "student_take_course", } }, { $unwind: "$student_take_course" }, { $match: { "student_take_course.teacher": "Prof. Wong" } }, { $project: { sname: 1, _id: 0} }, { $group: { _id: "$sname" }} ])

  37. 1. NoSQL (Previous Examples) Output { _id : "David Lee" } { _id : "Mary Lau" } { _id : "Raymond" }

  38. 1. NoSQL (Previous Examples) • We could write this NoSQL script in another way by using the concept of “set intersection” • “Set intersection” is an operation returning a set of elements which could be found in two given sets.

  39. Peter takes Prof. Wong teaches Prof. Wong teaches 2012 1021 1942 4332 4332 5331 5331 2711 Raymond takes 2012 1021 1942 2711 5331 1. NoSQL (Previous Examples) cname cid sid byear teacher sname take student course Peter DOES NOT take the course taught by Prof. Wong e.g.1 e.g.2 Raymond TAKES the course taught by Prof. Wong

  40. Peter takes Prof. Wong teaches Prof. Wong teaches 2012 Raymond TAKES the course taught by Prof. Wong 1021 1942 4332 4332 5331 5331 2711 Raymond takes 2012 1021 1942 2711 5331 1. NoSQL (Previous Examples) Peter DOES NOT take the course taught by Prof. Wong e.g.1 Intersect? No Intersect? A set of courses taught by Prof. Wong A set of courses taken by Raymond e.g.2 Intersect? Yes 5331 Intersect? if true, the student takes the course taught by Prof. Wong if false, the student DOES NOT take the course taught by Prof. Wong Courses taughtby Prof. Wong Courses takenby a student Conclusion Intersect?

  41. 1. NoSQL (Previous Examples) 1. NoSQL (Previous Examples) cname cid sid byear teacher sname take student course student (sid, sname, byear)course (cid, cname, teacher)take (sid, cid) • Query 17: Find the names of all students who take the course taught by “Prof. Wong” This is an alternative version. NoSQL R1 = db.course.find({teacher:"Prof. Wong"}, {cid:1, _id:0}).toArray() db.student.aggregate( [ { $lookup: { localField: "sid", from: "take", foreignField: "sid", as: "courseList_taken" } }, ] ) { $project: {sname: 1, "courseList_taken.cid": 1, _id: 0} }, { $project: {sname: 1, takenSome: { $setIntersection: [R1, "$courseList_taken"] }, _id: 0} }, { $project: {sname: 1, noOfTakenSome: { $size: "$takenSome" }, _id: 0} }, { $match: { noOfTakenSome: { $gte: 1}} }, { $project: {sname: 1, _id: 0} }, { $group: { _id: "$sname" }}

  42. 1. NoSQL (Previous Examples) Output { _id : "David Lee" } { _id : "Mary Lau" } { _id : "Raymond" }

  43. 1. NoSQL (Previous Examples) cname cid sid byear teacher sname take student course student (sid, sname, byear)course (cid, cname, teacher)take (sid, cid) • Query 18: Find the names of all students who take ALL courses taught by “Prof. Wong”

  44. Peter takes Prof. Wong teaches Prof. Wong teaches 2012 1021 4332 4332 4332 5331 5331 2711 Raymond takes 2012 1021 4332 2711 5331 1. NoSQL (Previous Examples) cname cid sid byear teacher sname take student course Peter takes only ONE course taught by Prof. Wong e.g.1 Peter DOES NOT take ALL courses taught by Prof. Wong e.g.2 Raymond takes TWO courses taught by Prof. Wong Raymond TAKES ALL courses taught by Prof. Wong

  45. Peter takes Prof. Wong teaches Prof. Wong teaches 2012 Raymond TAKES ALL courses taught by Prof. Wong 1021 4332 4332 4332 5331 5331 2711 Raymond takes 2012 1021 4332 2711 5331 1. NoSQL (Previous Examples) Peter DOES NOT take ALL courses taught by Prof. Wong e.g.1 Subset? No ? ? ? A set of courses taught by Prof. Wong A set of courses taken by Raymond e.g.2 Yes Subset? if true, the student takes ALL courses taught by Prof. Wong if false, the student DOES NOT take ALL courses taught by Prof. Wong Courses taughtby Prof. Wong Courses takenby a student Conclusion

  46. 1. NoSQL (Previous Examples) cname cid sid byear teacher sname take student course student (sid, sname, byear)course (cid, cname, teacher)take (sid, cid) • Query 18: Find the names of all students who take ALL courses taught by “Prof. Wong” R1 = db.course.find({teacher:"Prof. Wong"}, {cid:1, _id:0}).toArray() db.student.aggregate( [ { $lookup: { localField: "sid", from: "take", foreignField: "sid", as: "courseList_taken" } }, ] ) { $project: {sname:1, "courseList_taken.cid": 1, _id: 0} }, { $project: {sname: 1, takenAll: { $setIsSubset: [R1, "$courseList_taken"] }, _id: 0} }, { $match: {takenAll: true} }, “$setIsSubset” could not be used in “$match”. Thus, it is now used in “$project”. { $project: {sname: 1, _id: 0} }, { $group: { _id: "$sname" }}

  47. 1. NoSQL (Previous Examples) Output { _id : "Raymond" }

  48. Query 18: Find the names of all students who take ALL courses taught by “Prof. Wong” NoSQL One of the courses taught by Prof. Wong All courses taught by Prof. Wong NoSQL • Query 17: Find the names of all students who take the course taught by “Prof. Wong” R1 = db.course.find({teacher:"Prof. Wong"}, {cid:1, _id:0}).toArray() db.student.aggregate( [ { $lookup: { from: "take", localField: "sid", foreignField: "sid", as: "courseList_taken" } }, { $project: {sname:1, "courseList_taken.cid": 1, _id: 0} }, { $project: {sname: 1, takenAll: { $setIsSubset: [R1, "$courseList_taken"] }, _id: 0} }, { $match: {takenAll: true} }, { $project: {sname: 1, _id: 0} }, { $group: { _id: "$sname" }} ] ) R1 = db.course.find({teacher:"Prof. Wong"}, {cid:1, _id:0}).toArray() db.student.aggregate( [ { $lookup: { from: "take", localField: "sid", foreignField: "sid", as: "courseList_taken" } }, { $project: {sname: 1, "courseList_taken.cid": 1, _id: 0} }, { $project: {sname: 1, takenSome: { $setIntersection: [R1, "$courseList_taken"] }, _id: 0} }, { $project: {sname: 1, noOfTakenSome: { $size: "$takenSome" }, _id: 0} }, { $match: { noOfTakenSome: { $gte: 1}} }, { $project: {sname: 1, _id: 0} }, { $group: { _id: "$sname" }} ] )

  49. 1. NoSQL (Previous Examples) • Query 19: Find the class size of each course.Please show the course id and the class size for each course. cname cid sid byear teacher sname take student course student (sid, sname, byear)course (cid, cname, teacher)take (sid, cid) NoSQL db.take.aggregate([{ $group : {_id : "$cid", classSize : {$sum : 1}} } ])

  50. 1. NoSQL (Previous Examples) Output { _id : "COMP2711", classSize : 1 } { _id : "COMP2011", classSize : 2 } { _id : "RMBI4310", classSize : 2 } { _id : "COMP5331", classSize : 2 } { _id : "COMP4332", classSize : 2 }

More Related