MONGODB Introduction & CRUD (Chapter 1 & 2)

MONGODB


This is a reference page for myself on what i learned on the MONGODB, through the mongodb Univeristy.

What causes significant problems for SQL when you attempt to scale horizontally (to multiple servers)
Joins and Queires. 

In MongoDB without those above features, they comeup with solution that "xml", But it is not an actual XML it is called "Documented Oriented" which use JSON (Java script object Notation). Also JSON is language independent. 

RFC (Request for content)

MongoDB is a document oriented database. 

Json
 {
       -id:"Q33",
      x:3,
      y:"abc",
      z:[1,2],
}

"Embedding"

The complete set of data is stored as one document. It all stored together. 

For example if you split this data in to RDBMS with the primary key column it will come like this. 

p - primary key

table    T                                          table  TZ

p        x         y                              p         z
--------------------                            ---------------
Q33   3    "abc"                            Q33     1
                                                    Q33      2

If you want to get the data on the above two tables, you have to join both the tables and give the id Q33

select * from t,tz
on t.p = tz.p
where p= "Q33"

but in Mongo just ask for the document which have the ID Q33.

db.parts.find({_id:"Q33"})

JSON and its syntax

Data Types

There are 6 datatypes in JSON

1.String
2. Number
3.booleans (true/false)
4. null
5. array
6. objects/documents

Example

"Associated array" should be string (The variable name should be)

{
"name":"moosa",
"age",35,
"voted":true
"school":null,
"likes":["tennis","math"],
"address":
{
"city":"madison",
"state":"wi"
}
}

Valid varibales

{
"#":3,
"349":false,
" ":space,
"abc":1,
"ab cd":"space in the middle"
}

Quiz

<person>
  <name>John</name>
  <age>25</age>
  <address>
    <city>New York</city>
    <postalCode>10021</postalCode>
  </address>
  <phones>
    <phone type="home">212-555-1234</phone>
    <phone type="mobile">646-555-1234</phone>
  </phones>
</person>

Change this to JSON Type
{
"name":"John",
"age":25,
"address":
{
"city":"New York",
"postalCode":"10021"
},
"phones":
[
{"phone":"212-555-1234","type":"home"},
{"phone":"646-555-1234","type":"mobile"}
]

}

or the last statement we can use 

{
phones:
{
"home":"212-555-1234"
"mobile":"646-555-1234"
}
}

JSON SYNTAX 2

For the following XML, Is the corresponding JSON example legal json?

<things>
  <hat>one</hat>
  <coat>z</coat>
  <hat>two</hat>
</things>

{
  "hat" : "one",
  "coat" : "z",
  "hat" : "two"
}

Answer : Maybe
Becuase the "hat" has repeated twice and should not be repeat, it will allow in mongodb but itis not a best practise. It has go through in a array field. 

The better solution is array
Correct way to do it. 

{
"hats":["one","two"]
}

BSON - Binary JSON 

--Reference http://bsonspec.org/

Way to represent binary code of JSON is called BSON.
It is for efficiency

Advantage
 -Fast Scanability 
- Data Types
      1. Date Datatypes
      2. Bin Data (used to store big object, like image, video and uuid)
      3.objectID - less common - little more comact

also have _ID datatype, it is the primary key for every document. The value must be unique, but it can be string or int. 

Fast scanabiltiy is used to skip some object, when you do search. 

BSON FOrmat

#inc is a increment variable, if you use this you don't have to adjust the size. 

QUIZ: BINARY JSON

Why do we represent our data as BSON rather than JSON in the system?

Fast machine scanability.Human readability.Stronger typing (and more types) than JSON

BSON and application

The below image will show how the data is transfer to the mongodb server and the client app also the convert. 
Also the serialization format on BSON. 
Two of them are common, one is protocol buffers and THRIFT. 

QUIZ: BSON AND APPLICATIONS

For a typical client (a python client, for example) that is receiving the results of a query in BSON, would we convert from BSON to JSON to the client's native data structures (for example, nested dictionaries and lists in Python), or would we convert from BSON straight to those native data structures?

BSON -> Native data structuresBSON -> JSON -> Native data structures

Dynamic Schema 
MongoDB documents(tables) are "schema less" - which is called Dynamic Schema.
You can add column and value any time, without altering the table(document)

QUIZ: DYNAMIC SCHEMA

True or False: MongoDB is schemaless because a schema isn't very important in MongoDB

Answer : False

Mongo SHell

QUIZ: WHAT IS THE MONGODB SHELL?

Q: By default, which database does the mongo shell connect to?

testlocalhostmongohelpdefault

