25MongoDBDataManipulations

MongoDB Data Manipluations

Outline

A sample database
Inserting documents
Removing documents
Updating documents

A sample database

A conceptual schema of a database with information about suppliers, products, customers, orders, and details of orders

A sample database

A sample collection orders

A sample database

A sample document, that belongs to a class CUSTOMER

{
        "_id":"ALFKI",
        "CUSTOMER":{
                 "customer code":"ALFKI",
                 "company name":"Alfreds Futterkiste",
                 "contact name":"Maria Anders",
                 "contact title":"Sales Representative",
                 "address":"Obere Str. 57",
                 "city":"Berlin",
                 "region":null,
                 "postal code":"12209",
                 "country":"Germany",
                 "phone":"030-0074321",
                 "fax":"030-0076545",
                 "submits":[ ]
        }
}

A sample database

A sample nested document, that belongs to a class CUSTOMER

{
 "_id":"FAMIA",
 "CUSTOMER":{
               "customer code":"FAMIA",
                ...  ...  ...  ...  ...  ...
               "submits":[
                            {
                             "ORDER":{
                                        "order id":328,
                                         ...  ...  ...  ...  ...
                                        "consists of":[
                                                         {
                                                          "ORDER DETAIL":{
                                                                            "product name":"Louisiana Fiery Hot Pepper Sauce",
                                                                            ...  ...  ...  ...  ...
                                                                           }
                                                         },
                                                         {
                                                          "ORDER DETAIL":{
                                                                            "product name":"Raclette Courdavault",
	                                                                    ...  ...  ...  ...  ...
                                                                           }
                                                         }
                                                        ]
                                       }
                            } 
 ] } }

The present slide shows a sample BSON document that implements an instance of object in a class SUPPLIER. A key "_id" is associated with value "Karkki Oy". The same value is associated with a key "company name". A property "company name" is an identifier of a class SUPPLIER.

A key "SUPPLIER" is associated with a sub-document that contains a description of an instance of object in a class SUPPLIER. A key "supplies" is associated with an array that contains the descriptions of products supplied by a supplier. A product is represented by a sub-document associated with a key "PRODUCT".

A value associated with a key "product name" uniquely identifies each product. Such identification can be later on enforced by a unique index created over the values associated with a key "product name".

Note that a value associated with a key "product name" is used in a BSON object given on the previous slide s link to a product description sub-document nested with SUPPLIER sub-document.

A sample database

A sample nested document, that belongs to a class SUPPLIER

{
 "_id":"Karkki Oy",
 "SUPPLIER":{
               "company name":"Karkki Oy",
               "contact name":"Anne Heikkonen",
               "contact title":"Product Manager",
               "address":"Valtakatu 12",
               ...  ...  ...  ...  ...  ...
               "supplies":[
                             {
                              "PRODUCT":{
                                           "product name":"Maxilaku",
                                           "category name":"Confections",
                                            ...  ...  ...  ...  ... 
                                          }
                             },
                             {
                              "PRODUCT":{
                                           "product name":"Valkoinen suklaa",
                                           "category name":"Confections",
                                           ...  ...  ...  ...  ...
                                          }
                             }

                            ]
              }
}

MongoDB Data Manipluations

Inserting documents

A method insert() can be used to insert a single document into a collection

db.orders.insert(
      {
        "_id":"ALFKI",
        "CUSTOMER":{
                      "customer code":"ALFKI",
                      "company name":"Alfreds Futterkiste",
                      "contact name":"Maria Anders",
                      "contact title":"Sales Representative",
                      "address":"Obere Str. 57",
                      "city":"Berlin",
                      "region":null,
                      "postal code":"12209",
                      "country":"Germany",
                      "phone":"030-0074321",
                      "fax":"030-0076545",
                      "submits":[ ]
                     }
      }
)

The present document shows that it is possible to insert many documents into the same collection with a single call to insert() methods. Two documents are inserted into a collection "department".

