Javatpoint Logo
Javatpoint Logo

SQL to MongoDB Mapping

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

SQL Terms MongoDB Terms
database Database
table Collection
row document or BSON document
column field
index index
table joins $lookup, embedded document
primary key primary 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.
aggregation aggregation pipeline
SELECT INTO NEW_TABLE $out
MERGE INTO TABLE $merge
transactions transactions

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 statements MongoDB 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 statement MongoDB 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 Statement MongoDB 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 Statements MongoDB 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 Statements MongoDB deleteMany() Statements
DELETE FROM JavaTpoint WHERE status = "D"
db.JavaTpoint.deleteMany( { status: "D" } )
DELETE FROM JavaTpoint
db.JavaTpoint.deleteMany( { } )





Help Others, Please Share

facebook twitter pinterest

Learn Latest Tutorials


Preparation


Trending Technologies


B.Tech / MCA