MONGO IMport

The mongoimport utility can import what types of data?

JSONCSVBSONTSVXML

connect to mongo for db pcat

$mongo localhost/pcat
>
>dbpcat

will show current db

>Show collections

product
products
system.indexes

Collection will show all the available collectoins (Tables).

To view the data in the collections
>db.product.find()

show all records
It will return 10 rows at a time. If you want more result. You have to use IT

>it
no cursor

No of records count

>db.products.count()
11 

toArray() - will show the result in Array format

limit - limit the output when it shows. 

>db.products.find({brand:"ACME"}).limit(1).toArray()
[

{

"_id" :"ac3",

"name" :"ac3 phone",

...

...

...

}

]

findone() - will show the first match of the result .

>db.products.findOne()

[

{

"_id" :"ac3",

"name" :"ac3 phone",

...

...

...

}

]

If it doesn't have any result. it returns null. 

find will return nothing. 

>db.users.findOne()

null

>db.users.find()

>

skip is to skip the first output line. Here it will skip one output. 

>db.users.find().limit(1).skip(1)

[

{

"_id" :"ac3",

"name" :"ac3 phone",

...

...

...

}

]

second section find is use to select the column with value 1. to eliminate _id is by 0. _id will be default

>db.users.find({},{name:1,brand:1,_id:0})

QUIZ: QUERY LANGUAGE: PROJECTION

You want to query the “people” collection, you want the results of the query to include only documents where age is 50, and you want to look at all fields except “email”. What query should you write?

db.people.find( { email : 0, age : 50 } , { } )db.people.find( { email : 0 }, { age : 50 } )db.people.find( { age : 50 } , {email: 0 } )db.people.find( { } , { email : 0 , age : 50 } )db.people.find( { age : 50 }, {email : 1} )

QUERY LANGUAGE: ADVANTAGES OF A DYNAMIC SCHEMA

If you want to add a new key: value pair to the documents in the “shapes” collection, what methods could you use?

db.shapes.alterCollection()db.shapes.update()db.shapes.resizeObject()db.shapes.find()db.shapes.save()

Exisits is used to find is the column is exists

>db.users.find({for:{$exists:true}},{name:1,brand:1,_id:0})

{name:ac3.......

......

...

}

find greater then value

>db.products.find({price:{$gte:200}})

SHELL: QUERIES

We have sample documents in our products collection such as:

{
  name: "AC1 Case Green",
  color: "green",
  price: 12.00,
  for: "ac1",
  type: ["accessory", "case"],
  available: true
}

How would we query in the shell for all products that are cases for an ac9 phone? That is, wheretype contains the value "case" and for equals "ac9"?

Please use field names in the order they appear in the question and use double quotes around values.

1

db.products.find({type:"case",for:"ac9"})

Home Work 

HW 1.1

Download and install MongoDB from www.mongodb.org. Then run the database as a single server instance on your PC (that is, run the mongod binary). Then, run the administrative shell.

From the shell prompt type

 db.isMaster().maxBsonObjectSize

at the ">" prompt.

What do you get as a result?

16777216

certa

HW 1.2

Download the file products.json from education.mongodb.com. Take a look at its content.

Now, import its contents into MongoDB, into a database called "pcat" and a collection called "products". Use the mongoimport utility to do this.

When done, run this query in the mongo shell:

db.products.find({type:"case"}).count()

mongoimport --host 192.168.50.5 -d pcat -c products < products.json

connected to: 192.168.50.5

Fri Dec 13 16:34:59 imported 11 objects

mongo

> use pcat

switched to db pcat

> show collections

products

system.indexes

> db.products.find({type:"case"}).count()

3

certa

HW 1.3

At this point you should have pcat.products loaded from the previous step. You can confirm this by running in the shell: 

> db.products.find()
> // or:
> db.products.count()
> // should print out "11"

Now, what query would you run to get all the products where brand equals the string “ACME”? 
db.products.find({"brand": "ACME"})

certa

Homework: Homework 1.4

How would you print out, in the shell, the name of all the products without extraneous characters or braces, sorted alphabetically, ascending? (Check all that would apply.) 

db.products.find({},{name:1,_id:0}).sort({name:1})
 var c = db.products.find({},{name:1,_id:0}).sort({name:1}); while( c.hasNext() ) print( c.next().name);
var c = db.products.find({}).sort({name:1}); c.forEach( function(doc){ print(doc.name) } );
var c = db.products.find({}).sort({name:-1}); while( c.hasNext() ) print( c.next().name);

Week 2:


create database called create_lesson_db

