11 - NoSQL and MongoDB - CRUD Statements

Table of Contents

Suggested Reading Materials

Welcome to Week 11

Welcome to week 11 of DBS311/710, Advanced Database Systems.  This week we continue our work with MongoDB in the area of writing statements to manipulate the data in the database, the equivalent of the SQL statements known as CRUD:

Create or Drop Databases

Creating Using MongoDB Shell

MongoDB creates a database (if it does not exist) when you insert the first document into your database.

> use NewDatabaseName
> db.NewCollection.insertOne({"keyname": "value"})

Both the database, NewDatabaseName, and collection, NewCollection, will be created when the second line is executed.

Dropping Using MongoDB Shell

To drop a database, i.e. delete it permanently 

> use NewDatabaseName
> db.dropDatabase()
removes the current database and all data inside it. The use statement is only required if the database to be deleted is not the current database. This will be the case when first loading the shell each day.

Creating Using Compass

Alternatively you can use Compass to create a database from the main screen by clicking on "Create Database" and entering the database name and the first collection name.

Dropping Using Compass

To Drop a database within Compass, navigate to the main screen by clicking on "Local" in the top left corner, then clicking the trashcan icon beside the database to be deleted. Note that only admins can delete the three reserved databases present. In Compass, you will be prompted to type in the database name to confirm the deletion as an added security protocol.

Create or Drop Collections

Creating Using MongoDB Shell

In addition to creating collections while creating databases shown above, you can choose to create a second collection. By setting the current database first and then using a insert statement to add a document 

> db.NewCollectionName.insert({"keyname":"value", "keyname2":"value2"})
WriteResult({ "nInserted" : 1 })

Dropping Using MongoDB Shell

With the current database set correctly, you can type

> db.CollectionName.drop();

Creating using Mongo Compass

From the main MongoDB screen where the databases are listed, you can click on the correct database and then either click the CREATE COLLECTION button or Click the + icon beside the database name. Both bring up the create collection screen where you type in the name and click CREATE COLLECTION.

Dropping Using Mongo Compass

To drop a collection and all the contained documents, click on the correct database and on the list of collections, hit the trashcan icon beside it. To verify the drop, you are prompted to type in the collection name and then click the DROP COLLECTION button.

Manipulating Documents - CRUD

INSERT

The insertOne function can be used to insert a single document into a collection.

> db.collectionName.insertOne({"key1":value1, "key2":value2, "key3":value3})

The insertion automatically creates an _id key and an auto generated value for each document. You can override this feature by including a "_id" key in the document insertion.

PRACTICE: Insert one document into the book collection, the insertion will be the book "Lord of the Rings" and include the author as "J.R.R. Tolkien". Use the books ISBN, 9780007488377, as the document key value.

> db.book.insertOne({"_id" : "9780007488377", "title" : "Lord of the Rings", "author" : "J.R.R. Tolkien"})
{ "acknowledged" : true, "insertedId" : "9780007488377" }

Bulk INSERT

In the Shell, we use the .insert function to add one or more documents to a collection.

To insert a single row with .insert

> db.collectionName.insert({"key1":value1, "key2":value2, "key3":value3})

or inserting multiple documents at the same time:

> db.collectionName.insert({"key1":value1, "key2":value2}, {"key1" : value1b, "key2" : value2b})

Example:

Insert both a Jeep Cherokee (2 doors) and a Nissan Maxima (4 doors) as documents to the car collection in one statement

> db.car.insert([{"make" : "Jeep", "model" : "Cherokee", "numDoors" : "2"},
... {"make" : "Nissan", "model" : "Maxima", "numDoors" : 4}])
BulkWriteResult({
    "writeErrors" : [ ],
    "writeConcernErrors" : [ ],
    "nInserted" : 2,
    "nUpserted" : 0,
    "nMatched" : 0,
    "nModified" : 0,
    "nRemoved" : 0,
    "upserted" : [ ]
})

The following code would fail 

> db.foo.insert([{"_id" : 0}, {"_id" : 1}, {"_id" : 1}, {"_id" : 2}])
as the _id is duplicated. The first 2 would insert correctly, but the second 2 would fail.

The insertMany function can also be used for bulk inserts.

> db.blog.insertMany([
… {"title" : "My Blog Post", "content" : "Here's my blog post."},
… {"title" : "New Post", "content" : "Here's the new blog post."},
… {"title" : "Public Post", "content" : "Here's the public post."},
])

Deleting Documents (REMOVE)

The remove function deletes documents and can be used in several forms: 

> db.collectionname.remove()
removes ALL documents in the given collection.

The following command removes all documents where the keyname field's value is the value provided. 

> db.collectionname.remove{"keyname" : value}
for example: 
> db.collectionname.remove{"make" : "Honda"}
would remove all cars where the make was set to Honda, regardless of all other values. This would be the equivalent of the SQL statement: 
DELETE FROM cars WHERE make = 'Honda';
that has no filtering by primary key.

