New update is available. Click here to update.

SQL to MongoDB Mapping

Gunjeev Singh
Last Updated: May 13, 2022

Introduction

A Database Management System (DBMS) is a system software used to create, manage and work with databasesMongoDB is a non-relational database management system. Unlike SQL(Structured Query Language) databases based on tables, MongoDB is a document-based database. 

 

This blog will look into the various comparative aspects of SQL and MongoDB. We will learn multiple commands in Mongo and their SQL analogies.

MongoDB is often an integral part of the Javascript web ecosystem, MERN, or MEAN stack. 

You can learn more about MERN stack web development here!

An analogy to RDBM Systems:

In traditional Relational Database Management Systems, data is stored in databases with rows and columns tables.

MongoDB is a NOSQL DBMS. In NOSQL DBMS, information is stored in a BSON format. A BSON format is simply a binary version of JSON. Data is stored in the form of collections

 

If we create a diagrammatic analogy between SQL tables and NOSQL collections, we would have the following analogy:

 

Comparison between NOSQL and SQL DBMS

 

In every NOSQL collection, BSON Documents exist with single data storages called fields, just like columns exist in rows in SQL tables. 

Concepts in SQL and MongoDB

Before we compare SQL terminology to MongoDB terminology, let us first understand some basic concepts of SQL and MongoDB.

FieldThis is a name and value pair. 
DocumentA group of fields is called a document.
CollectionA group of documents clubbed together, is called a collection.
DatabaseA physical container to keep multiple collections is called a database. 

 

Now, let us see how these MongoDB concepts match up with SQL terminology. 

SQL Terminology

MongoDB Terms

DatabaseDatabase
TableCollection
RowDocument
ColumnField
IndexIndex
Primary KeyPrimary Key
AggregationAggregation Pipeline
SELECT INTO NEW_TABLE$out
MERGE INTO TABLE$merge
table joins$lookup, embedded document

 

As discussed in the first table, a field is the smallest part of a MongoDB, just like a column is the smallest part of a SQL table. Like columns clubbed together form rows and rows form tables, they are analogous to collections and documents. 

Another important analogy that needs to be discussed here is that of the primary key. In SQL systems we can assign any unique column as the primary key. In MongoDB every collection has an _id field, which is automatically taken as the primary key.

Mapping SQL Commands to MongoDB

For learning the difference between SQL and MongoDB we will take an example. Let us assume a table called Demo in SQL. For the analogy, let us assume a collection called Demo in MongoDB which contains documents of the following type:

 

{  
  _id: ObjectId("50646isz351sd"),  
  user_id: "gunjeev",  
  age: 19,  
  status: 'Y'
}

Create and Alter Commands

The following table includes Create and Alter commands in SQL and MongoDB.

SQL Statements

MongoDB Command

CREATE TABLE Demo (

    id MEDIUMINT NOT NULL

        AUTO_INCREMENT,

    user_id Varchar(20),

    age Number,

    status char(1),

    PRIMARY KEY (id)

)

db.createCollection ( " Demo " )

 

ALTER TABLE Demo ADD join_date DATETIME

db.Demo.updateMany(

    { },

    { $set: { join_date: new Date() } }

)

ALTER TABLE Demo DROP COLUMN join_date

db.Demo.updateMany(

    { },

    { $unset: { "join_date": "" } }

)

CREATE INDEX idx_user_id_asc ON Demo ( user_id )

db.people.createIndex ( { user_id: 1 } )

 

CREATE INDEX idx_user_id_asc ON people (user_id)

db.people.createIndex( { user_id: 123, age: 1} )

 

DROP TABLE people

db.people.drop ()

 

Insert Statements

The following table includes insertion commands in SQL and MongoDB.

SQL Insert Statement

MongoDB Insert Statement

INSERT INTO Demo (user_id,

                  age,

                  status)

VALUES ("ninja", 19, "N")

 

db.Demo.insertOne(

   { user_id: "ninja", age: 19, status: "N" }

)

