~~CLOSETOC~~ |<100% 25% - >| ^ \\ DATA ANALYTICS REFERENCE DOCUMENT\\ \\ ^^ ^ Document Title:|Mongodb exercise sheet| ^ Document No.:|1552511517| ^ Author(s):|Gerhard van der Linde| ^ Contributor(s):| | **REVISION HISTORY** |< 100% 10% - - 10% 17% 10% >| ^ \\ Revision\\ \\ ^\\ Details of Modification(s)^\\ Reason for modification^ \\ Date ^ \\ By ^ | [[:doku.php?id=worksheet:databases:topic6&do=revisions|0]] |Draft release|Mongodb exercise sheet| 2019/03/13 21:11 | Gerhard van der Linde | ---- ====== Applied Databases - Topic 6 ====== ===== 1. Create a mongodb database called usersdb ===== use userdb ===== 2. Create a collection called users which has documents with the following attributes: ===== ^_id ^fname ^surname ^age ^email ^carReg^ |100 |John |Smith |33 |jsmith@gmail.com| 131-G-101| |101 |Sean |Murphy |21 |seanmurph@yahoo.com| 04-WH-235| |102 |Aine |Browne |23 |abrowne@gmail.com| | |103 |Alan |Murphy |24 |murpha@hotmail.com| 07-RN-9988| |104 |Sarah |Doyle |23 |sarah@gmail.com| 142-G-2343| |105 |Bill |Mulligan |19 |billy123@gmail.com| | |106 |Shane |Kelly |24 |sk998@yahoo.com| | |107 |Will |Doyle |19 |doyler123@gmail.com| 10-G-2353| _id,fname,surname,age,email,carReg 100,John,Smith,33,jsmith@gmail.com, 131-G-101 101,Sean,Murphy,21,seanmurph@yahoo.com, 04-WH-235 102,Aine,Browne,23,abrowne@gmail.com, 103,Alan,Murphy,24,murpha@hotmail.com, 07-RN-9988 104,Sarah,Doyle,23,sarah@gmail.com, 142-G-2343 105,Bill,Mulligan,19,billy123@gmail.com, 106,Shane,Kelly,24,sk998@yahoo.com, 107,Will,Doyle,19,doyler123@gmail.com, 10-G-2353 > show dbs admin 0.000GB config 0.000GB local 0.000GB > db.user.save({_id:100,fname:,surname:,age:,email:,carReg: }) 2019-03-13T21:40:32.298+0000 E QUERY [js] SyntaxError: expected expression, got ',' @(shell):1:28 > db.user.save({_id:100,fname:"John",surname:"smith",age:"33",email:"jsmith@gmail.com",carReg:"131-G-101"}) WriteResult({ "nMatched" : 0, "nUpserted" : 1, "nModified" : 0, "_id" : 100 }) > db.user.find() { "_id" : 100, "fname" : "John", "surname" : "smith", "age" : "33", "email" : "jsmith@gmail.com", "carReg" : "131-G-101" } > db.user.find().pretty() { "_id" : 100, "fname" : "John", "surname" : "smith", "age" : "33", "email" : "jsmith@gmail.com", "carReg" : "131-G-101" } > db.user.find() { "_id" : 100, "fname" : "John", "surname" : "smith", "age" : "33", "email" : "jsmith@gmail.com", "carReg" : "131-G-101" } > db.user.save({_id:101,fname:"Sean",surname:"Murphy",age:"21",email:"seanmurphy@yahoo.com",carReg:"04-WH-235"}) WriteResult({ "nMatched" : 0, "nUpserted" : 1, "nModified" : 0, "_id" : 101 }) > db.user.find() { "_id" : 100, "fname" : "John", "surname" : "smith", "age" : "33", "email" : "jsmith@gmail.com", "carReg" : "131-G-101" } { "_id" : 101, "fname" : "Sean", "surname" : "Murphy", "age" : "21", "email" : "seanmurphy@yahoo.com", "carReg" : "04-WH-235" } > db userdb mongoimport --db userdb --collection user --type csv --headerline --file C:\Users\121988\Documents\users.csv 2019-03-13T22:02:11.355+0000 connected to: localhost 2019-03-13T22:02:11.359+0000 imported 6 documents ===== 3. Give the command to find all documents in the users collection. ===== > db userdb > db.user.find() { "_id" : 100, "fname" : "John", "surname" : "smith", "age" : "33", "email" : "jsmith@gmail.com", "carReg" : "131-G-101" } { "_id" : 101, "fname" : "Sean", "surname" : "Murphy", "age" : "21", "email" : "seanmurphy@yahoo.com", "carReg" : "04-WH-235" } { "_id" : 102, "fname" : "Aine", "surname" : "Browne", "age" : 23, "email" : "abrowne@gmail.com", "carReg" : "" } { "_id" : 104, "fname" : "Sarah", "surname" : "Doyle", "age" : 23, "email" : "sarah@gmail.com", "carReg" : "142-G-2343" } { "_id" : 103, "fname" : "Alan", "surname" : "Murphy", "age" : 24, "email" : "murpha@hotmail.com", "carReg" : "07-RN-9988" } { "_id" : 106, "fname" : "Shane", "surname" : "Kelly", "age" : 24, "email" : "sk998@yahoo.com", "carReg" : "" } { "_id" : 105, "fname" : "Bill", "surname" : "Mulligan", "age" : 19, "email" : "billy123@gmail.com", "carReg" : "" } { "_id" : 107, "fname" : "Will", "surname" : "Doyle", "age" : 19, "email" : "doyler123@gmail.com", "carReg" : "10-G-2353" } > ===== 4. Give the command to find all documents in the users collection where the age is 19. ===== > db.user.find({age:19}) { "_id" : 105, "fname" : "Bill", "surname" : "Mulligan", "age" : 19, "email" : "billy123@gmail.com", "carReg" : "" } { "_id" : 107, "fname" : "Will", "surname" : "Doyle", "age" : 19, "email" : "doyler123@gmail.com", "carReg" : "10-G-2353" } > ===== 5. Give the command to find all documents in the users collection where the age is greater than 19. ===== > db.user.find({age:{$gt:19}}) { "_id" : 102, "fname" : "Aine", "surname" : "Browne", "age" : 23, "email" : "abrowne@gmail.com", "carReg" : "" } { "_id" : 104, "fname" : "Sarah", "surname" : "Doyle", "age" : 23, "email" : "sarah@gmail.com", "carReg" : "142-G-2343" } { "_id" : 103, "fname" : "Alan", "surname" : "Murphy", "age" : 24, "email" : "murpha@hotmail.com", "carReg" : "07-RN-9988" } { "_id" : 106, "fname" : "Shane", "surname" : "Kelly", "age" : 24, "email" : "sk998@yahoo.com", "carReg" : "" } > ===== 6. Give the command to find all documents in the users collection where the age is greater than 19 and the user has a car. ===== > db.user.find({$and: [{age:{$gt:19}}, {carReg: {$exists:true}}]}) { "_id" : 102, "fname" : "Aine", "surname" : "Browne", "age" : 23, "email" : "abrowne@gmail.com", "carReg" : "" } { "_id" : 104, "fname" : "Sarah", "surname" : "Doyle", "age" : 23, "email" : "sarah@gmail.com", "carReg" : "142-G-2343" } { "_id" : 103, "fname" : "Alan", "surname" : "Murphy", "age" : 24, "email" : "murpha@hotmail.com", "carReg" : "07-RN-9988" } { "_id" : 106, "fname" : "Shane", "surname" : "Kelly", "age" : 24, "email" : "sk998@yahoo.com", "carReg" : "" } > ===== 7. Give the command to find all documents in the users collection where _id is greater than 104 and age is greater than 20. ===== > db.user.find({$and: [{_id:{$gt:104}}, {age: {$gt:20}}]}).pretty() { "_id" : 106, "fname" : "Shane", "surname" : "Kelly", "age" : 24, "email" : "sk998@yahoo.com", "carReg" : "" } ===== 8. Give the command to find the first document in the users collection where the user has a car. ===== > db.user.findOne({carReg:{$exists:true}}) { "_id" : 100, "fname" : "John", "surname" : "smith", "age" : "33", "email" : "jsmith@gmail.com", "carReg" : "131-G-101" } ===== 9. User 106 - Shane has bought a car with reg 12-G-1234. ===== The following command was run to update the user’s document: db.users.save({_id:106, carReg:"12-G-1234"}) What does the document look like now and why?((https://docs.mongodb.com/manual/reference/method/db.collection.save/)) > db.users.save( {_id:106, carReg:"12-G-1234"} ) WriteResult({ "nMatched" : 1, "nUpserted" : 0, "nModified" : 1 }) > db.users.find() { "_id" : 100, "fname" : "John", "surname" : "Smith", "age" : 33, "email" : "jsmith@gmail.com", "carReg" : "131-G-101" } { "_id" : 102, "fname" : "Aine", "surname" : "Browne", "age" : 23, "email" : "abrowne@gmail.com", "carReg" : "" } { "_id" : 101, "fname" : "Sean", "surname" : "Murphy", "age" : 21, "email" : "seanmurph@yahoo.com", "carReg" : "04-WH-235" } { "_id" : 103, "fname" : "Alan", "surname" : "Murphy", "age" : 24, "email" : "murpha@hotmail.com", "carReg" : "07-RN-9988" } { "_id" : 104, "fname" : "Sarah", "surname" : "Doyle", "age" : 23, "email" : "sarah@gmail.com", "carReg" : "142-G-2343" } { "_id" : 105, "fname" : "Bill", "surname" : "Mulligan", "age" : 19, "email" : "billy123@gmail.com", "carReg" : "" } { "_id" : 106, "carReg" : "12-G-1234" } { "_id" : 107, "fname" : "Will", "surname" : "Doyle", "age" : 19, "email" : "doyler123@gmail.com", "carReg" : "10-G-2353" } > ===== 10. User 102 - Aine has bought a car with reg 10-G-9876. ===== The following command was run to update the user’s document: db.users.update({_id:102}, {carReg:"10-G-9876"}) What does the document look like now and why? > db.users.update({_id:102}, {carReg:"10-G-9876"}) WriteResult({ "nMatched" : 1, "nUpserted" : 0, "nModified" : 1 }) > db.users.find() { "_id" : 100, "fname" : "John", "surname" : "Smith", "age" : 33, "email" : "jsmith@gmail.com", "carReg" : "131-G-101" } { "_id" : 102, "carReg" : "10-G-9876" } { "_id" : 101, "fname" : "Sean", "surname" : "Murphy", "age" : 21, "email" : "seanmurph@yahoo.com", "carReg" : "04-WH-235" } { "_id" : 103, "fname" : "Alan", "surname" : "Murphy", "age" : 24, "email" : "murpha@hotmail.com", "carReg" : "07-RN-9988" } { "_id" : 104, "fname" : "Sarah", "surname" : "Doyle", "age" : 23, "email" : "sarah@gmail.com", "carReg" : "142-G-2343" } { "_id" : 105, "fname" : "Bill", "surname" : "Mulligan", "age" : 19, "email" : "billy123@gmail.com", "carReg" : "" } { "_id" : 106, "carReg" : "12-G-1234" } { "_id" : 107, "fname" : "Will", "surname" : "Doyle", "age" : 19, "email" : "doyler123@gmail.com", "carReg" : "10-G-2353" } Why.....FIXME ===== 11. User 105 – Bill’s document is as follows: ===== { "_id" : 105, "fname" : "Bill", "surname" : "Mulligan", "age": 19, "email" : "billy123@gmail.com" } Bill has bought a car with reg 161-MO-4. Give the command so that Bill’s document now looks as follows: { "_id" : 105, "fname" : "Bill", "surname" : "Mulligan", "age": 19, "email" : "billy123@gmail.com", "carReg" : "161-MO-4" } > db.users.update({_id:105},{$set:{"carReg":"161-MO-4"}}) WriteResult({ "nMatched" : 1, "nUpserted" : 0, "nModified" : 1 }) The key difference is $set in the update command used to achieve this result. ===== 12. User 106’s document now looks as follows: ===== { "_id" : 106, "carReg" : "12-G-1234" } Give the command to add the original fields: ^_id ^fname ^surname ^Age ^email ^ |106 |Shane |Kelly |24 |sk998@yahoo.com | back to the document in a single command so that the document now looks as follows: ^_id ^fname ^surname ^Age ^email ^carReg ^ |106 |Shane |Kelly |24 |sk998@yahoo.com |12-G-1234 | db.users.update({_id:106},{$set:{"fname":"Shane","surname":"Kelly","age":"24","email":"sk998@yahoo.com"}}) ===== 13. Give the mongodb command to add 1 to each user’s age. ===== db.users.update({age:{$exists:true}},{$inc:{"age":1}},{multi:true}) See update ((https://docs.mongodb.com/manual/reference/method/db.collection.update/#db.collection.update)) documentation. db.collection.update(query, update, options) db.collection.update( , , { upsert: , multi: , writeConcern: , collation: , arrayFilters: [ , ... ] } ) ===== 14. Add a new attribute sex, to each document as follows: ===== ^_id ^Sex^ |100 |M | |101 |M | |103 |M | |104 |F | |105 |M | |106 |M | |107 |M | _id,Sex, 100,M 101,M 103,M 104,F 105,M 106,M 107,M mongoimport --db userdb --collection users --type csv --headerline --mode merge --file C:\Users\121988\Documents\sex.csv 2019-03-14T15:05:16.974+0000 connected to: localhost 2019-03-14T15:05:16.992+0000 error inserting documents: Performing an update on the path '_id' would modify the immutable field '_id' 2019-03-14T15:05:16.993+0000 imported 7 documents Note the addition flag used: **--mode merge** ===== 15. Add a new attribute title with the value Mr., to each document where the sex is M, and the age is greater than 20: ===== > db.users.update({$and: [{Sex:"M"},{age:{$gt:20}}]},{$set:{"title":"Mr."}}) WriteResult({ "nMatched" : 1, "nUpserted" : 0, "nModified" : 1 }) > db.users.update({$and: [{Sex:"M"},{age:{$gt:20}}]},{$set:{"title":"Mr."}},{multi:true}) WriteResult({ "nMatched" : 4, "nUpserted" : 0, "nModified" : 3 }) > db.users.find() { "_id" : 100, "fname" : "John", "surname" : "Smith", "age" : 37, "email" : "jsmith@gmail.com", "carReg" : "131-G-101", "Sex" : "M", "title" : "Mr." } { "_id" : 102, "carReg" : "10-G-9876", "age" : 3 } { "_id" : 101, "fname" : "Sean", "surname" : "Murphy", "age" : 24, "email" : "seanmurph@yahoo.com", "carReg" : "04-WH-235", "Sex" : "M", "title" : "Mr." } { "_id" : 103, "fname" : "Alan", "surname" : "Murphy", "age" : 27, "email" : "murpha@hotmail.com", "carReg" : "07-RN-9988", "Sex" : "M", "title" : "Mr." } { "_id" : 104, "fname" : "Sarah", "surname" : "Doyle", "age" : 26, "email" : "sarah@gmail.com", "carReg" : "142-G-2343", "Sex" : "F" } { "_id" : 105, "fname" : "Bill", "surname" : "Mulligan", "age" : 22, "email" : "billy123@gmail.com", "carReg" : "161-MO-4", "Sex" : "M", "title" : "Mr." } { "_id" : 106, "carReg" : "12-G-1234", "age" : 25, "email" : "sk998@yahoo.com", "fname" : "Shane", "surname" : "Kelly" } { "_id" : 107, "fname" : "Will", "surname" : "Doyle", "age" : 20, "email" : "doyler123@gmail.com", "carReg" : "10-G-2353", "Sex" : "M" } > ===== 16. Users 101 – Sean, 103 – Alan and 107 – Will have sold their cars, update the collection to remove the carReg attribute from these documents. ===== ==== Clearing the field only ==== > db.users.update({_id:{$in:[101,103,107]}},{$set:{carReg:""}},{multi:true}) WriteResult({ "nMatched" : 3, "nUpserted" : 0, "nModified" : 3 }) > db.users.find() { "_id" : 100, "fname" : "John", "surname" : "Smith", "age" : 37, "email" : "jsmith@gmail.com", "carReg" : "131-G-101", "Sex" : "M", "title" : "Mr." } { "_id" : 102, "carReg" : "10-G-9876", "age" : 3 } { "_id" : 101, "fname" : "Sean", "surname" : "Murphy", "age" : 24, "email" : "seanmurph@yahoo.com", "carReg" : "", "Sex" : "M", "title" : "Mr." } { "_id" : 103, "fname" : "Alan", "surname" : "Murphy", "age" : 27, "email" : "murpha@hotmail.com", "carReg" : "", "Sex" : "M", "title" : "Mr." } { "_id" : 104, "fname" : "Sarah", "surname" : "Doyle", "age" : 26, "email" : "sarah@gmail.com", "carReg" : "142-G-2343", "Sex" : "F" } { "_id" : 105, "fname" : "Bill", "surname" : "Mulligan", "age" : 22, "email" : "billy123@gmail.com", "carReg" : "161-MO-4", "Sex" : "M", "title" : "Mr." } { "_id" : 106, "carReg" : "12-G-1234", "age" : 25, "email" : "sk998@yahoo.com", "fname" : "Shane", "surname" : "Kelly" } { "_id" : 107, "fname" : "Will", "surname" : "Doyle", "age" : 20, "email" : "doyler123@gmail.com", "carReg" : "", "Sex" : "M" } > ==== Removing the field completely ==== > db.users.update({_id:{$in:[101,103,107]}},{$unset:{carReg:1}},{multi:true}) WriteResult({ "nMatched" : 3, "nUpserted" : 0, "nModified" : 3 }) > db.users.find() { "_id" : 100, "fname" : "John", "surname" : "Smith", "age" : 37, "email" : "jsmith@gmail.com", "carReg" : "131-G-101", "Sex" : "M", "title" : "Mr." } { "_id" : 102, "carReg" : "10-G-9876", "age" : 3 } { "_id" : 101, "fname" : "Sean", "surname" : "Murphy", "age" : 24, "email" : "seanmurph@yahoo.com", "Sex" : "M", "title" : "Mr." } { "_id" : 103, "fname" : "Alan", "surname" : "Murphy", "age" : 27, "email" : "murpha@hotmail.com", "Sex" : "M", "title" : "Mr." } { "_id" : 104, "fname" : "Sarah", "surname" : "Doyle", "age" : 26, "email" : "sarah@gmail.com", "carReg" : "142-G-2343", "Sex" : "F" } { "_id" : 105, "fname" : "Bill", "surname" : "Mulligan", "age" : 22, "email" : "billy123@gmail.com", "carReg" : "161-MO-4", "Sex" : "M", "title" : "Mr." } { "_id" : 106, "carReg" : "12-G-1234", "age" : 25, "email" : "sk998@yahoo.com", "fname" : "Shane", "surname" : "Kelly" } { "_id" : 107, "fname" : "Will", "surname" : "Doyle", "age" : 20, "email" : "doyler123@gmail.com", "Sex" : "M" } > ===== 17. Give the mongodb command to list/show only the fname, surname, age and sex attributes of documents where the _id is between 101 and 107 inclusive. ===== > db.users.find({_id:{$gte:101,$lte:107}},{fname:1,surname:1,age:1,sex:1}) { "_id" : 101, "fname" : "Sean", "surname" : "Murphy", "age" : 24 } { "_id" : 102, "age" : 3 } { "_id" : 103, "fname" : "Alan", "surname" : "Murphy", "age" : 27 } { "_id" : 104, "fname" : "Sarah", "surname" : "Doyle", "age" : 26 } { "_id" : 105, "fname" : "Bill", "surname" : "Mulligan", "age" : 22 } { "_id" : 106, "age" : 25, "fname" : "Shane", "surname" : "Kelly" } { "_id" : 107, "fname" : "Will", "surname" : "Doyle", "age" : 20 } ===== 18. Give the mongodb command to rename the fname attribute to Name. ===== > db.users.updateMany({},{$rename:{"fname":"Name"}}) { "acknowledged" : true, "matchedCount" : 8, "modifiedCount" : 7 } > db.users.find() { "_id" : 100, "surname" : "Smith", "age" : 37, "email" : "jsmith@gmail.com", "carReg" : "131-G-101", "Sex" : "M", "title" : "Mr.", "Name" : "John" } { "_id" : 102, "carReg" : "10-G-9876", "age" : 3 } { "_id" : 101, "surname" : "Murphy", "age" : 24, "email" : "seanmurph@yahoo.com", "Sex" : "M", "title" : "Mr.", "Name" : "Sean" } { "_id" : 103, "surname" : "Murphy", "age" : 27, "email" : "murpha@hotmail.com", "Sex" : "M", "title" : "Mr.", "Name" : "Alan" } { "_id" : 104, "surname" : "Doyle", "age" : 26, "email" : "sarah@gmail.com", "carReg" : "142-G-2343", "Sex" : "F", "Name" : "Sarah" } { "_id" : 105, "surname" : "Mulligan", "age" : 22, "email" : "billy123@gmail.com", "carReg" : "161-MO-4", "Sex" : "M", "title" : "Mr.", "Name" : "Bill" } { "_id" : 106, "carReg" : "12-G-1234", "age" : 25, "email" : "sk998@yahoo.com", "surname" : "Kelly", "Name" : "Shane" } { "_id" : 107, "surname" : "Doyle", "age" : 20, "email" : "doyler123@gmail.com", "Sex" : "M", "Name" : "Will" } ===== 19. Export the userdb.users to a json file ===== mongoexport.exe /db:userdb /collection:users /jsonArray /pretty /out:users.json 2019-03-15T14:51:28.701+0000 connected to: localhost 2019-03-15T14:51:28.704+0000 exported 8 records [{ "_id": 100, "name": "John", "surname": "Smith", "sex": "M", "age": 37.0, "email": "jsmith@gmail.com", "carReg": "131-G-101", "titel": "Mr." }, { "_id": 101, "name": "Sean", "surname": "Murphy", "sex": "M", "age": 24.0, "email": "seanmurph@yahoo.com", "titel": "Mr." }, { "_id": 102, "age": 3.0, "carReg": "10-G-9876" }, { "_id": 103, "name": "Alan", "surname": "Murphy", "sex": "M", "age": 27.0, "email": "murpha@hotmail.com", "titel": "Mr." }, { "_id": 104, "name": "Sarah", "surname": "Doyle", "sex": "F", "age": 26.0, "email": "sarah@gmail.com", "carReg": "142-G-2343" }, { "_id": 105, "name": "Bill", "surname": "Mulligan", "sex": "M", "age": 22.0, "email": "billy123@gmail.com", "carReg": "161-MO-4", "titel": "Mr." }, { "_id": 106.0, "name": "Shane", "surname": "Kelly", "age": 25.0, "email": "sk998@yahoo.com", "carReg": "12-G-1234" }, { "_id": 107, "name": "Will", "surname": "Doyle", "sex": "M", "age": 20.0, "email": "doyler123@gmail.com" }]