Objective:
I
recently helped our business and development team to get up to speed with basic
query operation and thought share the same with the community. Some of
the information I pulled from mongo db site(there is no better place than the
source).
What is Mongo DB
MongoDB is a Document type NoSQL database,
NoSQL = Not Only SQL
MongoDB has document oriented storage, dynamic schemas
and data is stored as JSON-style documents. In No-SQL world you do not have to
stick to a schema as with relational databases.
Two documents (rows) in the same collection (table)
can have different schemas.
DB/Scripting Language= JavaScript
Reference to RDBMS DB terms
|
SQL Terms
|
MongoDB Concepts
|
|
database
|
|
|
table
|
|
|
row
|
|
|
column
|
|
|
index
|
|
|
table
joins
|
embedded
documents and linking
|
|
primary key
Specify any unique column or column
combination as primary key.
|
|
|
aggregation
(e.g. group by)
|
aggregation pipeline
|
|
SQL Operators
|
MongoDB Aggregation Operators
|
|
WHERE
|
|
|
GROUP
BY
|
|
|
HAVING
|
|
|
SELECT
|
|
|
ORDER
BY
|
|
|
LIMIT
|
|
|
SUM()
|
|
|
COUNT()
|
|
|
join
|
No direct corresponding operator; however, the $unwind operator allows for somewhat similar
functionality, but with fields embedded within the document.
|
*** Source: MongoDB website
Important data structure :
JSON/BSON:
JSON (Java Script Object Notation):
JSON is a lightweight data-interchange format, which
is completely independent from programming languages. JSON primarily
works with two different structured data:
A collection of name/value pairs.
An ordered list of values.
To learn more about JSON, visit : http://www.json.org/
BSON (Binary Java Script Object Notation):
MongoDB doesn't actually use JSON to store the data;
rather, it uses an open data format developed by the MongoDB team called
BSON. BSON makes MongoDB even faster by making it much easier for a
computer to process and search documents. BSON also adds a couple of features
that aren't available in standard JSON, including the ability to add types for
handling binary data.
CRUD OPERATION
Before we start working with the Mongo DB, we should take a
moment to talk about the case-sensitive rules for SQL commands. Mongo
uses camel case.
Before we dive in to CRUD operation with example,
there are few important commands to do the basic operations like Listing all
databases, connecting to database, listing all documents, etc...
|
use
|
To
switch to a db library
|
|
show
dbs
|
To
view available databases and collections
|
|
show
collections
|
To
view all available collections for your current database
|
|
system.indexes
|
All
the indexed are stored here
|
CREATE operation
|
RDBMS:
INSERT INTO crew(name, age, job, email,
technology) values ("Sreenath Gotur", 40 , "Technology",
"sreenathgotur@gmail.com",
"Mongo,
C, Java, Oracle, MEAN, Python");
//Array
values need to be stored in a different table or stored with a separator in
RDBMS
|
|
MONGO:
You can Use insert or save method to insert
a record
***Creating a
simple document***
db.crew.insert(
{
name:"Jack Bauer",
age: 24,
job: "CTU",
}
);
***Creating a
document with a array***
db.crew.insert(
{
name:"Sreenath Gotur",
age: 40,
job: "Technology",
technology: ["Mongo", "C++",
"Java", "Oracle", "MEAN", "ROR",
"Python", "DotNet"]
}
);
db.crew.save(
{
name:"Tony Almeida",
age: 41,
job: "Technology",
technology: ["Java", "C#", "J2EE",
"Oracle", "SQL Server", "etc"]
}
);
***Creating a
document with a array and an embedded document***
**** Note: in
RDBMS, this is done using two different tables and queried by joining two
tables***
db.crew.insert(
{
name:"David Palmer",
age: 25,
job: "Technology",
technology: ["BA", "C#",
"Ms-Office", "Oracle", "SQL Server",
"etc"],
address:[
{address1: "525, North Tryon Street", address2:
"ODell Building", city:"Charlotte", state:"NC",
zip:"28255", type: "work"},
{address1: "100, North Tryon Street", address2:
"Corporate Tower", city:"Charlotte",
state:"NC", zip:"28255", type: "Home"}
]
}
);
|
READ Operation:
|
RDBMS: Select * from crew
|
|
MONGO:
db.crew.find();
db.crew.find().pretty();
// Pretty looking document... :-) it will display record in Tree view
structure
|
|
RDBMS: Select * from crew where
name="Sreenath Gotur"
|
|
MONGO:
db.crew.find({"name"
:"Sreenath Gotur"}).pretty();
or
db.crew.find({name
:"Sreenath Gotur"}).pretty();
|
|
RDBMS: Select name, age, job from crew where
name="Sreenath Gotur"
|
|
MONGO:
db.crew.find({"name"
:"Sreenath Gotur"}, {name:1, age:1, job:1}).pretty();
*** By Default, _id field will be
returned and to supress the "_id" field from the results ***
db.crew.find({"name"
:"Sreenath Gotur"}, {name:1, age:1, job:1, _id:0}).pretty();
*** to display the embedded
document***
db.crew.find({"name"
:"Sreenath Gotur"}, {name:1, age:1,
job:1,"address.city":1, _id:0}).pretty();
*** filter by sub document***
db.crew.find({"address.city":"Charlotte"},
{name:1, age:1, job:1,_id:0}).pretty();
|
|
RDBMS: Select * from crew where
age > 40
|
|
db.crew.find(
{ age: { $gt: 40 } } ).pretty()
|
|
RDBMS: Select * from crew where age >= 40
|
|
db.crew.find(
{ age: { $gte: 40 } } ).pretty()
|
UPDATE Operation
|
RDBMS: Update crew set age=42 where name="Sreenath
Gotur"
|
|
db.crew.update({
"name" :"Sreenath Gotur"}, {$set: {"age":
"42"}});
|
|
*** Update as well
as create a new field(column), which is impossible in RDBMS without touching the
database schema***
db.crew.update({
"name" :"Sreenath Gotur"}, {$set: {"age":
"42","sex": "Male"}});
|
|
*** update embedded document value
***
db.crew.update({
"name" :"David Palmer"}, {$set: {"age":
"21","sex":
"Male","address":{state" : "NH"}}});
|
|
*** If no record found mongo won't
create or update a record ***
db.crew.update(
{
"name" :"Michelle Dessler"},
{$set:
{
name:"Michelle Dessler",
age: 42,
job: "Technology",
technology: ["ASP.net", "C#",
"J2EE", "Oracle", "SQL Server",
"etc"]
}
}
);
|
|
*** UPSERT, use Upsert to insert a
record if no matching record found for update ***
** Basically Update if one found
else create a record ***
db.crew.update(
{
name :"Michelle Dessler"}, {$set: {name:"Michelle
Dessler",
age: 42,
job: "Technology",
technology: ["ASP.net", "C#",
"J2EE", "Oracle", "SQL Server",
"etc"]
}
},
{ upsert: true }
);
|
DELETE Operation
*** Delete the
entire table/collection****
|
RDBMS: Delete
from crew;
|
|
db.crew.remove({})
|
*** Delete records
from a table based on matching condition ***
|
RDBMS: Delete
from crew where job =
"Technology";
|
|
db.crew.remove({job:
"Technology"})
|
