MongoDB for DBA's 2/7. Crud. Homeworks

Homework 2.1

We will use the pcat.products collection from week 1. So start with that; if not already set up, import it:

mongoimport --db pcat -c products < products.json

You can find products.json from the Download Handouts link. In the shell, if you type:

db.products.count()

should return 11.

Next, download homework2.js from the Download Handouts link. Run the shell with this script:

mongo --shell pcat homework2.js

First, make a mini-backup of the collection before we start modifying it. In the shell:

b = db.products_bak; db.products.find().forEach( function(o){ b.insert(o) } )
 // check it worked: 
b.count()
// should print 11

If you have any issues you can restore from "products_bak"; or, you can re-import with mongoimport. (You would perhaps need in that situation to empty the collection first or drop it; see the --drop option on mongoimport --help.) At the shell ">" prompt type:

homework.a()

What is the output? (The above will check that products_bak is populated.)

3.05

Homework 2.2

Add a new product to the products collection of this form:

{
 "_id" : "ac9",
 "name" : "AC9 Phone",
 "brand" : "ACME",
 "type" : "phone",
 "price" : 333,
 "warranty_years" : 0.25,
 "available" : true
}

Note: in general because of the automatic line continuation in the shell, you can cut/paste in the above and shouldn't have to type it all out. Just enclose it in the proper statement(s) to get it added.

> myobj = { "_id" : "ac9", "name" : "AC9 Phone", "brand" : "ACME", "type" : "phone", "price" : 333, "warranty_years" : 0.25, "available" : true }

> db.products.insert(myobj)

WriteResult({ "nInserted" : 1 })

Next, load into a shell variable the object corresponding to

_id : ObjectId("507d95d5719dbef170f15c00")

bb = db.products.find({"_id": ObjectId("507d95d5719dbef170f15c00")})

• Then change term_years to 3 for that document. (And save that to the database.)

db.products.update({"_id":ObjectId("507d95d5719dbef170f15c00")},
                                   {"$set":{"term_years":3}}
                                   )

• Then change over_rate for sms in limits to 0.01 from 0. Save that too.

db.products.update({"_id":ObjectId("507d95d5719dbef170f15c00")},
                                    {"$set":{"limits.sms.over_rate":0.01}}
                                   )


At the shell prompt type:

homework.b()

What is the output?

0.050.019031

Homework 2.3

How many products have a voice limit? (That is, have a voice field present in the limits array.)

db.products.find({ "limits" : { "$exists" : "voice" }}).count()

3

Homework 2.4

Create an index on the field for. You might want to first run the following to get some context on what is present in that field in the documents of our collection:

db.products.find({},{for:1})

db.products.ensureIndex({"for":1},{name:"for"})
db.products.getIndexes()
[
{
"v" : 1,
"key" : {
"_id" : 1
},
"name" : "_id_",
"ns" : "pcat.products"
},
{
"v" : 1,
"key" : {
"for" : 1
},
"name" : "for",
"ns" : "pcat.products"
}

]

After creating the index, query products that work with an "ac3" phone; that is, "ac3" is present in the product's "for" field.

• Q1: How many are there?
•  db.products.find({"for":"ac3"}).count()
• Q2: Run an explain plan on the above query. How many records were scanned?
•  db.products.find({"for":"ac3"}).explain()
• {
  "cursor" : "BtreeCursor for",
  "isMultiKey" : true,
  "n" : 4,
  "nscannedObjects" : 4,
  "nscanned" : 4,
  "nscannedObjectsAllPlans" : 4,
  "nscannedAllPlans" : 4,
  "scanAndOrder" : false,
  "indexOnly" : false,
  "nYields" : 0,
  "nChunkSkips" : 0,
  "millis" : 0,
  "indexBounds" : {
  "for" : [
  [
  "ac3",
  "ac3"
  ]
  ]
  },
  "server" : "SERVER:27017",
  "filterSet" : false
  
  }
• Q3: Was an index used?
• "cursor" : "BtreeCursor for",

Q1: 0Q1: 1Q1: 3Q1 : 4Q2 : 1Q2 : 4Q2 : 5Q2 : 12Q3 : NoQ3 : Yes

Homework 2.5

Referring back to 2.4 above, update those products (products that work with an "ac3" phone) and add 2 to the "price" of each of those items.

db.products.find({"for":"ac3"})

db.products.update( {"for":"ac3"}, { "$inc" : { "price": 2}}, {"multi" : 1})

89.5954.5