SQL to MongoDB Mapping Chart

CREATE TABLE people (
    id MEDIUMINT NOT NULL
        AUTO_INCREMENT,
    user_id Varchar(30),
    age Number,
    status char(1),
    PRIMARY KEY (id)
)

db.people.insertOne( {
    user_id: "abc123",
    age: 55,
    status: "A"
 } )
However, you can also explicitly create a collection:


db.createCollection("people")
ALTER TABLE people
ADD join_date DATETIME
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, updateMany() operations can add fields to existing documents using the $set operator.
db.people.updateMany(
    { },
    { $set: { join_date: new Date() } }
)
ALTER TABLE people
DROP COLUMN join_date
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, updateMany() operations can remove fields from documents using the $unset operator.
db.people.updateMany(
    { },
    { $unset: { "join_date": "" } }
)
CREATE INDEX idx_user_id_asc
ON people(user_id)


db.people.createIndex( { user_id: 1 } )
CREATE INDEX
       idx_user_id_asc_age_desc
ON people(user_id, age DESC)


db.people.createIndex( { user_id: 1, age: -1 } )
DROP TABLE people


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

Tip

Databases and Collections in MongoDB
Documents
Indexes
Data Modeling Concepts.

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

INSERT INTO people(user_id,
                  age,
                  status)
VALUES ("bcd001",
        45,
        "A")
db.people.insertOne(
   { user_id: "bcd001", age: 45, status: "A" }
)
For more information, see db.collection.insertOne().

Tip

Insert Documents
db.collection.insertMany()
Databases and Collections in MongoDB
Documents

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

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

Tip

Query Documents
Comparison Query Predicate Operators
mongosh Methods

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

UPDATE people
SET status = "C"
WHERE age > 25
db.people.updateMany(
   { age: { $gt: 25 } },
   { $set: { status: "C" } }
)
UPDATE people
SET age = age + 3
WHERE status = "A"
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

Tip

Update Documents
Update Operators
db.collection.updateOne()
db.collection.replaceOne()

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

DELETE FROM people
WHERE status = "D"
db.people.deleteMany( { status: "D" } )
DELETE FROM people
db.people.deleteMany({})
For more information, see db.collection.deleteMany().

Tip

Delete Documents
db.collection.deleteOne()
Back
Retryable Reads
Next
Natural Language to MongoDB