User Tools

Site Tools


mcq:db

Table of Contents

MongoDB

This page contains extracts from other pages grouped here for convenience and being related to MongoDB only.

The pages referenced are the MongoDB quick reference summary, topic 6 and 7 from the lecture notes on MongoDB and topic 6 and 7 exercise sheets at the end.

Hint: Use ctrl+f to search on the page and F3 to jump to the next occurrence of the search.

MongoDB Quickref Sheet

Admin Commands

help
db.help()
 
show dbs
show databases
use <db>
show collections

aggregate $ keywords 1) 2)

$group
  $avg
  $min
  $max
  $sum
  $first
  $last
$lookup
$project

DB Commands

db.collection.save()
db.collection.find()
db.colle....find().pretty()
db.collection.findOne()
 
db.collection.insert()
db.collection.update()

Sorting 3)

cursor.sort()

find/update $ Keywords

$and: [{},{}]
$or: [{},{}]
$in: [1,2,3,7]
$set:{{},{}}
$unset:{field:1}
$rename:{"old":"new"}
 
$exists:true/false
$gt:1
$gte:1
$lt:1
$lte:1

Import/Export data

// import csv into empty collection
mongoimport --db userdb --collection user --type csv --headerline --file C:\Use...\users.csv
// add csv to existing collection
mongoimport --db userdb --collection users --type csv --headerline --mode merge --file C:\Use...\sex.csv
// import json into empty collection
mongoimport --db proj --collection docs --type json --file C:\Users\121988\Documents\52553\mongo.json
//export an exixting collection
mongoexport.exe /db:userdb /collection:users /jsonArray /pretty /out:users.json

Find Examples

find(query)

db.user.find({age:{$gt:19}})
db.user.find({$and: [{age:{$gt:19}}, {carReg: {$exists:true}}]})

find(query, projection)

db.User.find({age: {$gt: 20}}, {email:1})
//Return only the first_name and surname attributes of all documents 
db.User.find({}, {_id:false, first_name:1, surname:1})
db.student.find({address:{$exists: true}}, {_id:0, "address.county":1})                 

Update Examples

Note the use of $set and $unset

db.users.update({_id:105},{$set:{"carReg":"161-MO-4"}})
db.users.update({$and: [{Sex:"M"},{age:{$gt:20}}]},{$set:{"title":"Mr."}},{multi:true})
db.users.update({_id:{$in:[101,103,107]}},{$set:{carReg:""}},{multi:true})
db.users.update({_id:{$in:[101,103,107]}},{$unset:{carReg:1}},{multi:true})
db.users.updateMany({},{$rename:{"fname":"Name"}})

Aggregate Examples

db.collection.aggregate 4) ([$match, $group, $sort])

db.collection.aggregate([{$match:{status:"A"}},{$group:{_id:"$cust_id",total:{$sum:"$amount"}}},{$sort:{total:-1}}])

$group

$group same as Group by in MySQL

//Get the average gpa for all students 
db.users.aggregate([{$group:{_id:null, Average:{$avg:"$gpa"}}}])
//Get the Maximum GPA per age group 
db.march8.aggregate([{$group:{_id:"$age", "Max GPA per Age":{$max:"$gpa"}}}])
//To sort: $sort
db.march8.aggregate([{$group:{_id:"$age", "Max GPA per Age":{$max:"$gpa"}}}, {$sort:{_id:1}}])

$lookup

$lookup: {from, localField, foreignField, as}

from: <collection to join>
localField: <field from the input documents>
foreignField: <field from the documents of the “from” collection>
as: <output array field>
// .aggregate([{$lookup}])
db.docs.aggregate([{$lookup:{from:"docs", localField:"modules", foreignField: "_id", as:"Details"}}])
// .aggregate([{$match},{$lookup}])
db.cityinfo.aggregate([{$match:{name:"Tom"}},{$lookup:{from:"cityinfo", localField:"addresses", foreignField: "_id", as:"Details"}}])
// .aggregate([{$match},{$lookup},{$project}]) 
db.cityinfo.aggregate([{$match:{city:"CHESTERFIELD"}},{$lookup:{from:"cityinfo", localField:"state", foreignField:"_id", as:"StateInfo"}},{$project:{capital:0}}])

Query, Update, Options

db.collection.update 5) (query, update, options)

db.collection.update(
   <query>,
   <update>,
   {
     upsert: <boolean>,
     multi: <boolean>,
     writeConcern: <document>,
     collation: <document>,
     arrayFilters: [ <filterdocument1>, ... ]
   }
)

Indexing

db.collection.getIndexes()
db.user.createIndex({age:1})
db.collection.dropIndex({age:1})

Note: The index on _id cannot be dropped

