SQL to MongoDB Mapping

The table below presents the various SQL terminology and concepts, which are similar to MongoDB terminology and concepts.

SQL TermsMongoDB Terms
databaseDatabase
tableCollection
rowdocument or BSON document
columnfield
indexindex
table joins$lookup, embedded document
primary keyprimary key
In SQL, we can specify any unique column or column combination as the primary key.In MongoDB, we don't need to set the primary key. The _id field is automatically set to the primary key.
aggregationaggregation pipeline
SELECT INTO NEW_TABLE$out
MERGE INTO TABLE$merge
transactionstransactions

Examples below represent various SQL statements and similar MongoDB statements.

The examples in the table assume the following conditions:

  • The SQL example assumes a table name JavaTpoint
  • The MongoDB examples assume a collection named JavaTpoint that contain documents of the following prototype:

Create and Alter commands

SQL statementsMongoDB statements
CREATE TABLE JavaTpoint (
    id MEDIUMINT NOT NULL
        AUTO_INCREMENT,
    user_id Varchar(20),
    age Number,
    status char(1),
    PRIMARY KEY (id)
)
db.createCollection ( " JavaTpoint " )
ALTER TABLE JavaTpoint ADD join_date DATETIME
db.JavaTpoint.updateMany(
    { },
    { $set: { join_date: new Date() } }
)
ALTER TABLE JavaTpoint DROP COLUMN join_date
db.JavaTpoint.updateMany(
    { },
    { $unset: { "join_date": "" } }
)
CREATE INDEX idx_user_id_asc ON JavaTpoint ( user_id )
db.people.createIndex ( { user_id: 1 } )
CREATE INDEX idx_user_id_asc ON people (user_id)
db.people.createIndex( { user_id: 123, age: 1} )
DROP TABLE people
db.people.drop ()

MongoDB and SQL Insert Statement

SQL Insert statementMongoDB insert statement
INSERT INTO JavaTpoint (user_id,
                  age,
                  status)
VALUES ("mongo",
        45,
        "A")
db.JavaTpoint.insertOne(
   { user_id: "mongo", age: 18, status: "A" }
)

SQL and Mongo DB Select Command

SQL SELECT StatementMongoDB find() Statement
SELECT *
FROM JavaTpoint
db.JavaTpoint.find()
SELECT id, user_id, status FROM JavaTpoint
db.JavaTpoint.find( { }, { user_id: 1, status: 1 } )
SELECT user_id, status FROM JavaTpoint
db.JavaTpoint.find( { }, { user_id: 1, status: 1, _id: 0 } )
SELECT * FROM JavaTpoint WHERE status = "B"
db.JavaTpoint.find( { status: "A" } )
SELECT user_id, status FROM JavaTpoint WHERE status = "A"
db.javaTpoint.find( { status: "A" }, { user_id: 1, status: 1, _id: 0 } )
SELECT * FROM JavaTpoint WHERE status != "A"
db.JavaTpoint.find( { status: { $ne: "A" } } )
SELECT *
FROM JavaTpoint
WHERE status = "A"
AND age = 50
db.JavaTpoint.find(
    { status: "A",
      age: 50 }
)
SELECT *
FROM JavaTpoint
WHERE status = "A"
OR age = 50
db.JavaTpoint.find(
    { $or: [ { status: "A" } , { age: 50 } ] }
)
SELECT *
FROM JavaTpoint
WHERE age > 25
db.JavaTpoint.find(
    { age: { $gt: 25 } }
)
SELECT *
FROM JavaTpoint
WHERE age < 25
Db.JavaTpoint.find(
   { age: { $lt: 25 } }
)
SELECT *
FROM JavaTpoint
WHERE age > 25
AND   age <= 50
db.JavaTpoint.find(
   { age: { $gt: 25, $lte: 50 } }
)
SELECT *
FROM JavaTpoint
WHERE user_id like "%bc%"
db.JavaTpoint.find( { user_id: /bc/ } )
-or-

db.JavaTpoint.find( { user_id: { $regex: /bc/ } } )
SELECT *
FROM JavaTpoint
WHERE user_id like "bc%"
db.JavaTpoint.find( { user_id: /^bc/ } )
-or-

db.JavaTpoint.find( { user_id: { $regex: /^bc/ } } )
SELECT *
FROM JavaTPoint
WHERE status = "A"
ORDER BY user_id ASC
db. JavaTPoint. find( { status: "A" } ). sort( { user_id: 1 } )
SELECT *
FROM JavaTPoint
WHERE status = "A"
ORDER BY user_id ASC
db. JavaTPoint. find( { status: "A" } ). sort( { user_id: 1 } )
SELECT *
FROM JavaTPoint
WHERE status = "A"
ORDER BY user_id ASC
db. JavaTPoint. find( { status: "A" } ). sort( { user_id: 1 } )
SELECT *
FROM JavaTPoint
WHERE status = "A"
ORDER BY user_id DESC
db. JavaTPoint. find( { status: "A" } ). sort( { user_id: -1 } )
SELECT *
FROM JavaTPoint
WHERE status = "A"
ORDER BY user_id DESC
db. JavaTPoint. find( { status: "A" } ). sort( { user_id: -1 } )
SELECT COUNT(*)
FROM JavaTPoint
db. JavaTPoint. count()
or

db. JavaTPoint. find(). count()
SELECT COUNT(user_id)
FROM JavaTPoint
db. JavaTPoint.count( { user_id: { $exists: true } } )
or

db. JavaTPoint.find( { user_id: { $exists: true } } ).count()
SELECT COUNT(*)
FROM JavaTPoint
WHERE age > 30
db. JavaTPoint.count( { age: { $gt: 30 } } )
or

db. JavaTPoint.find( { age: { $gt: 30 } } ).count()
SELECT DISTINCT(status)
FROM JavaTPoint
db. JavaTPoint.aggregate( [ { $group : { _id : "$status" } } ] )
or, for distinct value sets that do not exceed the BSON size limit

db. JavaTPoint.distinct( "status" )
SELECT *
FROM JavaTPoint
LIMIT 1
db. JavaTPoint.findOne()
or

db. JavaTPoint.find(). limit(1)
SELECT *
FROM JavaTPoint
LIMIT 5
SKIP 10
db. JavaTPoint.find(). limit(5). skip(10)
EXPLAIN SELECT *
FROM JavaTPoint WHERE status = "A"
db. JavaTPoint. find( { status: "A" } ). explain()

SQL and MongoDB Update Statements

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

SQL and MongoDB Delete Statements

SQL Delete StatementsMongoDB deleteMany() Statements
DELETE FROM JavaTpoint WHERE status = "D"
db.JavaTpoint.deleteMany( { status: "D" } )
DELETE FROM JavaTpoint
db.JavaTpoint.deleteMany( { } )





Latest Courses