Delete Statements

The following table includes delete commands in SQL and MongoDB.

SQL Delete Statement

MongoDB Delete Command

DELETE FROM Demo WHERE status = "N"

db.Demo.deleteMany( { status: "N" } )

DELETE FROM Demo

db.Demo.deleteMany( { } )

Update Statements

The following table includes updation commands in SQL and MongoDB.

SQL Update Statement

MongoDB Update Statement

UPDATE Demo SET status = "Y"

WHERE age > 18

db.Demo.updateMany( { age: { $gt: 18 } }, { $set: { status: "Y" } } )

 

UPDATE Demo SET age = age + 2

WHERE status = "N"

 

db.Demo.updateMany( { status: "N" } , { $inc: { age: 2 } } )

 

Selection Commands

The following table includes selection commands in SQL and MongoDB.

SQL Commands

MongoDB Statements

SELECT *

FROM Demo

db.Demo.find()

SELECT id, user_id, status FROM Demo

db.Demo.find( { }, { user_id: 1, status: 1 } )

 

SELECT user_id, status FROM Demo

db.Demo.find( { }, { user_id: 1, status: 1, _id: 0 } )

 

SELECT * FROM Demo WHERE status = "B"

db.Demo.find( { status: "B" } )

 

SELECT user_id, status FROM Demo WHERE status = "A"

 

db.Demo.find( { status: "A" }, { user_id: 1, status: 1, _id: 0 } )

 

SELECT * FROM Demo WHERE status != "A"

 

db.Demo.find( { status: { $ne: "A" } } )

 

SELECT *

FROM Demo

WHERE status = "A"

AND age = 50

 

db.Demo.find(

    { status: "A",

      age: 50 }

)

 

SELECT *

FROM Demo

WHERE status = "A"

OR age = 50

 

db.Demo.find(

    { $or: [ { status: "A" } , { age: 50 } ] }

)

 

SELECT *

FROM Demo

WHERE age > 25

 

db.Demo.find(

    { age: { $gt: 25 } }

)

 

SELECT *

FROM Demo

WHERE age < 25

db.Demo.find(

   { age: { $lt: 25 } }

)

 

SELECT *

FROM Demo

WHERE age > 25

AND   age <= 50

db.Demo.find(

   { age: { $gt: 25, $lte: 50 } }

)

 

SELECT COUNT(*)

FROM Demo

db. Demo.count()

 

SELECT COUNT(user_id)

FROM Demo

db.Demo.count( { user_id: { $exists: true } } )

 

SELECT COUNT(*)

FROM Demo

WHERE age > 30

db.Demo.count( { age: { $gt: 30 } } )

 

SELECT DISTINCT(status)

FROM Demo

db.Demo.aggregate( [ { $group : { _id : "$status" } } ] )

 

SELECT *

FROM Demo

LIMIT 1

db.Demo.find().limit(1)

 

SELECT *

FROM Demo

LIMIT 5

SKIP 10

db.Demo.find().limit(5).skip(10)

 

EXPLAIN SELECT *

FROM Demo WHERE status = "A"

db.Demo.find( { status: "A" } ).explain()

 

 

FAQs

1. What is the analogy between SQL and MongoDB with regards to aggregation?
Aggregation in DBMS refers to grouping two or more entities together in order to make a new, more meaningful new entity. MongoDB offers an aggregation pipeline just like SQL offers aggregation commands. 

 

2. What are regex commands in MongoDB?
Regex commands provide the ability to search for patterns in queries and requests. Examples of Regex commands are:

{ <field>: { $regex: /pattern/, $options: '<options>' } }

Key Takeaways

This blog looked into the various comparative aspects of SQL and MongoDB, a NOSQL Database Management System. If you were comfortable with SQL systems, you would now be comfortable working with MongoDB systems.

 

Now that you have learned about the differences between MongoDB and SQL, continue your backend development journey by following this link! You can also explore other blogs related to web technologies here.

Was this article helpful ?
1 upvote