Topic 6 - MongoDB I

Why NoSQL Databases?

Scalability

Scale Up/Vertically: means moving the database to a bigger server.

Scale Out/Horizontally

Unstructured Data

Add on new features later on like email and then twitter etc…

MongoDB

  • Document Database
  • Schemaless
  • Horizontal Scalability Through sharding6)
  • Duplication of data

JSON

  • JSON - JavaScript Object Notation
  • Lightweight data-interchange format
  • Machine/Human readable
  • Language independent
  • JSON Structure
    • Name/Value pair
    • Ordered Lists

JSON Datatypes

Number

{
  "id" : 1
}
{
  "id" : 3.14
}

Note that there is no distinction between integer and floating point numbers.

String

{
  "id" : 1,
  "fname" : "John"
}

Boolean

{
  "reg" : "09-G-13"
  "hybrid" : false 
}

Array

{
  "student" : "G00257854"
  "subjects" : ["Databases", "Java", "Mobile Apps"]
}

Object Document

{
  "student" : "G00257854"
  "address" : {
    "street" : "Castle Street"
    "town" : "Athenry"
    "county" : "Galway"
  }
}

JSON USES

MongoDB, JSON and BSON

  • JSON object = MongoDB document
  • Internally, MongoDB represents JSON documents in binary-encoded format called BSON (Binary JavaScript Object Notation)
  • BSON extends JSOM model to provide additional data types as well as indexes.

MongoDB Structures

Document FIXME - slide 12….

A document is record in a MongoDB collection and the basic unit of data in MongoDB. Documents are analogous to JSON objects or records in an RDBMS.

Collection

  • A grouping of MongoDB documents.
  • Collections are analogous to RDBMS tables.
  • A collection exists within a single database.
  • Collections do not enforce a schema. Documents within a collection can have different fields.
  • Typically, all documents in a collection have a similar or related purpose.

Database A number of databases can be run on a single MongoDB server.

MongoDB Commands

  • show dbs - Show Databases
  • use myDB - Switch to databases named “myDB” (If it doesn't exist, Mongo creates it)
  • db - Show current Database.
  • show collections - Show collections in the current database

MongoDB Rules for creating a Document

  • Rules for MongoDB documents
    • A document must have an _id field. if one is not provided, it will be automatically generated
    • The _id cannot be an array

Create a document - save()

Query the database - find()

pretty()

$and

$or

$in

Attribute

Attribute and age is greater than 20

findOne()

sort()

MongoDB -_id

  • As previously described, the document ID (_id) attribute of a mongoDB document is the only mandatory part of a document.
  • It can be any value, except an array.

more on save()

insert()

  • Insert a document or documents into a collection.

update()

  • Modifies an existing document or docents in a collection
  • Update (query, update, options)7)

Does not update Mary

$set

deleteOne()

  • Removes a single document from a collection

deleteMany()

Operators

Update Operators

Logical Query Operators

Comparison Query Operators

Topic 7 - MongoDB II

More on find()

db.user.find()

To find only documents that have an email attribute and age is greater than 20

db.user.find({$and:[{email: {$exists:true}}, {age:{$gt:20}}]})

find(query, projection)

db.User.find()

Return only the email attribute of documents where age is greater than 18

db.User.find({age: {$gt: 20}}, {email:1})

Return only the first_name and surname attributes of all documents

db.User.find({}, {_id:false, first_name:1, surname:1})

aggregate()

  • Calculates aggregate values for the data in a collection
  • db.collection.aggregate(pipeline, options)
    • pipeline stages
    • pipeline Operators

Example

Get the average gpa for all students

db.users.aggregate([{$group:{_id:null, Average{$avg:"$gpa"}}}])

$group same as Group by in MYSQL

Result:

Get the Maximum GPA per age group

db.march8.aggregate([{$group:{_id:"$age", "Max GPA per Age":{$max:"$gpa"}}}])

To sort: $sort

db.march8.aggregate([{$group:{_id:"$age", "Max GPA per Age":{$max:"$gpa"}}}, {$sort:{_id:1}}])

Indexing

db.user.find()

Return all documents where age is greater than 18

Psuedo code example

for each document d in 'user'{
  if(d.age == 35){
    return d;
  }
}
  • Indexes support the efficient execution of queries in MongoDB.
  • Without indexes, MongoDB must perform a collection scan, i.e scan every document in a collection, to select those documents that match the query statement.
  • Indexes are special data structures that store a small portion of the collection's data set in an easy to traverse form.
  • Indexes hold mappings from field values to document locations.

getIndexes()

  • By default the only index on a document is on the _id field.
  • To find the indexes on a collection:
db.collection.getIndexes()

