139
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 tutoraspire
- The MongoDB examples assume a collection named tutoraspire that contain documents of the following prototype:
Create and Alter commands
SQL statements | MongoDB statements |
---|---|
CREATE TABLE tutoraspire ( id MEDIUMINT NOT NULL AUTO_INCREMENT, user_id Varchar(20), age Number, status char(1), PRIMARY KEY (id) ) | db.createCollection ( " tutoraspire " ) |
ALTER TABLE tutoraspire ADD join_date DATETIME | db.tutoraspire.updateMany( { }, { $set: { join_date: new Date() } } ) |
ALTER TABLE tutoraspire DROP COLUMN join_date | db.tutoraspire.updateMany( { }, { $unset: { "join_date": "" } } ) |
CREATE INDEX idx_user_id_asc ON tutoraspire ( 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 tutoraspire (user_id, age, status) VALUES ("mongo", 45, "A") | db.tutoraspire.insertOne( { user_id: "mongo", age: 18, status: "A" } ) |
SQL and Mongo DB Select Command
SQL SELECT Statement | MongoDB find() Statement |
---|---|
SELECT * FROM tutoraspire | db.tutoraspire.find() |
SELECT id, user_id, status FROM tutoraspire | db.tutoraspire.find( { }, { user_id: 1, status: 1 } ) |
SELECT user_id, status FROM tutoraspire | db.tutoraspire.find( { }, { user_id: 1, status: 1, _id: 0 } ) |
SELECT * FROM tutoraspire WHERE status = "B" | db.tutoraspire.find( { status: "A" } ) |
SELECT user_id, status FROM tutoraspire WHERE status = "A" | db.tutoraspire.find( { status: "A" }, { user_id: 1, status: 1, _id: 0 } ) |
SELECT * FROM tutoraspire WHERE status != "A" | db.tutoraspire.find( { status: { $ne: "A" } } ) |
SELECT * FROM tutoraspire WHERE status = "A" AND age = 50 | db.tutoraspire.find( { status: "A", age: 50 } ) |
SELECT * FROM tutoraspire WHERE status = "A" OR age = 50 | db.tutoraspire.find( { $or: [ { status: "A" } , { age: 50 } ] } ) |
SELECT * FROM tutoraspire WHERE age > 25 | db.tutoraspire.find( { age: { $gt: 25 } } ) |
SELECT * FROM tutoraspire WHERE age < 25 | Db.tutoraspire.find( { age: { $lt: 25 } } ) |
SELECT * FROM tutoraspire WHERE age > 25 AND age <= 50 | db.tutoraspire.find( { age: { $gt: 25, $lte: 50 } } ) |
SELECT * FROM tutoraspire WHERE user_id like "%bc%" | db.tutoraspire.find( { user_id: /bc/ } ) -or- db.tutoraspire.find( { user_id: { $regex: /bc/ } } ) |
SELECT * FROM tutoraspire WHERE user_id like "bc%" | db.tutoraspire.find( { user_id: /^bc/ } ) -or- db.tutoraspire.find( { user_id: { $regex: /^bc/ } } ) |
SELECT * FROM tutoraspire WHERE status = "A" ORDER BY user_id ASC | db. tutoraspire. find( { status: "A" } ). sort( { user_id: 1 } ) |
SELECT * FROM tutoraspire WHERE status = "A" ORDER BY user_id ASC | db. tutoraspire. find( { status: "A" } ). sort( { user_id: 1 } ) |
SELECT * FROM tutoraspire WHERE status = "A" ORDER BY user_id ASC | db. tutoraspire. find( { status: "A" } ). sort( { user_id: 1 } ) |
SELECT * FROM tutoraspire WHERE status = "A" ORDER BY user_id DESC | db. tutoraspire. find( { status: "A" } ). sort( { user_id: -1 } ) |
SELECT * FROM tutoraspire WHERE status = "A" ORDER BY user_id DESC | db. tutoraspire. find( { status: "A" } ). sort( { user_id: -1 } ) |
SELECT COUNT(*) FROM tutoraspire | db. tutoraspire. count() or db. tutoraspire. find(). count() |
SELECT COUNT(user_id) FROM tutoraspire | db. tutoraspire.count( { user_id: { $exists: true } } ) or db. tutoraspire.find( { user_id: { $exists: true } } ).count() |
SELECT COUNT(*) FROM tutoraspire WHERE age > 30 | db. tutoraspire.count( { age: { $gt: 30 } } ) or db. tutoraspire.find( { age: { $gt: 30 } } ).count() |
SELECT DISTINCT(status) FROM tutoraspire | db. tutoraspire.aggregate( [ { $group : { _id : "$status" } } ] ) or, for distinct value sets that do not exceed the BSON size limit db. tutoraspire.distinct( "status" ) |
SELECT * FROM tutoraspire LIMIT 1 | db. tutoraspire.findOne() or db. tutoraspire.find(). limit(1) |
SELECT * FROM tutoraspire LIMIT 5 SKIP 10 | db. tutoraspire.find(). limit(5). skip(10) |
EXPLAIN SELECT * FROM tutoraspire WHERE status = "A" | db. tutoraspire. find( { status: "A" } ). explain() |
SQL and MongoDB Update Statements
SQL Update Statements | MongoDB updateMany() Statements |
---|---|
UPDATE tutoraspire SET status = "C" WHERE age > 25 | db.tutoraspire.updateMany( { age: { $gt: 25 } }, { $set: { status: "C" } } ) |
UPDATE tutoraspire SET age = age + 3 WHERE status = "A" | db.tutoraspire.updateMany( { status: "A" } , { $inc: { age: 3 } } ) |
SQL and MongoDB Delete Statements
SQL Delete Statements | MongoDB deleteMany() Statements |
---|---|
DELETE FROM tutoraspire WHERE status = "D" | db.tutoraspire.deleteMany( { status: "D" } ) |
DELETE FROM tutoraspire | db.tutoraspire.deleteMany( { } ) |
Next TopicMongoDB text search