101
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