inside the db you insert the data

db location in LInux

/var/lib/mongodb/> ls -l

INsert
>db.sample.insert({x:1,y:2})

>db.sample.insert({"A":99,somedate:22.7})
>db.sample.find()

Update:
update mean update one or more documents in a collection

1. Full document update - update the full collection, new copy of the document
2. Partial update - means update one document

<Upsert> - update/insert if not present
<Multi> - update multiple documnet on the collection where ever it matched with where condition. 

>db.samples.update({x:100}.{x:100,y:101})  -

if you dont' specify x:100 it will remove that value

$Pushis to push a value to a array. It will add a value to an array

It will add a new value if you add twice with the same value. this will elimnate by using $addToSet

> m.update({_id:"jane"},{$push:{likes:"football"}})

$addToSet

> m.update({_id:"jane"},{$addToSet:{likes:"football"}})

$set

 $set operator will add the field with the specified value

>t.update({_id:100},{$set:{y:100}})

$inc

• the $inc operator to increment the stock field; and

>t.update({_id:100},{$inc:{y:100}})

now the y value value will be 200 because the 100 added to the old value 100.

QUIZ: UPDATE

Check all that are true about the _id field:

It must be of type ObjectId()It must exist in every documentIt can be changedIt must be unique inside the collectionIt is automatically indexed

DOCUMENT GROWTH/RELOCATION

What operations could cause an existing document to be moved on disk? Check all that apply.

Adding items to an arrayRemoving a fieldChanging a fields valueAdding new fields to an existing documentInserting a sub-document into an existing document

SAVE() COMMAND

What happens if you try to use db.collection.save(document) if the inserted document has no _id?

It will assign the document a new _id of the type already used in the table and insert it.It will assign the document an existing _id field at random and overwrite that existing document.It will insert the document with no _id.It will infer on its own what you meant and do that.It will assign the document an objectID for its _id field, and then insert it.It will throw an exception, as it cannot update a document that does not exist in the collection.

Upsert

Update are insert when the value is not present. 

1. > db.collection.update( query_document , update_document , [ upsert_boolean , [ multi_boolean ] ] )

   This is no longer the recommended format.

   We now recommend:

   > db.collection.update( query_document, update_document, options_document )
   

   where options_document contains key:value pairs such as:

   multi : true/false, 
   upsert : true/false,
   writeConcern: document

Write protocol

CURD - Create, update,read write

commands are used to run the query. Which start with $ symbol

there are 50 commands. 5 are important , 20 are used often

QUIZ: WIRE PROTOCOL

What are the basic building blocks of the wire protocol? Check all that apply.

GetMoreInsertQueryRemoveUpdateCommand

Which operation is overloaded in the wire protocol to handle commands?

GetMoreRemoveQueryInsertUpdate

Bulk operations and Methods

Ordered - Do the insert in ordered

UnOrdered -   Do the insert in the different order and do a parallel insert of multiple document.

> var bulk = db.items.initalizeUnorderedBulkOp();var bulk = db.items.initializeUnorderedBulkOp() Wed Apr 23 14:01:40.284 TypeError: Property 'initializeUnorderedBulkOp' of object MyDB.test is not a function
Got this error becuase of the version. The above is available after the version 2.6.0. When i ran the above command my current version was 2.4.6. After upgrading my mongo to 2.6.5, it started working. 

UPGRADE MONGODB in UBUNTU Linux

Reference

https://www.digitalocean.com/community/tutorials/how-to-install-mongodb-on-ubuntu-12-04

1. step link the ubunto on the Sudo to do the installation. i have chnaged the 10gen to -org to update the 2.6.X

apt-key adv --keyserver keyserver.ubuntu.com --recv 7F0CEB10
echo "deb http://downloads-distro.mongodb.org/repo/ubuntu-upstart dist 10gen" | tee -a /etc/apt/sources.list.d/10gen.list
apt-get -y update
apt-get -y install mongodb-org

To check version

>version()

2.6.5

> var bulk = db.items.initalizeUnorderedBulkOp();

>db.items.count()

0

>bulk.insert({_id:1,item:"abc1",Qty:100,status:"available"})

>bulk.insert({_id:2,item:"abc2",Qty:50,status:"available"})

>bulk.insert({_id:3,item:"abc3",Qty:10,status:"available"})

>db.items.count()

0

>bulk.execute()

>db.items.count()

3

> var b = db.items.initalizeOrderedBulkOp();

>b.find({item:"abc1"}).remove()

>b.find({item:"abc2"}).update({$inc:{Qty:50}})

>b.execute()

>db.items.find()