Please note that it is possible to insert into the same collection several documents that he different internal structure (a different JSON schema). In the present example one of the documents contains information about a customer and the other one information about supplier. A description of an instance of an object is assigned to a key that represent the respective class of objects. It allows to group the documents that are frequently retrieved together into the same collection of documents and because that to the adjacent data blocks in a persistent storage. it speed up a sequential search through such collection of documents.

Inserting documents

It is possible to insert many documents in one call

db.department.insert(
    [ {"_id":"ANATR",
       "CUSTOMER":{
	           "customer code":"ANATR",
	           "company name":"Ana Trujillo Emparedados y helados",
	           "contact name":"Ana Trujillo",
	           "contact title":"Owner",
                   "submits":[ ]
	          }
      },	     
      {"_id":"Aux joyeux ecclesiastiques",
       "SUPPLIER":{
                   "company name":"Aux joyeux ecclesiastiques",
                   "contact name":"Guylene Nodier",
	           "contact title":"Sales Manager",
	           "supplies": [ ]
                  }
      }	     
    ]
)

MongoDB imposes the following restruction on insertions of the documents.

1 It is impossible to insert two documents with the same "_id" into the same collection, each document insert into a collection must have a unique value associated with "_id" key. such restruction is enforce by automatic creation of a unique index on the values associated with "_id" key.

2 Insertion of many documents with one call can be done only into one collection. However, it is still possible to insert into one collections many documents that have heterogeneous structures.

3 No more than 48 Mb can be inserted with one insert.

4 If insertion of many documents with one call fails then entire operation is not rolled back (partial insertion). it means that some of the documents are inserted into a collection and some of the documents are not inserted into a collection. Apart from search over a value associated with a key "_id" the system does not provide any information which documents are inserted and whiich documents are not inserted. A document must be smaller than 16Mb.

Inserting documents

Restrictions on insertions:
- It is impossible to insert two documents with the same "_id" into the same collection
- Insertion of many documents with one call can be done only into one collection
- There is a limit on much data can be inserted in one inserts (48Mb)
- If insertion of many documents with one call fails then entire operation is not rolled back (partial insertion)
- An inserted document must be smaller than 16Mb

MongoDB Data Manipluations

Outline

A sample database
Inserting documents
Removing documents
Updating documents

A method remove() can be used to remove the documents from a collection.

db.orders.remove({})

The first example show how to remove all documents from a collection "orders". An argument of remove() method is an empty BSON document. Interpretation is the same as fro find() method. An empty document means no restriction imposed on a collection of document operated by a method. Hence al documents are removed from a collection.

The next example shows ho to remove all documenst that contain information about customers.

db.orders.remove({"CUSTOMER":{$exists:true}})

If a document has a key "CUSTOMER" associated with a subdocument then it means that such document represents an instance of object from a class CUSTOMER.

In the next example, we remove from a collection "orders" all suppliers who supply no products.

db.orders.remove({"SUPPLIER.supplies":[]})

The documents that contain information about suppliers with no product supplies must have a path "SUPPLIER.supplies" included.

The next example shows how to drop entire collection "orders".

db.orders.drop()

A difference between deleting all documents from a collection and dropping of a collection is such that after delete we still insert more4 documents into a collection without re-creating it. When a collection is dropped then to insert a new document a collection must be re-created first. It is the same as a difference between DELETE TABLE and DROP TABLE in SQL.

Removing documents

A method remove() can be used to remove the documents from a collection
Remove all documents from a collection orders

Remove all customers from a collection orders

  
db.orders.remove({"CUSTOMER":{$exists:true}})

Remove from a collection orders all suppliers who supply no products
Drop a collection orders

MongoDB: Data Manipluationss

Outline

A sample database
Inserting documents
Removing documents
Updating documents

Updating documents

Replace a document that describes a customer with customer code ALFKI with a new document