Which returns information in the following format, detailing the index field (_id) and the order of the indexes(1 is ascending:-1 is descending):

"key":{
 
  "_id":1
}

createIndex()

  • To create an index on a field other than _id:
  • db.collection.createIndex()

db.user.createIndex({age:1})

dropIndex()

  • To drop an index on a field use:
db.collection.dropIndex()
  • To drop the index on the age field we just created use:
db.collection.dropIndex({age:1})
  • Note: The index on _id cannot be dropped

sort()

  • When a sort() is performed on a field that is not an index, MongoDB will sort the results in memory.
  • If the sort() method consumes more than 32MB of memory, MongoDB aborts the sort.
  • To avoid this error, create an index supporting the sort operation.

__Relationships__ in MongoDB

  • Modelling relationships between documents
    • One-to-One Relationships with Embedded Documents
    • One-to-many Relationships with embedded Documents
    • One-to-many relationships with document references

One-to-One relationships with embedded documents

db.student.save({_id:"G00789445", 
                 name: "John", 
                 address:{_id: 100, 
                          town: "Athenry", 
                          county:"Galway"}})
db.student.find({}, {address:1})
{ "_id" : "G00789445",
  "address" : {
    "_id" : 100,
    "town" : "Athenry",
    "county" : "Galway"
  }
}
  • Show only the county field of documents that have an address field.
db.student.find({address:{$exists: true}}, {_id:0, "address.county":1})                 

Note: Observe details in projection part of find, i.e. find(query, projection), {_id:0, “address.county”:1}

{_id:0, “address.county”:1}Meaning
_id:0Do NOT output _id field
“address.county”:1 Only output county field

One-to-Many Relationships with Embedded Documents

Create the document with the relationships

db.student.save({_id:"G00101224", 
                 name:"Mary", 
                 modules:[{_id:"M100", module:"Databases"},
                           _id:"M101", module:"Java"}]})            

Show the student's _id and module of all modules taken by student G00101224

db.student.find({_id:"G00101224"}, {"modules.module":1})          

projection - only show the module of the modules field

{"_id":"G00101224", "modules":[{"module":"Databases"},{"module":"Java"}]}

One-to-Many relationships with document References

In the example the document has only two field, but in reality it can be a very long document with much more information, so it makes sense to use relationships instead.

with referencing

//save the modules to the docs collection
db.docs.save({_id:"M100", module:"Databases"})      
db.docs.save({_id:"M101", module:"Java"})        
//save the students to the docs collection with references to the modules using the module _id fields.
db.docs.save({_id:"G00101224", name:"Mary", modules["M100", "M101"]})
db.docs.save({_id:"G00205266", name:"Sarah", modules["M100", "M101"]})

$lookup

Using the $lookup pipeline8)

Similar to a join in MySQL…

Performs a left outer join to an unsharded collection in the same database to filter in documents from the “joined” collection for processing. To each input document, the $lookup stage adds a new array field whose elements are the matching documents from the “joined” collection. The $lookup stage passes these reshaped documents to the next stage.

{
   $lookup:
     {
       from: <collection to join>,
       localField: <field from the input documents>,
       foreignField: <field from the documents of the "from" collection>,
       as: <output array field>
     }
}

from - specifies the collection in the same databases to perform the join with. The from collection cannot be sharded.

localField - The value to search for.

foreignField - The field to search for the value specified by localField.

as - The name of the output.

Return all documents including the complete referenced documents

db.docs.aggregate([{$lookup:{from:"docs", localField:"modules", foreignField: "_id", as:"Details"}}])

Embedded Documents vs Referenced Documents

Features of embedded Documents

  • Better performance
  • Atomic

Features of Referenced Documents

  • Slower
  • No repetition
  • More complex relationships

MongoDB vs MySQL

Features of MongoDB

  • Huge amounts of data
  • Unstructured
  • Doesn't really support relationships

Features of MySQL

  • Very Stable
  • Structured
  • Integrity

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
users.csv
_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?9)

> 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 10) documentation.

db.collection.update(query, update, options)

db.collection.update(
   <query>,
   <update>,
   {
     upsert: <boolean>,
     multi: <boolean>,
     writeConcern: <document>,
     collation: <document>,
     arrayFilters: [ <filterdocument1>, ... ]
   }
)

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
sex.csv
_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
users.json
[{
	"_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"
}]

Applied Databases - Topic 7

1. Import lab7.json to MongoDB.

mongoimport --db w7 --collection cityinfo --type json --file ../../../documents/lab7.json
2019-03-21T11:31:23.436+0000    connected to: localhost
2019-03-21T11:31:23.455+0000    imported 19 documents

2. Show the name and population of all cities where the population is over 10,000.

