User Tools

Site Tools


mcq:db

Table of Contents

This is an old revision of the document!


MongoDB

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 sharding1)
  • 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)2)

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 pipeline3)

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?4)

> 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 5) 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
                }
        ]
}

MongoDB Quickref Sheet

Admin Commands

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

aggregate $ keywords 6) 7)

$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 8)

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 9) ([$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 10) (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

mcq/db.1554631063.txt.gz · Last modified: 2019/04/07 09:57 by gerhard