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/Oracle | MongoDB | |
|---|---|---|
| Database Server | mysqld/oracle | mongod |
| Database Client | mysql/sqlplus | mongo |
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:
| SQL | MongoDB |
|---|---|
| database | MongoDB also uses the term database |
| table | The SQL table in MongoDB is called a collection |
| row | In MongoDB the SQL row translates to a document or BSON document |
| column | The SQL column in MongoDB is called a field |
| index | index |
| table joins | The table joins in SQL translates to Embedded Documents and linking |
| primary key | primary 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 Statements | MongoDB 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 family | db.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 Statements | Notes |
|---|---|---|
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 Statements | Notes |
|---|---|---|
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 Statements | Notes |
|---|---|---|
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 Statements | Notes |
|---|---|---|
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