SQL to MongoDB Mapping Chart

In addition to the charts that follow, you might want to consider theFrequently Asked Questions section for a selection of common questions about MongoDB.

Terminology and Concepts

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

SQL Terms/ConceptsMongoDB Terms/Concepts
databasedatabase
tablecollection
rowdocument or BSON document
columnfield
indexindex
table joins$lookup, embedded documents
primary keySpecify any unique column or column combination as primary key.primary keyIn MongoDB, the primary key is automatically set to the_id field.
aggregation (e.g. group by)aggregation pipelineSee the SQL to Aggregation Mapping Chart.
SELECT INTO NEW_TABLE$outSee the SQL to Aggregation Mapping Chart.
MERGE INTO TABLE$merge (Available starting in MongoDB 4.2)See the SQL to Aggregation Mapping Chart.
transactionstransactionsTipFor many scenarios, the denormalized data model(embedded documents and arrays)will continue to be optimal for your data and use casesinstead of multi-document transactions. That is, for manyscenarios, modeling your data appropriately will minimize theneed for multi-document transactions.

Executables

The following table presents some database executables and thecorresponding MongoDB executables. This table is not meant to beexhaustive.

MongoDBMySQLOracleInformixDB2
Database ServermongodmysqldoracleIDSDB2 Server
Database ClientmongomysqlsqlplusDB-AccessDB2 Client

Examples

The following table presents the various SQL statements and thecorresponding MongoDB statements. The examples in the table assume thefollowing conditions:

  • The SQL examples assume a table named people.

  • The MongoDB examples assume a collection named people that containdocuments of the following prototype:

  1. {
  2.   _id: ObjectId("509a8fb2f3f4948bd2f983a0"),
  3.   user_id: "abc123",
  4.   age: 55,
  5.   status: 'A'
  6. }

Create and Alter

The following table presents the various SQL statements related totable-level actions and the corresponding MongoDB statements.

SQL Schema StatementsMongoDB Schema Statements
  1. CREATE TABLE people (    id MEDIUMINT NOT NULL        AUTO_INCREMENT,    user_id Varchar(30),    age Number,    status char(1),    PRIMARY KEY (id))
Implicitly created on first insertOne() orinsertMany() operation. The primary key_id is automatically added if _id field is not specified.
  1. db.people.insertOne( {    user_id: "abc123",    age: 55,    status: "A" } )
However, you can also explicitly create a collection:
  1. db.createCollection("people")
  1. ALTER TABLE peopleADD join_date DATETIME
Collections do not describe or enforce the structure of itsdocuments; i.e. there is no structural alteration at thecollection level.However, at the document level, updateMany()operations can add fields to existing documents using the$set operator.
  1. db.people.updateMany(    { },    { $set: { join_date: new Date() } })
  1. ALTER TABLE peopleDROP COLUMN join_date
Collections do not describe or enforce the structure of itsdocuments; i.e. there is no structural alteration at the collectionlevel.However, at the document level, updateMany()operations can remove fields from documents using the$unset operator.
  1. db.people.updateMany(    { },    { $unset: { "join_date": "" } })
  1. CREATE INDEX idx_user_id_ascON people(user_id)
  1. db.people.createIndex( { user_id: 1 } )
  1. CREATE INDEX       idx_user_id_asc_age_descON people(user_id, age DESC)
  1. db.people.createIndex( { user_id: 1, age: -1 } )
  1. DROP TABLE people
  1. db.people.drop()

For more information on the methods and operators used, see:

- db.collection.insertOne()- db.collection.insertMany()- db.createCollection()- db.collection.updateMany()- db.collection.createIndex()- db.collection.drop()- $set- $unset

See also

Insert

The following table presents the various SQL statements related toinserting records into tables and the corresponding MongoDB statements.

SQL INSERT StatementsMongoDB insertOne() Statements
  1. INSERT INTO people(user_id,                  age,                  status)VALUES ("bcd001",        45,        "A")
  1. db.people.insertOne(   { user_id: "bcd001", age: 45, status: "A" })

For more information, see db.collection.insertOne().

See also

Select

The following table presents the various SQL statements related toreading records from tables and the corresponding MongoDB statements.

Note

The find() method always includes the _idfield in the returned documents unless specifically excluded throughprojection. Some of the SQL queries below may include an_id field to reflect this, even if the field is not included in thecorresponding find() query.