//Query
db.cityinfo.find({$and:[{city:{$exists:true}},{pop:{$gt:10000}}]})
//Result
{ "_id" : "02906", "city" : "PROVIDENCE", "pop" : 31069, "state" : "RI", "capital" : { "name" : "Providence", "electoralCollege" : 4 } }
{ "_id" : "02907", "city" : "CRANSTON", "pop" : 25668, "state" : "RI", "capital" : { "name" : "Providence", "electoralCollege" : 4 } }
{ "_id" : "10001", "city" : "NEW YORK", "pop" : 18913, "state" : "NY", "capital" : { "name" : "Albany", "electoralCollege" : 29 } }
{ "_id" : "01001", "city" : "AGAWAM", "pop" : 15338, "state" : "MA", "capital" : { "name" : "Boston", "electoralCollege" : 11 } }
{ "_id" : "33125", "city" : "MIAMI", "pop" : 47761, "state" : "FL", "capital" : { "name" : "Tallahassee", "electoralCollege" : 29 } }

3. Show the name and population of each state based on the cities shown.

db.cityinfo.find({city:{$exists:true}},{_id:false,state:1, pop:1})
{ "pop" : 31069, "state" : "RI" }
{ "pop" : 177, "state" : "MA" }
{ "pop" : 2867, "state" : "NY" }
{ "pop" : 25668, "state" : "RI" }
{ "pop" : 943, "state" : "NY" }
{ "pop" : 18913, "state" : "NY" }
{ "pop" : 958, "state" : "NY" }
{ "pop" : 15338, "state" : "MA" }
{ "pop" : 47761, "state" : "FL" }
{ "pop" : 9275, "state" : "FL" }
{ "pop" : 7655, "state" : "FL" }
{ "pop" : 3697, "state" : "MA" }

or an aggregated interpretation

db.cityinfo.aggregate([{$match:{city:{$exists:true}}},{$group:{_id:"$state", "Total by State":{$sum:"$pop"}}}])
{ "_id" : "NY", "Total by State" : 23681 }
{ "_id" : "FL", "Total by State" : 64691 }
{ "_id" : "MA", "Total by State" : 19212 }
{ "_id" : "RI", "Total by State" : 56737 }

4. Show the total population of cities in NY as “Population”.

db.cityinfo.aggregate([{$match:{state:"NY"}},{$group:{_id:"$state","Population":{$sum:"$pop"}}}])
{ "_id" : "NY", "Population" : 23681 }

5. Show the _id, city and name of the capital city of each state for cities with a population greater than 20,000.

db.cityinfo.find({$and:[{city:{$exists:true}},{pop:{$gt:20000}}]},{city:1,"capital.name":1})
{ "_id" : "02906", "city" : "PROVIDENCE", "capital" : { "name" : "Providence" } }
{ "_id" : "02907", "city" : "CRANSTON", "capital" : { "name" : "Providence" } }
{ "_id" : "33125", "city" : "MIAMI", "capital" : { "name" : "Tallahassee" } }

6. Show all details for “Tom” including full details of his addresses.

db.cityinfo.aggregate([{$match:{name:"Tom"}},{$lookup:{from:"cityinfo", localField:"addresses", foreignField: "_id", as:"Details"}}]).pretty()
{
        "_id" : "1",
        "name" : "Tom",
        "addresses" : [
                "01001",
                "12997"
        ],
        "Details" : [
                {
                        "_id" : "01001",
                        "city" : "AGAWAM",
                        "pop" : 15338,
                        "state" : "MA",
                        "capital" : {
                                "name" : "Boston",
                                "electoralCollege" : 11
                        }
                },
                {
                        "_id" : "12997",
                        "city" : "WILMINGTON",
                        "pop" : 958,
                        "state" : "NY",
                        "capital" : {
                                "name" : "Albany",
                                "electoralCollege" : 29
                        }
                }
        ]
}

7. Show all details for “Chesterfield” including full details of the state, but do not show details relating to its capital.

HINT: Use the $project aggregation pipeline stage.

chesterfield.txt
db.cityinfo.aggregate([{$match:{city:"CHESTERFIELD"}},{$lookup:{from:"cityinfo", localField:"state", foreignField:"_id", as:"StateInfo"}},{$project:{capital:0}}]).pretty()
{
        "_id" : "01012",
        "city" : "CHESTERFIELD",
        "pop" : 177,
        "state" : "MA",
        "StateInfo" : [
                {
                        "_id" : "MA",
                        "name" : "Massachusetts",
                        "pop" : 6868000,
                        "state" : 1790
                }
        ]
}
mcq/db.txt · Last modified: 2019/04/07 10:31 by gerhard