SQL to MongoDB Chart Mapping

Introduction

If you’re used to working with relational databases that store data in rows, you’ve probably developed some solid SQL skills. Switching to a NoSQL database like MongoDB, which stores data in collections, may seem intimidating. Don’t fear– most common SQL terms and queries can be easily translated to MongoDB. In this article, we’ll present a handy reference chart containing common SQL statements and their equivalent MongoDB statements.

The SQL to MongoDB Comparison Table

Database Executables

Let’s start with a quick look at the different executables used in a MySQL/Oracle implementation compared to a MongoDB implementation. In both cases, you’ll have both a server and a client:

MySQL/OracleMongoDB
Database Servermysqld/oraclemongod
Database Clientmysql/sqlplusmongo

SQL to MongoDB Terminology and Concepts

Next, let’s compare some different terms and concepts that are frequently used in database administration. The table below lists some common SQL terms and the equivalent terminology used in MongoDB:

SQLMongoDB
databaseMongoDB also uses the term database
tableThe SQL table in MongoDB is called a collection
rowIn MongoDB the SQL row translates to a document or BSON document
columnThe SQL column in MongoDB is called a field
indexindex
table joinsThe table joins in SQL translates to Embedded Documents and linking
primary keyprimary key
In MongoDB, the primary key is automatically set to the _id field as ObjectId.

TABLE Operations

If you have experience with SQL, you’re probably familiar with the syntax used to create a table in a database. Similar functionality exists in MongoDB to create a collection:

SQL Schema StatementsMongoDB Schema Statements
CREATE TABLE family ( id MEDIUMINT NOT NULL
AUTO_INCREMENT,
family_id Varchar(30),
age Number,
gender char(1),
PRIMARY KEY (id))
The “family” database is implicitly created on the
first use of insertOne() or the insertMany() operation.
The equivalent of PRIMARY KEY is _id which is automatically
generated if the same is not specified.

db.family.insert({
family_id: "fam123",
age: 7,
gender: "male"
})

To create a collection explicitly:

db.createCollection("family")
CREATE INDEX idx_family_id_asc
ON family(family_id)
db.family.ensureIndex ({ family_id: 1})
CREATE INDEX
      idx_user_id_asc_age_desc
ON users(user_id, age DESC)
db.family.ensureIndex( { family_id: 1, age: -1 } )
DROP TABLE familydb.family.drop()

SQL Statements

SELECT

SELECT statements in SQL are used for reading records from tables. You can create similar queries in MongoDB using the following corresponding statements:

SELECT Statements (SQL)find() MongoDB StatementsNotes
EXPLAIN SELECT *
FROM family
WHERE gender = "male"
db.family.find( { gender: "male" } ).explain().explain() — Gives information to aid in understanding how queries were executed and behaves.
SELECT *
FROM family
LIMIT 3
SKIP 20
db.family.find().limit(3).skip(20).limit() — Specifies the number of documents the cursor will return.
.skip() — Skips the number of documents in the Query result.
SELECT COUNT(*)
FROM family
WHERE age > 10
db.family.find( { age: { $gt: 10 } } ).count()

$gt is the equivalent of the (>) greater than sign in SQL.
SELECT *
FROM family
LIMIT 1
db.users.find().limit(1)
or
db.family.findOne()
findOne() — Returns one document as per the required criteria.
SELECT COUNT(family_id)
FROM family
db.family.find( { family_id: { $exists: true } } ).count()
or
db.family.count( { family_id: { $exists: true } } )
.count() — Returns the count of the document from a find() query.
SELECT *
FROM family
db.family.find().find() — Selects documents in a collection and return a cursor to the selected documents.
SELECT id, family_id, gender
FROM family
db.users.find(
{ },
{ user_id: 1, status: 1 }
)
.find() — Selects documents in a collection and return a cursor to the selected documents.

INSERT

Next, let’s look at a sample SQL statement for inserting records and its counterpart in MongoDB:

INSERT Statements (SQL)insert() MongoDB StatementsNotes
INSERT INTO family(family_id,
age,
gender)
VALUES ("rmg1982",
37,
"male")
db.family.insert( {
family_id: "rmg1982",
age: 37,
status: "male"
} )
insert() — This inserts a new document in the
collection with the corresponding stated values.
MongoDB add _id with an ObjectId value if not set.

UPDATE

The next table we’ll look at shows a typical SQL statement for updating records and its counterpart in MongoDB:

UPDATE Statements (SQL)update() MongoDB StatementsNotes
UPDATE family
SET age = age + 1
WHERE gender = "female"
db.family.update(
{ gender: "female" },
{ $inc: { age: 3 } },
{ multi: true }
)
update() — This updates a single document by default.
$inc — This increments a field by a specified value.
multi — If set to true, this will update multiple
documents that satisfy the criteria otherwise will update one document only.
UPDATE family
SET gender = "female"
WHERE age > 2
db.family.update(
{ age: { $gt: 2 }},
{ $set: { gender: "female" } },
{ multi: true }
)
$set — This operator replaces the value of a field with a give value.
$gt — is the equivalent of the (>) greater than sign in SQL.

DELETE

The final table in our reference guide shows a typical SQL statement for deleting records and the equivalent MongoDB statement:

DELETE Statements (SQL)remove() MongoDB StatementsNotes
DELETE FROM family
WHERE gender = "male"
db.family.remove( { gender: "male" } )remove() — This removes a document from a specified collection.

Conclusion:

If you’re accustomed to working with SQL, MongoDB statements may seem like a foreign language. However, looking at a few side-by-side comparisons makes it easy to see how common SQL statements can be translated into their MongoDB equivalents. With this handy reference guide, you’ll be able to find the MongoDB equivalent for virtually any SQL statement or concept. SQL Statements Equivalent to MongoDB

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.