Click here to Skip to main content
15,867,568 members
Articles / Database Development / MongoDB

Mongo DB Tutorial and Mapping of SQL and Mongo DB Query

Rate me:
Please Sign up or sign in to vote.
4.97/5 (63 votes)
18 Mar 2021CPOL8 min read 308K   190   81   17
A tutorial for MongoDB Tutorial
This article is the first article of a three-part MongoDB tutorial series.

Introduction

Welcome to the MongoDB Tutorial. This is the first part in the MongoDB article series.

Below are the links to all the three parts:

  1. Mongo DB Tutorial and Mapping of SQL and Mongo DB Query
  2. MongoDB Tutorial - Day 2
  3. MongoDB Tutorial - Day 3 (Performance - Indexing)

In the first part and as we are new to MongoDB and No-SQL, I would like to start with the definition of No-SQL.

No-SQL: No-SQL stands for Not Only SQL. No-SQL is a not relational based database.No-SQL databases does not follow the rules of RDMS and No-SQL databases does not use SQL to query the data.

No-SQL databases can be divided in to four categories:

  1. Document Store
  2. Graph Store
  3. Column Value Store
  4. Key-Value Store

MongoDB comes under Document Store database.

Document Store Databases: In Document Store Database, Data stored in form of documents. It extends the Key value Store Database Concept.

Mongo DB and Couch DB are two main Document Store Databases. Mongo DB stores data in form of Documents. Here is an example of a sample document.

Image 1

Set Up Mongo DB on Machine

Download MongoDB from the below MongoDB official Site (Download MSI file as per your Operating System).