The documents cannot be recovered after they are removed.

Updating Documents (UPDATE)

There are two main functions to update data in a MongoDB: .replace() and .update(). Let us look at replace first.

Replace and ReplaceOne

To replace a document with a new one, the replaceOne() function is used.

To replace the following document (in the People collection): 

{ "name" : "Joe" }
we could use something like: 
> db.people.replaceOne({"name" : "Joe"}, {"name" : "Joe", "friends" : 32, "enemies" : 2})
It is possible to change Joe's name too.

Update()

The update() function is used to update the value of a key in a document and takes 2 parameters:

  1. a query document to locate the document to update (like a WHERE clause in SQL)
  2. a modifier document describing the changes to be made (like the SET clause in SQL)

The update operation is atomic, meaning that the LILO (last-in, last-out) philosophy is applied. If there are two update requests coming to the server, the one that reaches the server first will be executed, and when it is done, the second one will be executed.

A Basic Update
given an existing record : { "_id" : 2, "fname" : "Josh", "lname" : "Phan", "favNum" : 16 }
> db.students.update({"_id": 2} , {"fname" : "Josh", "lname": "Phan", "favNum" : 22, "favColor" : "Red"})
WriteResult({ "nMatched" : 1, "nUpserted" : 0, "nModified" : 1 })

NOTE: All the keys and values are included except the _id key. This is important as the following code will cause major problems.

given an existing record : { "_id" : 2, "fname" : "Josh", "lname" : "Phan", "favNum" : 16 }
> db.students.update({"_id": 2} , {"favColor" : "Red"})
WriteResult({ "nMatched" : 1, "nUpserted" : 0, "nModified" : 1 })
RESULTS IN : { "_id" : 2, "FavColor" : "Red" }

NOTE: All the other Keys and Values were lost using that statement. Be CAREFUL.

To solve this you can use a little JavaScript.

given an existing record : { "_id" : 2, "fname" : "Josh", "lname" : "Phan", "favNum" : 16 }
> var id2 = db.students.findOne({"_id" : 2});
> id2.favColor = "Red";
Red
> db.students.update({"_id" : 2}, id2);
WriteResult({ "nMatched" : 1, "nUpserted" : 0, "nModified" : 1 })

to delete a key and value from a record, you can use the same code as above and add:

> delete id2.favNum;
> db.students.update({"_id" : 2}, id2);
WriteResult({ "nMatched" : 1, "nUpserted" : 0, "nModified" : 1 })

This would simply remove the favNum key and value from the document with _id 2.

Duplicate Key/Value Pairs

Assume we have the following documents:

> db.people.find()
{"_id" : ObjectId("4b2b9f67a1f631733d917a7b"), "name" : "joe", "age" : 65},
{"_id" : ObjectId("4b2b9f67a1f631733d917a7c"), "name" : "joe", "age" : 20},
{"_id" : ObjectId("4b2b9f67a1f631733d917a7d"), "name" : "joe", "age" : 49}

Let’s say we want to increment the value of age for the second document.

    > joe = db.people.findOne({"name" : "joe", "age" : 20});
    {
        "_id" : ObjectId("4b2b9f67a1f631733d917a7c"),
        "name" : "joe",
        "age" : 20
    }
    > joe.age++;                                 // makes joe 21 now
    > db.people.update({"name" : "joe"}, joe);
    E11001 duplicate key on update

The update function wants to replace the updated document to the first matching {"name" : "joe"}. But when updating there is another document with the same _id which is the second document. The update will fail. 

> db.people.update({"_id" : ObjectId("4b2b9f67a1f631733d917a7c")}, joe)

Update Modifiers

Now, if we want to update specific Key Values within a document we can use modifiers to alter, add or remove key values.

$inc Modifier

The $inc modifier can be used to increment values by a supplied amount.

> db.websites.findOne({"url":"www.example.com"});
{
    "_id" : ObjectId("4b253b067525f35f94b60a31"),
    "url" : "www.example.com",
    "pageviews" : 52
}
> db.analytics.update({"url" : "www.example.com"}, {"$inc" : {"pageviews" : 1}})
{
    "_id" : ObjectId("4b253b067525f35f94b60a31"),
    "url" : "www.example.com",
    "pageviews" : 53
}

$set Modifier

The $set modifier allows you to update a specific field without replacing the entire document. If the key provided does not exist, it will create it.

For Example: given the following document:

{
    "_id" : ObjectId("4b253b067525f35f94b60a31"),
    "url" : "www.example.com",
    "pageviews" : 52
}

We can use the .update() function to change a value for a specific existing key.

> db.websites.update({"url" : "www.example.com"}, {"$set" : {"pageviews" : 23}})
{
    "_id" : ObjectId("4b253b067525f35f94b60a31"),
    "url" : "www.example.com",
    "pageviews" : 23
}

We can use the .update() function to add a new key and value to an existing document.

