SQL to MongoDB Mapping
Introduction
A Database Management System (DBMS) is a system software used to create, manage and work with databases. MongoDB 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.
| Field | This is a name and value pair. |
| Document | A group of fields is called a document. |
| Collection | A group of documents clubbed together, is called a collection. |
| Database | A 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 |
| Database | Database |
| Table | Collection |
| Row | Document |
| Column | Field |
| Index | Index |
| Primary Key | Primary Key |
| Aggregation | Aggregation 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.