db.orders.update({"CUSTOMER.customer code":"ALFKI"},
   	         {"_id":"ALFKI",
                  "CUSTOMER":{"customer code" :"ALFKI",
                              "company name":"Alfreds Futterkiste",
                              "contact name":"James Bond",
                              "contact title":"Sales Representative",
                              "address":"Johan Strauss Str. 23",
                              "city":"Hamburg",
                              "region":null,
                              "postal code":"34567",
                              "country":"Germany",
                              "phone":"030-0074321",
                              "fax":"030-0076545",
                              "submits":[ ]
                             }
                 }
)

The first example in the present slide shows how to rename a key "contact title" into "title" in all documents that contain information about customers.

db.orders.updateMany({"CUSTOMER":{$exists:true}}, {$rename:{"CUSTOMER.contact title":"CUSTOMER.title"}})

The first argument is a query template that find all documents with a path "CUSTOMER". The second argument uses an operator $rename to place a path "CUSTOMER.contacttitle" with a path "CUSTOMER. title".

The next example shows how to change a value associated with a path. The example changes a name of city to Liverpool in a document describing a customer who has customer code AROUT

db.orders.updateOne({"CUSTOMER.customer code":"AROUT"}, {$set:{"CUSTOMER.city":"Liverpool"}})

The first argument is a query template that finds all documents with a value "AROUT" at the end of a path "CUSTOMER.customer code". A $set operator replaces a value at the end of a path "CUSTOMER.city" with a new value "Liverpool".

The next example shows how to change a name of city to null in a document describing a customer who has a customer code "AROUT".

db.orders.updateOne({"CUSTOMER.customer code":"AROUT"}, {$set:{"CUSTOMER.city":null}})

The first argument is the same as in the previous example, i.e. a path "CUSTOMER.customer code" associated with a value "AROUT" at its end. The second argument applies $set operator to replace a value at the end of path "CUSTOMER.city" with nothing (null).

The last example show how to remove a key "city" from a document describing a customer who has a customer code "AROUT".

db.orders.updateOne({"CUSTOMER.customer code":"AROUT"}, {$unset:{"CUSTOMER.city":null}}))

The first argument is the same as in the previous example, i.e. a path "CUSTOMER.customer code" associated with a value "AROUT" at its end. The second argument applies $unset operator to remove "city":value pair at the end of a path "CUSTOMER.city".

A pair of operators $set and $unset can be used to create/change/remove a value associated with a given key.

Updating documents

Rename a key contact title into title in all documents describing customers

db.orders.updateMany({"CUSTOMER":{$exists:true}},
                     {$rename:{"CUSTOMER.contact title":"CUSTOMER.title"}})

Change a name of city to Liverpool in a document describing a customer who has customer code AROUT

db.orders.updateOne({"CUSTOMER.customer code":"AROUT"},
                    {$set:{"CUSTOMER.city":"Liverpool"}})

Change a name of city to null in a document describing a customer who has a customer code AROUT

db.orders.updateOne({"CUSTOMER.customer code":"AROUT"},
                    {$set:{"CUSTOMER.city":null}})

Remove a key city from a document describing a customer who has a customer code AROUT

db.orders.updateOne({"CUSTOMER.customer code":"AROUT"},
                    {$unset:{"CUSTOMER.city":null}}))

The first example on the present slide shows how to change a unit price of a product Guarana Fantastica to 10.

Like before update() method needs two arguments. The first is a query template that determines the documents to be updated. Application of a query template find all documents that have a value "Guarana Fantastica" assigned to a path "SUPPLIER.supplies.PRODUCT.product name". Then, operator $set changes a value assigned to a path {"SUPPLIER.supplies.$.PRODUCT.unit price" to 100. A sign $ used in a path "SUPPLIER.supplies.$.PRODUCT.unit price" means that any subdocument (representing any product) in an array associated with a path "SUPPLIER.supplies" can be used for a modification.

The second example removes value of a unit price of a product Guarana Fantastica. A missing value is represented by a symbol null.

db.orders.updateOne({"SUPPLIER.supplies.PRODUCT.product name":"Guarana Fantastica"}, {$set:{"SUPPLIER.supplies.$.PRODUCT.unit price":null}}))

