MongoDB Fundamentals – Mapping Relational SQL – Day 6 of 6

This entire week, I am going to discuss the Fundamentals of MongoDB. Today is the final (sixth) post in the six post series. We are going to learn about Mapping Relational SQL in MongoDB. I have recently build a Video Course at Foundations of Document Databases with MongoDB focusing on helping users to get started with MongoDB quickly and efficiently.

MongoDB Fundamentals - Mapping Relational SQL - Day 6 of 6 MappingRelationalSQL-800x413

Mapping Relational SQL

If you are beginning the journey of MongoDB from the SQL Server world, I am very confident that you have a few questions and any confusion. Let us start by addressing the elephant in the room.

  • SQL Tables are MongoDB Collections
  • SQL Rows are MongoDB Documents
  • SQL Columns are MongoDB Fields

Actually, I personally feel awesome after what I just mentioned above. Lots of people get confused with the terminology while essentially they are the same.

SQL TermsMongoDB Terms
DatabaseDatabase
TableCollection
RowDocument
ColumnField
IndexIndex
Table joins$lookup
Primary keyPrimary Key
TransactionsTransactions

The table above presents the various SQL terminology and concepts and the corresponding MongoDB terminology and concepts.

Let us see some of the common SQL operations and equivalent MongoDB script. I will be using MySQL as a base to write all of my scripts.

Create Table vs Create Collection

MySQL:

CREATE TABLE actors (
id MEDIUMINT NOT NULL AUTO_INCREMENT,
name varchar(50),
age int,
PRIMARY KEY (id)
)

MongoDB:

db.createCollection("actors")

Insert Statement

MySQL:

INSERT INTO actors (name, age, email)
VALUES ('Roger', 46, 'roger@email.com')

MongoDB:

db.actors.insertOne(
    {
		name: "Roger", 
		age: 46,
		email: "roger@email.com"  	
   }
)

Select Statement

MySQL:

SELECT *
FROM actors

MongoDB:

db.actors.find()

Select Statement – Filter

MySQL:

SELECT *
FROM actors
WHERE age > 20

MongoDB:

db.user.find(
    { age: { $gt: 20 } },
    {name: 1, age: 1, _id: 0 }
)

Select Statement – Advanced

MySQL:

SELECT name, age
FROM actors
WHERE age > 20
LIMIT 5
SKIP 10

MongoDB:

db.actors.find(
    { age: { $gt: 20 } },
    {name: 1, age: 1, _id: 0 }
).limit(5).skip(10)
)

Update Statement

MySQL:

UPDATE actors
SET email = 'NA'
WHERE age < 18

MongoDB:

db.actors.updateMany(
   { age: { $lt: 18 } },
   { $set: {email : "NA" } }
)

Delete Statement

MySQL:

DELETE FROM actors
WHERE age < 18

MongoDB:

db.actors.deleteMany(
   { age: { $lt: 18 } })

Drop Table – Drop Collection

MySQL:

DROP TABLE actors

MongoDB:

db.actors.drop()

Well, that’s it. We have learned all the basics statements and their presentation in MongoDB.

I strongly suggest that we all learn multiple programming languages for databases.

Learning

Here are some of the relevant blog posts on MongoDB.

Technology Online

I hope you find these Learning paths helpful. If you have a Pluralsight subscription, you can watch it for free. If you do not have a Pluralsight subscription, you can still watch the course for FREE by signing up for a trial account. Please note that you do not need any credit card. You can always connect with me on twitter.

Reference: Pinal Dave (https://blog.sqlauthority.com)

, , ,
Previous Post
MongoDB Fundamentals – CRUD: Deleting Objects – Day 5 of 6
Next Post
Ask the Experts: Database Performance Roundtable by Quest

Related Posts

2 Comments. Leave new

  • Precise and simple to get basic understanding on MongoDB concept. Thanks for Sharing Pinal.

    Reply
  • Christian Cobas Scutari
    June 11, 2020 4:57 pm

    Good article. Very clear. Thanks

    Reply

Leave a Reply

Menu