SQL SELECT StatementsMongoDB find() Statements
  1. SELECT FROM people
  1. db.people.find()
  1. SELECT id,       user_id,       statusFROM people
  1. db.people.find(    { },    { user_id: 1, status: 1 })
  1. SELECT user_id, statusFROM people
  1. db.people.find(    { },    { user_id: 1, status: 1, _id: 0 })
  1. SELECT FROM peopleWHERE status = "A"
  1. db.people.find(    { status: "A" })
  1. SELECT userid, statusFROM peopleWHERE status = "A"
  1. db.people.find(    { status: "A" },    { user_id: 1, status: 1, _id: 0 })
  1. SELECT FROM peopleWHERE status != "A"
  1. db.people.find(    { status: { $ne: "A" } })
  1. SELECT FROM peopleWHERE status = "A"AND age = 50
  1. db.people.find(    { status: "A",      age: 50 })
  1. SELECT FROM peopleWHERE status = "A"OR age = 50
  1. db.people.find(    { $or: [ { status: "A" } , { age: 50 } ] })
  1. SELECT FROM peopleWHERE age > 25
  1. db.people.find(    { age: { $gt: 25 } })
  1. SELECT FROM peopleWHERE age < 25
  1. db.people.find(   { age: { $lt: 25 } })
  1. SELECT FROM peopleWHERE age > 25AND   age <= 50
  1. db.people.find(   { age: { $gt: 25, $lte: 50 } })
  1. SELECT FROM peopleWHERE user_id like "%bc%"
  1. db.people.find( { user_id: /bc/ } )
-or-
  1. db.people.find( { user_id: { $regex: /bc/ } } )
  1. SELECT FROM peopleWHERE user_id like "bc%"
  1. db.people.find( { user_id: /^bc/ } )
-or-
  1. db.people.find( { user_id: { $regex: /^bc/ } } )
  1. SELECT FROM peopleWHERE status = "A"ORDER BY user_id ASC
  1. db.people.find( { status: "A" } ).sort( { user_id: 1 } )
  1. SELECT FROM peopleWHERE status = "A"ORDER BY user_id DESC
  1. db.people.find( { status: "A" } ).sort( { user_id: -1 } )
  1. SELECT COUNT(*)FROM people
  1. db.people.count()
_or
  1. db.people.find().count()
  1. SELECT COUNT(userid)FROM people
  1. db.people.count( { user_id: { $exists: true } } )
_or
  1. db.people.find( { userid: { $exists: true } } ).count()
  1. SELECT COUNT(*)FROM peopleWHERE age > 30
  1. db.people.count( { age: { $gt: 30 } } )
_or
  1. db.people.find( { age: { $gt: 30 } } ).count()
  1. SELECT DISTINCT(status)FROM people
  1. db.people.aggregate( [ { $group : { id : "$status" } } ] )
or, for distinct value sets that do not exceed the BSON size limit
  1. db.people.distinct( "status" )
  1. SELECT *FROM peopleLIMIT 1
  1. db.people.findOne()
_or
  1. db.people.find().limit(1)
  1. SELECT FROM peopleLIMIT 5SKIP 10
  1. db.people.find().limit(5).skip(10)
  1. EXPLAIN SELECT FROM peopleWHERE status = "A"
  1. db.people.find( { status: "A" } ).explain()

For more information on the methods and operators used, see

- db.collection.find()- db.collection.distinct()- db.collection.findOne()- limit()- skip()- explain()- sort()- count()- $ne- $and- $or- $gt- $lt- $exists- $lte- $regex

See also

Update Records

The following table presents the various SQL statements related toupdating existing records in tables and the corresponding MongoDBstatements.

SQL Update StatementsMongoDB updateMany() Statements
  1. UPDATE peopleSET status = "C"WHERE age > 25
  1. db.people.updateMany(   { age: { $gt: 25 } },   { $set: { status: "C" } })
  1. UPDATE peopleSET age = age + 3WHERE status = "A"
  1. db.people.updateMany(   { status: "A" } ,   { $inc: { age: 3 } })

For more information on the method and operators used in the examples, see:

- db.collection.updateMany()- $gt- $set- $inc

See also

Delete Records

The following table presents the various SQL statements related todeleting records from tables and the corresponding MongoDB statements.

SQL Delete StatementsMongoDB deleteMany() Statements
  1. DELETE FROM peopleWHERE status = "D"
  1. db.people.deleteMany( { status: "D" } )
  1. DELETE FROM people
  1. db.people.deleteMany({})

For more information, see db.collection.deleteMany().

See also