Install MSI file (In case of Window Operation System, default location is (C:\Program Files\MongoDB).

Copy this folder and place this in your C Drive or any other drive and create a folder in C drive named data and a folder named db inside data folder.

So in C drive, we have two folders now:

  1. MongoDB (copied from Installation Location)
  2. data/db (create a folder named data and a folder named db inside data folder)

Start Mongo DB

Go to the bin folder inside MongoDB folder which we placed in c drive.

In my case, bin folder is inside MongoDB folder. (It can be inside server folder as well as below)

C:\Program Files\MongoDB\Server\3.0\bin

and double click on the Mongod.exe (It will open a command prompt and it will start the MongoDB server).

Last line should say "Waiting for connections..." as below it means our Mongo DB server has been started successfully:

Image 2

Download RoboMongo

RoboMongo. Shell-centric cross-platform open source MongoDB management tool (i.e., Admin GUI). [It] embeds the same JavaScript engine (based on Mozilla SpiderMonkey), that powers MongoDB's mongo shell. It means that you can reuse your existing skills of MongoDB Shell in Robomongo.

We can download Robomongo from Robomongo Original Site named Robomongo.org. Here is the URL:

Once download, run the EXE and go to the file-->Connect (Make sure your mongodb server is up and running which we ran in the last step through Mongod.exe)

MongoDB Terminology

Before going forward, we should know the terminology of MongoDB:

Operation In SQL In MongoDB
Create Insert Insert
Read Select Find
Update Update Update
Delete Delete Remove
Table Table Collection
Row Row Document

Key Points of MongoDB

  1. MongoDB Stores Data in Json Format (We call it BSON(Binary JSON))
  2. JSON stands for JavaScript Object Notations and looks like {“Name”:”Vijay”}
  3. JSON documents store data in Key Value Pair like {“X”:1,”Y”:2,”Z”:3}
  4. There are two basic structures inside JSON:
    1. Array: List of things is represented in List of Items [……..]
    2. Dictionaries: Associate Maps {key:Value}

      For example {Name : ‘Vijay’,City : ‘Shamli’,interest : ["Sports" ,"Music" ]}
      Name and city is dictionary and Interest is an Array.

  5. Mongo Db is Schema less, Schema Less means two documents don’t need to be same schema.
    First Document in a collection can be: {Name:"Vijay",Email:"VijayRana1091@gmail.com"}
    Second Document in same collection can be: {Name:"Vijay",Email:"VijayRana1091@gmail.com",Address : "Delhi"}
  6. MongoDB does not support Joins.
  7. Mongo DB does not support Transactions.

It's Query Time

We will see here some of the query which we run in SQL server on a daily basis and equivalent query in Mongo DB.

For this, I created a table in SQL server with the below schema and a same collection (Remember table is a collection in MongoDB) in MongoDB.

Image 3

We will create the same table in MongoDB and different operations on this table using MongoDB.

Operation SQL MongoDB
Select Database use Test use Test

Image 4

We will get a message "switched to db Test".

Insert Record
SQL
Insert into Student Values_
(1,'Vijay', 'VijayRana1091@gmail.com', _
'9711965544','Delhi')
SQL
db.Student.insert(_
{"_id":1,"Name":"Vijay",_
"Email":"VijayRana1091@gmail.com",
"PhoneNo":"9711965544",_
"Address":"Delhi"}
)

Image 5

Note: _id works as a primary key in MongoDb. If we will not insert any value in this column, then MongoDB will automatically insert a unique ID in Table.

Inserting some more records in both SQL Server and in MongoDB (Attaching SQL script and MongoDB script)

After running scripts, we have the below data in Student table (SQL server and in MongoDB) databases.

Image 6

Select

Select all Columns:

select * from student

Select few Column:

select ID,Name from Student

Select all Columns:

db.Student.find()

Select few Column:

db.Student.find({},{"Name":true})

We use find() Method to pull all the records from the table.

Image 7

Where Clause
SQL
select * from student _
where Name='Vijay'
SQL
db.Student.find({Name:"Vijay"})

Image 8

Greater Than and Less Than

select * from student where ID>2

select * from student where ID>=2

select * from student where ID<2

select * from student where ID<=2

db.Student.find({_id:{$gt:2}})

db.Student.find({_id:{$gte:2}})

db.Student.find({_id:{$lt:2}})

db.Student.find({_id:{$lte:2}})

We use $gt/$gte for Greater Than/Greater Than Equal and $lt/$lte for Less Than/Less Than Equal

Image 9

Like

Below query will Find all the records where letter P exists somewhere in Name Column

select * from student where Name like '%P%'

Suppose we want to find all the records where Name ends with letter 'a'

select * from student where Name like '%a'

Suppose we want to fetch all the records where Name starts with Letter 'P'

select * from student where Name like 'p%'

In Mongo DB, we use $regex operator to check whether letter 'P' exists somewhere in Name Column.

db.Student.find({Name:{$regex:"P"}})

Below query will Fetch all the records where Name ends with letter 'a'

db.Student.find({Name:{$regex:"a$"}})

Below query will Fetch all the records where Name starts with letter 'P'

db.Student.find({Name:{$regex:"^P"}})

Image 10

And/Or

And:

SQL
select * from Student_
		 where Name ='Vijay'_
		 and phoneNo='9711965544'

Or:

SQL
select * from Student_
		 where Name ='Vijay'_
		 or phoneNo='9711997119'

And:

SQL
db.Student.find_
			({$and :[{Name :'Vijay'},_
			{PhoneNo : '9711965544'}]})

Or:

SQL
db.Student.find_
			({$or:[{Name :'Vijay'},_
			{PhoneNo : '9711997119'}]})

Image 11

Image 12

in
SQL
select * from Student where id in(1,3,5)
db.Student.find({_id:{$in:[1,3,5]}})

Image 13

Count/Sort

Count all the records :

select count(*) from Student

Sort Records in ascending order:

select * from student order by Name

Sort in descending order :

select * from student order by Name desc

Count all the records :

db.Student.find().count()

Sort Records in ascending order:

db.Student.find().sort({Name:1})

Sort in descending order :

db.Student.find().sort({Name:-1})

Image 14

Image 15

Update
SQL
update student set phoneNo='2222222222',_
			Address='USA' where ID=4
SQL
db.Student.update({_id:4},_
	{$set:{PhoneNo:'2222222222',_
     Address:'USA'}})

Upsert:

SQL
db.Student.update({_id:9},_
			{$set:{PhoneNo:'2222222222',_
			Address:'USA'}},{upsert:true})

The above query will search a document where _id is 9. There will be an _id with 9 it will update phoneNo and Address, otherwise it will insert a new document where _id is 9.

Multi:

Suppose we execute the below query:

SQL
db.Student.update({Name:"Vijay"},_
		{$set:{PhoneNo:'2222222222',_
         Address:'USA'}})

It will update only one record (remember only first Match where name is "Vijay"). But if we want to update all the records where Name is "Vijay" then we will pass multi argument also

SQL
db.Student.update({Name:"Vijay"},_
	{$set:{PhoneNo:'2222222222',_
    Address:'USA'}},_
	{multi:true})

Image 16

Image 17

Delete/Remove/Drop

Delete with Condition:

Delete from student where ID=5

Delete all the records:

Delete from Student

Drop:

drop table Student

Delete with Condition:

db.Student.remove({_id:5})

Delete all the records:

db.Student.remove({})

Drop:

db.Student.drop()

Image 18Image 19

Top

select Top 2* from Student

Limit:

db.Student.find().limit(2)

Skip:

db.Student.find().skip(2).limit(2)

As the name suggests, skip will skip the number of documents and limit will limit the number of records.

 
distinct select distinct Name from Student db.Student.distinct("Name")
 
Backup
SQL
BACKUP DATABASE Test
	TO DISK = 'C:\Vijay\Test.Bak'
	WITH FORMAT,
	MEDIANAME = 'Z_SQLServerBackups',
	NAME = 'Full Backup of Test Database';

Go to the Command Prompt and run the below command (assuming your mongodump exe is inside bin folder, if it is a different location, then change this location accordingly)

C:\Program Files\MongoDB\Server\3.0\bin\mongodump --db Test

 

MongoDB Functions

In MongoDB databases, we can create functions like in SQL Server. MongoDB provides us a collection named as System.js for this.

System.js collection contains two keys:

  1. _id: _id is the function name.
  2. value: value contains actual function definition.

For example, suppose we want to create a function which will accept two parameters named firstname and lastname and will return full name.

C
db.system.js.save
(
   {
     _id: "FullName",
     value : function(FirstName,LastName) { return FirstName + ' ' + LastName; }
   }
)

In order to call this function, we need to load server scripts first, and then we can call this function as below:

C
db.loadServerScripts();
FullName('Vijay','Rana')

Vijay Rana will be the result when we will call this function.

Auto Increment ID (Identity in SQL Server) in MongoDB

In MongoDB, _id works as a primary key. If we don't insert any value in _id field, then MongoDB automatically insert a unique hexadecimal value in this column. But if we want to enter auto increment integer value (Like Identity in SQL Server or Sequence in Oracle) in this field, then there is no direct way to do so. For this, we need to follow the below steps:

Step 1

Create a collection (Identity in my case) which will hold the counter:

C#
db.createCollection("Identity")    // Identity is my collection Name

Step 2

Insert a document in this collection with intial counter value:

C#
db.Identity.insert({_id:"incrementID",sequence_value:0}) 

Step 3

Create a function which will increment this sequence_value and add that function in system.js as below:

C#
db.system.js.save
(
   {
     _id: "getNextIdentity",                                            
     value : function getNextIdentity(sequenceName)
     {
        var sequenceDocument = db.Identity.findAndModify
            ({
                query:{_id: sequenceName },
                update: {$inc:{sequence_value:1}},
                new:true
            });
        return sequenceDocument.sequence_value;
     }
   }
)

Step 4

While inserting a document, we can call this function, which will return an incremented value as below:

C#
db.Employee.insert({
   "_id":getNextIdentity("incrementID"),
   "EmpName":"Vijay",
   "Age":"30"
})

In the next article, I will talk about the rest of the MongoDB commands. How we will generate Auto Increment ID as we normally does in SQL Server (Identity Column) and we will see the connectivity of .NET with MongoDB.

History

  • 25th March, 2016: Initial version

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Technical Lead
India India
Hi Myself Vijay having around 7 years of experience on Microsoft Technologies.

Comments and Discussions

 
QuestionSuggestions Pin
BillWoodruff21-Mar-21 19:27
professionalBillWoodruff21-Mar-21 19:27 
QuestionMongo DB 4.0 adds support for multi-document ACID transactions Pin
BillWoodruff21-Mar-21 19:12
professionalBillWoodruff21-Mar-21 19:12 
QuestionMongo DB does support transactions. Pin
Marcelo Sousa 202121-Mar-21 5:05
Marcelo Sousa 202121-Mar-21 5:05 
PraiseMY VOTE OF FIVE Pin
Tarun.Saini07171-Jul-16 1:03
Tarun.Saini07171-Jul-16 1:03 
Questionplease help to develop mongo db query Pin
Tushar sangani6-Jun-16 23:12
professionalTushar sangani6-Jun-16 23:12 
Praisewonderful Pin
Rupal Gupta24-Apr-16 22:22
Rupal Gupta24-Apr-16 22:22 
GeneralAmazing article Pin
Member 1247122619-Apr-16 8:07
Member 1247122619-Apr-16 8:07 
QuestionVery nice Vijay Pin
Member 124461708-Apr-16 15:16
Member 124461708-Apr-16 15:16 
GeneralMy vote of 5 Pin
priyanka choudhary8-Apr-16 14:49
priyanka choudhary8-Apr-16 14:49 
GeneralMy vote of 5 Pin
priyanka choudhary8-Apr-16 14:48
priyanka choudhary8-Apr-16 14:48 
SuggestionPlease research if you are about to write an article... Pin
Andreas Kroll31-Mar-16 6:22
Andreas Kroll31-Mar-16 6:22 
GeneralRe: Please research if you are about to write an article... Pin
VijayRana31-Mar-16 7:23
professionalVijayRana31-Mar-16 7:23 
PraiseGood article on Mongo DB.. Thanks alot!!! My vote is 5... Pin
Munir Ahmd29-Mar-16 7:20
Munir Ahmd29-Mar-16 7:20 
PraiseExcellent! Pin
DataBytzAI28-Mar-16 2:09
professionalDataBytzAI28-Mar-16 2:09 
GeneralRe: Excellent! Pin
VijayRana10-Apr-16 2:11
professionalVijayRana10-Apr-16 2:11 
GeneralRe: Excellent! Pin
DataBytzAI10-Apr-16 7:17
professionalDataBytzAI10-Apr-16 7:17 
GeneralBest article Pin
PreetiRana25-Mar-16 4:09
PreetiRana25-Mar-16 4:09 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.