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!!!