SQL to MongoDB Mapping

To learn new stuff (especially a programming language)by comparing with the one you already know of is very interesting. When i decided to use NoSQL in one of my personal projects, i decided to learn the fundamentals of it by comparing it with mySQL(one of the RDBMS languages out there..).

Let’s get started…

mySQL 2 MongoDB

SQL Terms → MongoDB Terms

database                -->            database
table --> collection
row --> document or BSON document column --> field
index --> index
table joins --> embedded documents & linking
where                   -->            $match
group by --> $group
count() --> $sum
order by --> $sort
limit() --> $limit
select --> $project
having --> $match

Let’s create a simple table User for which we write SQL statements and the corresponding MongoDB statements

{ 
_id: ObjectId(“20034059”),
user_id: “pranava”,
age: 29,
status: ‘A’
}

SQL Statements → MongoDB Statements

create a table(Sql)/Collection(MongoDB)
SQL:
CREATE TABLE users (
id MEDIUMINT NOT NULL AUTO_INCREMENT,
user_id Varchar(30),
age Number,
status char(1),
PRIMARY KEY (id))
MongoDB:
   db.users.insert({
user_id: 'Rama',
age: 100,
status: 'NA'
}) or db.createCollection('users')
Drop a table(Sql)/Collection(MongoDB)
SQL:
DROP table users;
MongoDB:
db.users.drop();
Alter a table(Sql)/Collection(MongoDB) for a column
SQL:
Alter table users ADD location Varchar(255);
MongoDB:
db.users.update(
{user_id: 'Pranava'},
{$set: { location: 'Bharat'},
{multi: true}
)
Insert a row into a table(Sql)/Collection(MongoDB)
SQL:
Insert into users (user_id,age,status) values ('abc',200, 'A');
MongoDB:
db.users.insert(
{user_id: 'Pranava'},
{$set: { location: 'Bharat'},
{multi: true}
)
Select all rows in a table(Sql)/Collection(MongoDB)
SQL:
SELECT * from users;
MongoDB:
db.users.find();
Select specific columns in a table(Sql)/Collection(MongoDB)
SQL:
SELECT user_id, age from users;
MongoDB:
db.users.find({},{user_id: 1, status: 1});
Select active users rows in a table(Sql)/Collection(MongoDB)
SQL:
SELECT * from users where status='A';
MongoDB:
db.users.find({status: 'A'});
SQL:
SELECT user_id, age from users where status='A';
MongoDB:
db.users.find({status: 'A'},{user_id: 1, status: 1});
Select users rows in a table(Sql)/Collection(MongoDB) AND Condition
SQL:
SELECT * from users where status='A' and location='Bharat';
MongoDB:
db.users.find({status: 'A',location: 'Bharat' });
Select users rows in a table(Sql)/Collection(MongoDB) OR Condition
SQL:
SELECT * from users where status='A' or location='Bharat';
MongoDB:
db.users.find($or: [{status: 'A'},{location: 'Bharat' }]);
Select users rows in a table(Sql)/Collection(MongoDB) Conditional Operators
SQL:
SELECT * from users where age > 29;
MongoDB:
db.users.find({ age: { $gt: 29}});
Other Operators: $lt, $gte, $lte
Select multiple user rows in a table(Sql)/Collection(MongoDB)
SQL:
SELECT * from users where user_id='%prana%';
MongoDB:
db.users.find( { user_id: /^bc/ } )
Sort return rows in a table(Sql)/Collection(MongoDB)
SQL:
SELECT * from users order by age desc;
MongoDB:
db.users.find().sort({age: -1});
Select count rows in a table(Sql)/Collection(MongoDB)
SQL:
SELECT count(*) from users ;
MongoDB:
db.users.count() || db.users.find().count();
Update rows in a table(Sql)/Collection(MongoDB)
SQL:
UPDATE users SET status = "C" WHERE age > 25;
MongoDB:
db.users.update(
{ age: { $gt: 25 } },
{ $set: { status: "C" } },
{ multi: true }
);
Delete rows in a table(Sql)/Collection(MongoDB)
SQL:
Delete from users where age > 50 ;
MongoDB:
db.users.remove({ age: {$gt: 50}});

I merely scratched the surface of the interesting language with known example set, Next step to get in-depth understanding of MongoDB and learn more about it’s features, visit MongoDB Manual or MongoDB University.

Happy Learning!!!