>{_id:2,item:"abc2",Qty:100,status:"available"})

>{_id:3,item:"abc3",Qty:10,status:"available"})

Bulk() insert reference

http://docs.mongodb.org/manual/reference/method/Bulk/

Commands

Command reference page:
http://docs.mongodb.org/manual/reference/command/distinct/#dbcmd.distinct

to run a command in MongoDB you have to use

db.runCommand({<commandname>:<val>,p1:val,p2:val})

Which of the following are user commands (as opposed to admin commands)? Check all that apply.

countaggregatedropMapReduceaddShardFindAndModifyserverStatus

Example

db.runCommand({getLastError:1})

When we execute an operation through the shell or another interface, we can use which of the following?

db.teaEarlGreyHot()db.engage()db.runCommand()db.makeItSo()

Some IMportant commands

1. ensureIndex
2. dropIndex
3. CurrentOp - Find the current active process
4. killOp - kill the process

to get the list of commands

Use either help or db.help()

>db.isMaster()
to know the current node is master, or secondary or slave

if you get value as true then it is master

or

>db.runCommand("isMaster")


What does the db.isMaster() command do?

Determine your level of mastery of the MongoDB shell.Determine if this shard is the Primary shard.Determine if this member of the replica set is the Primary.Determine if the database has Master-Slave replication

object name without () will show example on how to run

>db.serverStatus()
give lot of information about the server status. It is used by MMS( Mongo Monitorying Services)
ServerStatus will provide the result of mms in readable format

When running the command db.serverStatus() in the shell, what does the “ok” field represent?

That the command was sent and received properly by the mongodThat the master db process is okThat the shell is running properlyThat the daemon to which you are connected is okThat the daemon has master status

If you’re looking for problems with database performance, what is a good place to look, when you run db.currentOp()?

“ok”: false“secs_running”: (look for long times)“secs_running”: (look for short times)"opid", because you always want to killOp() everything to clean things up."opid": (something that is not sequential with the rest)

Index

ensureIndex 
This is used to create a index

>db.products.ensureIndex({name:1})1 is used to create index in ascending order : -1 is used for descending order
This is creating a index on the name column in the products collecions

To view the Query Plan

>db.products.find({name:"Ac3 Case Red"}).explain()

explain() -  will show the query plan

>db.products.getIndexes()

or

>db.system.indexes.find()

This will show the indexes in the products collection. By default it will have index on the _id column also shows index on the name column.


Dropping the index

>db.products.dropIndex({name:1})

this will drop the index name on the products collections

What will happen if an index is created on a field that does not exist in any of the documents in the collection?

MongoDB will throw an error and not create the indexMongoDB will not create the index and not throw an errorMongoDB will create the index but give a warningMongoDB will create the index but give no feedbackMongoDB will ask if you're sure first

Status 
>db.products.stats()

this will show the statistic of a colleciton. 
LIke, size, rows count, index size, etc..

>db.products.remove({}) - Delete all rows in the products collection. But not the collectoins

>db.system.namespaces.find()
this is equvalaent to a table in the sql server to find a object. select * from sysobjects

drop the collection

>db.products.drop()

list datbases - 

like sp_helpdb - >show dbs
or 

>db.runCommand({ListDatabases:1}) - like select * from sysdatabases

this has to run the admin dbs. 

True or false: db.collection.remove({}), which removes all messages in a collection, is the same as db.collection.drop(), which drops the collection.

TrueFalse

Command reference

Which of these statements is true?

MongoDB commands are for administrator use only.In MongoDB collections are usually created implicitly.In MongoDB indexes are usually created implicitly.

Homework 2

---- 2.2
	var prod1 = db.products.findOne({_id:ObjectId("507d95d5719dbef170f15c00")})
	prod1.term_years = 3
	db.products.update({_id:prod1._id},prod1)
	prod1.limits.sms.over_rate = 0.01
	db.products.update({_id:prod1._id},prod1)
	> homework.b()
	0.050.019031
	---- 2.3
	> db.products.find({"limits.voice":{$exists:1}}).count()
	3
	---- 2.4
	> db.products.ensureIndex({for:1})
	Q1 : 4
	Q2 : 4
	Q3 : YES
	---- 2.5
	// Trouver les items et boucler dessus
	> db.products.find({for:{$exists:1},for:"ac3"}).forEach( function(item) { printjson(item) } )
	// Réaliser l'update
	> db.products.find({for:{$exists:1},for:"ac3"}).forEach( function(item) { db.products.update({_id:item._id},{$inc:{price:2}}) } )
	> homework.c()
	89.5954.5