A method update() is used in the same way as before. The only difference is application of a symbol null in order o eliminate a value associated with a path "SUPPLIER.supplies.$.PRODUCT.unit price".

In the third example we Increment a unit price of a product Guarana Fantastica by 10.

db.orders.updateOne({"SUPPLIER.supplies.PRODUCT.product name":"Guarana Fantastica"}, {$inc:{"SUPPLIER.supplies.$.PRODUCT.unit price":10}})

A method update() is used in the same way as before. This time instead of $set operator we use $inc operator that increases a value associated with a path "SUPPLIER.supplies.$.PRODUCT.unit price" by 10.

In the last example we multiply a unit price of a product Guarana Fantastica by 2.

db.orders.updateOne({"SUPPLIER.supplies.PRODUCT.product name":"Guarana Fantastica"}, {$mul:{"SUPPLIER.supplies.$.PRODUCT.unit price":2}})

A method update() is used in the same way as in the previous example This time instead of $inc operator we use $mul operator that multiplies a value associated with a path "SUPPLIER.supplies.$.PRODUCT.unit price" by 2.

Updating documents

Change a unit price of a product Guarana Fantastica to 10

  
db.orders.updateOne({"SUPPLIER.supplies.PRODUCT.product name":"Guarana Fantastica"},
                    {$set:{"SUPPLIER.supplies.$.PRODUCT.unit price":100}}))

Set a value of a unit price of a product Guarana Fantastica to null

  
db.orders.updateOne({"SUPPLIER.supplies.PRODUCT.product name":"Guarana Fantastica"},
                    {$set:{"SUPPLIER.supplies.$.PRODUCT.unit price":null}}))

Increment a unit price of a product Guarana Fantastica by 10

  
db.orders.updateOne({"SUPPLIER.supplies.PRODUCT.product name":"Guarana Fantastica"},
                    {$inc:{"SUPPLIER.supplies.$.PRODUCT.unit price":10}})

Multiply a unit price of a product Guarana Fantastica by 2

 
 db.orders.updateOne({"SUPPLIER.supplies.PRODUCT.product name":"Guarana Fantastica"},
                     {$mul:{"SUPPLIER.supplies.$.PRODUCT.unit price":2}})

In the first example we apply a method update() to remove a key "unit price" at the end of a path "SUPPLIER.supplies.$.PRODUCT.unit price" from a description of a product Guarana Fantastica

db.orders.updateOne({"SUPPLIER.supplies.PRODUCT.product name":"Guarana Fantastica"}, {$unset:{"SUPPLIER.supplies.$.PRODUCT.unit price":null}}))

The first argument of update() method is the same as in the previous examples. To remove a key we apply an operator $unset in the second argument of update(). The operator is applied to a key at the end of a path "SUPPLIER.supplies.$.PRODUCT.unit price". null is required only to complement key:value pair being an argument of $unset.

In the second exam update() method is used to multiply a unit price of the first product supplied by a company Exotic Liquids by 2.

db.orders.updateOne({"SUPPLIER.company name":"Exotic Liquids"}, {$mul:{"SUPPLIER.supplies.0.PRODUCT.unit price":2}})

A query template "SUPPLIER.company name":"Exotic Liquids" finds a BSON document with a description of a company "Exotic Liquids". Then an operator $mul multiplies a value associated with a path "SUPPLIER.supplies.0.PRODUCT.unit price" by 2. Please note that this time we do not use $ symbol to denote any element in an array associated with a path "SUPPLIER.supplies". A specification of the update asks "to multiply a unit price of the first product supplied ...". It is why instead of $ the first element with index 0 in an array associated with a path "SUPPLIER.supplies" is used.

If we know, that the first product supplied by a company Exotic Liquids is Chai then its unit price can be multiplied by 2 in the following way

db.orders.updateOne({"SUPPLIER.supplies.PRODUCT.product name":"Chai"}, {$mul:{"SUPPLIER.supplies.$.PRODUCT.unit price":2}})

