SQL to MongoDB Mapping Chart
In addition to the charts that follow, you might want to consider the Frequently Asked Questions section for a selection of common questions about MongoDB.
Terminology and Concepts
The following table presents the various SQL terminology and concepts and the corresponding MongoDB terminology and concepts.
SQL Terms/Concepts | MongoDB Terms/Concepts |
---|---|
database | |
table | |
row | |
column | |
index | |
table joins | $lookup , embedded documents |
primary key Specify any unique column or column combination as primary key. | In MongoDB, the primary key is automatically set to the _id field. |
aggregation (e.g. group by) | aggregation pipeline |
SELECT INTO NEW_TABLE | |
MERGE INTO TABLE | |
UNION ALL | |
transactions | TipFor many scenarios, the denormalized data model (embedded documents and arrays) will continue to be optimal for your data and use cases instead of multi-document transactions. That is, for many scenarios, modeling your data appropriately will minimize the need for multi-document transactions. |
Executables
The following table presents some database executables and the corresponding MongoDB executables. This table is not meant to be exhaustive.
Examples
The following table presents the various SQL statements and the corresponding MongoDB statements. The examples in the table assume the following conditions:
The SQL examples assume a table named
people
.The MongoDB examples assume a collection named
people
that contain documents of the following prototype:{ _id: ObjectId("509a8fb2f3f4948bd2f983a0"), user_id: "abc123", age: 55, status: 'A' }
Create and Alter
The following table presents the various SQL statements related to table-level actions and the corresponding MongoDB statements.
SQL Schema Statements | MongoDB Schema Statements | ||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Implicitly created on first
However, you can also explicitly create a collection:
| ||||||||||||||
| Collections do not describe or enforce the structure of its documents; i.e. there is no structural alteration at the collection level. However, at the document level,
| ||||||||||||||
| Collections do not describe or enforce the structure of its documents; i.e. there is no structural alteration at the collection level. However, at the document level,
| ||||||||||||||
|
| ||||||||||||||
|
| ||||||||||||||
|
|
For more information on the methods and operators used, see:
Insert
The following table presents the various SQL statements related to inserting records into tables and the corresponding MongoDB statements.
SQL INSERT Statements | MongoDB insertOne() Statements | |||||||||
---|---|---|---|---|---|---|---|---|---|---|
|
|
For more information, see db.collection.insertOne()
.
Select
The following table presents the various SQL statements related to reading records from tables and the corresponding MongoDB statements.
Note
The find()
method always includes the _id
field in the returned documents unless specifically excluded through
projection. Some of the SQL queries below may include an
_id
field to reflect this, even if the field is not included in the
corresponding find()
query.
SQL SELECT Statements | MongoDB find() Statements | ||||||||
---|---|---|---|---|---|---|---|---|---|
|
| ||||||||
|
| ||||||||
|
| ||||||||
|
| ||||||||
|
| ||||||||
|
| ||||||||
|
| ||||||||
|
| ||||||||
|
| ||||||||
|
| ||||||||
|
| ||||||||
|
-or-
| ||||||||
|
-or-
| ||||||||
|
| ||||||||
|
| ||||||||
|
or
| ||||||||
|
or
| ||||||||
|
or
| ||||||||
|
or, for distinct value sets that do not exceed the BSON size limit
| ||||||||
|
or
| ||||||||
|
| ||||||||
|
|
For more information on the methods and operators used, see
Update Records
The following table presents the various SQL statements related to updating existing records in tables and the corresponding MongoDB statements.
SQL Update Statements | MongoDB updateMany() Statements | |||||||
---|---|---|---|---|---|---|---|---|
|
| |||||||
|
|
For more information on the method and operators used in the examples, see:
Delete Records
The following table presents the various SQL statements related to deleting records from tables and the corresponding MongoDB statements.
SQL Delete Statements | MongoDB deleteMany() Statements | |||
---|---|---|---|---|
|
| |||
|
|
For more information, see db.collection.deleteMany()
.
Further Reading
If you are considering migrating your SQL application to MongoDB, download the MongoDB Application Modernization Guide.
The download includes the following resources:
Presentation on the methodology of data modeling with MongoDB
White paper covering best practices and considerations for migrating to MongoDB from an RDBMS data model
Reference MongoDB schema with its RDBMS equivalent
Application Modernization scorecard