> db.websites.update({"url" : "www.example.com"}, {"$set" : {"homepage" : "/index.html"}})
{
    "_id" : ObjectId("4b253b067525f35f94b60a31"),
    "url" : "www.example.com",
    "pageviews" : 23
    "homepage" : "/index.html"
}
Using Set for Nested (Embedded) Documents

The $set operator can be used to update an embedded document.

> db.blog.posts.findOne({"title":"A Blog Post"});
{
    "_id" : ObjectId("4b253b067525f35f94b60a31"),
    "title" : "A Blog Post",
    "content" : "...",
    "author" : {
        "name" : "joe",
        "email" : "joe@example.com"
    }
}

> db.blog.posts.update({"author.name" : "joe"},
... {"$set" :{"author.name" :"joe schmoe"}})
> db.blog.posts.findOne()
{
    "_id" : ObjectId("4b253b067525f35f94b60a31"),
    "title" : "A Blog Post",
    "content" : "...",
    "author" : {
        "name" : "joe schmoe",
        "email" : "joe@example.com"
    }
}

$unset Operator

The $unset function is used to remove a key from a document.

> db.websites.update({"url":"www.example.com")},{"$unset", "homepage":"index.html"});
{
    "_id" : ObjectId("4b253b067525f35f94b60a31"),
    "url" : "www.example.com",
    "pageviews" : 23
    "homepage" : "/index.html"
}

the homepage key and value are deleted.

Array Modifiers

There are modifiers, similar to JavaScript, for manipulating arrays of documents.

$push

The "$push" operator adds elements to the end of an array if the array exists. If the array does not exists it will be created with the given elements.

> db.blog.posts.findOne()
{
            "_id" : ObjectId("4b2d75476cc613d5ee930164"),
            "title" : "A blog post",
            "content" : "..."
        }

The following command adds an array comment to the existing document. 

> db.blog.posts.update({"title" : "A blog post"}, 
... {"$push" : {"comments" :
...     {"name" : "joe", "email" : "joe@example.com", 
...     "content" : "nice post."}}})

Since the “comments” key does not exist, it will be created

> db.blog.posts.findOne()
{
    "_id" : ObjectId("4b2d75476cc613d5ee930164"),
    "title" : "A blog post",
    "content" : "...",
    "comments" : [
        {
            "name" : "joe",
            "email" : "joe@example.com",
            "content" : "nice post."
        }
    ]
}
Adding More Elements

Let's add another comments, but this time the array exists, so it will be added to the end.

> db.blog.posts.update({"title" : "A blog post"}, 
... {"$push" : {"comments" :
...     {"name" : "bob", "email" : "bob@example.com", 
...     "content" : "good post."}}})
> db.blog.posts.findOne()
{
    "_id" : ObjectId("4b2d75476cc613d5ee930164"),
    "title" : "A blog post",
    "content" : "...",
    "comments" : [
        {
            "name" : "joe",
            "email" : "joe@example.com",
            "content" : "nice post."
        },
        {
            "name" : "bob",
            "email" : "bob@example.com",
            "content" : "good post."
        }
    ]
}

$each

The “$each” operators is used to push multiple values to an array on one “$push” operation.

> db.stock.ticker.update({"_id" : "GOOG"}, 
    ... {"$push" : {"hourly" : {"$each" : [562.776, 562.790, 559.123]}}})

Three elements are pushed into the array.

$slice

The $slice operator is used with the “$push” operator to make sure that an array will not grow bigger than a certain size.

> db.movies.find({"genre" : "horror"}, 
    ... {"$push" : {"top10" : {
    ...     "$each" : ["Nightmare on Elm Street", "Saw"], 
    ...     "$slice" : -10}}})

The value of the $slice limits the array to hold 10 elements. If the number of pushing elements violates the size, only the last 10 elements added to the array will be kept.

$sort

You can sort an array before pushing more elements to the array. 

> db.movies.find({"genre" : "horror"}, 
    ... {"$push" : {"top10" : {
    ...     "$each" : [{"name" : "Nightmare on Elm Street", "rating" : 6.6}, 
    ...                {"name" : "Saw", "rating" : 4.3}], 
    ...     "$slice" : -10, 
    ...     "$sort" : {"rating" : -1}}}})

Before pushing the new elements, all elements existing in the array and the new ones is sorted by the “rating” key and the first 10 elements from the sorted list will be stored into the array.

Arrays as Sets

coming soon......

CRUD in MongoDB Compass

In Compass, the flexibility is currently fairly limited to what you can do directly in the GUI. It is easiest to create JSON or CSV files externally and then import documents into the collection.

INSERT

To insert a single document, click on the database and then the collection where the document is to be stored, then click the ADD DATA button and choose "Insert Document". Type the document to be inserted as a JSON object in the box. Note: At this time, entering JavaScript in the Insert window is not permitted, so new Date() does not function here yet. We can use a Shell update statement later to insert that information.