Updating documents

Remove a key unit price from a description of a product Guarana Fantastica

  
db.orders.updateOne({"SUPPLIER.supplies.PRODUCT.product name":"Guarana Fantastica"},
                    {$unset:{"SUPPLIER.supplies.$.PRODUCT.unit price":null}}))

Multiply a unit price of the first product supplied by a company Exotic Liquids by 2

 
db.orders.updateOne({"SUPPLIER.company name":"Exotic Liquids"},
                    {$mul:{"SUPPLIER.supplies.0.PRODUCT.unit price":2}})

If we know, that the first product supplied by a company Exotic Liquids is Chai then its unit price can be multiplied by 2 in the following way

 
db.orders.updateOne({"SUPPLIER.supplies.PRODUCT.product name":"Chai"},
                    {$mul:{"SUPPLIER.supplies.$.PRODUCT.unit price":2}})

In the present slide we use the operators $pull and $push to remove and to insert the elements from/into arrys.

In the first example, we would like to remove a product Chai supplied by a supplier Exotic Liquids.

db.orders.updateOne({"SUPPLIER.company name":"Exotic Liquids"}, {$pull:{"SUPPLIER.supplies":{"PRODUCT.product name":"Chai"}}})

First we find BSON document that contains all information about a company Exotic Liquids. Next, an operator $pull accesses an array associated with a path "SUPPLIER.supplies" and removes an element that has a path "PRODUCT.product name" associated with a value "Chai".

In the second example we remove all discontinued products supplied by a supplier Bigfoot Breweries

db.orders.updateMany({"SUPPLIER.company name":"Bigfoot Breweries"},{$pull:{"SUPPLIER.supplies":{"PRODUCT.discontinued":"Y"}}}))

A query template "SUPPLIER.company name":"Bigfoot Breweries" finds BSON document that contains information about a company Bigfoot Breweries. An operator $pull accesses an array associated with a path "SUPPLIER.supplies" and removes the elements that have a value "Y" associated with a path "PRODUCT.discontinued".

The last example shows how to insert a new element into an array. we add a new product Champagne to the products supplied by Exotic Liquids, the product is not discontinued and its unit price is 100.

db.orders.updateOne({"SUPPLIER.company name":"Exotic Liquids"},

{$push:{"SUPPLIER.supplies":{"PRODUCT":{"product name":"Champagne", "discontinued":"N", "unit price":100}}}}))

Like in the previous example a query template "SUPPLIER.company name":"Exotic Liquids" find s BSON document with information about a company Exotic Liquids. Then an operator $push is used to insert a new element {"PRODUCT":{"product name":"Champagne", "discontinued":"N", "unit price":100}} into an array associated with a path "SUPPLIER.supplies".

Updating documents

Remove a product Chai supplied by a supplier Exotic Liquids

 
db.orders.updateOne({"SUPPLIER.company name":"Exotic Liquids"},
                    {$pull:{"SUPPLIER.supplies":{"PRODUCT.product name":"Chai"}}})

Remove all discontinued products supplied by a supplier Bigfoot Breweries

 
db.orders.updateMany({"SUPPLIER.company name":"Bigfoot Breweries"},
                     {$pull:{"SUPPLIER.supplies":{"PRODUCT.discontinued":"Y"}}}))

Add a new product Champagne to the products supplied by Exotic Liquids, the product is not discontinued and its unit price is 100

 
db.orders.updateOne({"SUPPLIER.company name":"Exotic Liquids"},
                    {$push:{"SUPPLIER.supplies":{"PRODUCT":{"product name":"Champagne",
                                                            "discontinued":"N",
                                                            "unit price":100}}}}))

References

MongoDB Reference, Operators, Update Operators
Banker L., Bakkum P., Verch S., Garrett D., Hawkins T., MongoDB in Action, Manning, 2016
Chodorow K. MongoDB The Definitive Guide, O'Reilly, 2013, chapter 2