Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
MongoDb Cheat Sheets

SQL to MongoDB Mapping Chart

SQL to MongoDB Mapping Chart

In addition to the charts that follow, you might want to consider the Frequently Asked Questions section for a selection of common questions about MongoDB.

Executables

The following table presents the MySQL/Oracle executables and the corresponding MongoDB executables.

  MySQL/Oracle MongoDB
Database Server mysqld/oracle mongod
Database Client mysql/sqlplus mongo

Terminology and Concepts

The following table presents the various SQL terminology and concepts and the corresponding MongoDB terminology and concepts.

SQL Terms/Concepts MongoDB Terms/Concepts
database database
table collection
row document or BSON document
column field
index index
table joins embedded documents and linking

primary key

Specify any unique column or column combination as primary key.

primary key

In MongoDB, the primary key is automatically set to the _id field.

aggregation (e.g. group by)

aggregation framework

See the SQL to Aggregation Framework Mapping Chart.

Examples

The following table presents the various SQL statements and the corresponding MongoDB statements. The examples in the table assume the following conditions:

  • The SQL examples assume a table named users.

  • The MongoDB examples assume a collection named users that contain documents of the following prototype:

    {
      _id: ObjectID("509a8fb2f3f4948bd2f983a0"),
      user_id: "abc123",
      age: 55,
      status: 'A'
    }
    

Create and Alter

The following table presents the various SQL statements related to table-level actions and the corresponding MongoDB statements.

SQL Schema Statements MongoDB Schema Statements Reference
CREATE TABLE users (
    id MEDIUMINT NOT NULL
        AUTO_INCREMENT,
    user_id Varchar(30),
    age Number,
    status char(1),
    PRIMARY KEY (id)
)

Implicitly created on first insert operation. The primary key _id is automatically added if _id field is not specified.

db.users.insert( {
    user_id: "abc123",
    age: 55,
    status: "A"
 } )

However, you can also explicitly create a collection:

db.createCollection("users")
See insert() and createCollection() for more information.
ALTER TABLE users
ADD join_date DATETIME
Collections do not describe or enforce the structure of the constituent documents. See the Schema Design wiki page for more information. See update() and $set for more information on changing the structure of documents in a collection.
ALTER TABLE users
DROP COLUMN join_date
Collections do not describe or enforce the structure of the constituent documents. See the Schema Design wiki page for more information. See update() and $set for more information on changing the structure of documents in a collection.
CREATE INDEX idx_user_id_asc
ON users(user_id)
db.users.ensureIndex( { user_id: 1 } )
See ensureIndex() and indexes for more information.
CREATE INDEX
       idx_user_id_asc_age_desc
ON users(user_id, age DESC)
db.users.ensureIndex( { user_id: 1, age: -1 } )
See ensureIndex() and indexes for more information.
DROP TABLE users
db.users.drop()
See drop() for more information.

Insert

The following table presents the various SQL statements related to inserting records into tables and the corresponding MongoDB statements.

SQL INSERT Statements MongoDB insert() Statements Reference
INSERT INTO users(user_id,
                  age,
                  status)
VALUES ("bcd001",
        45,
        "A")
db.users.insert( {
       user_id: "bcd001",
       age: 45,
       status: "A"
} )
See insert() for more information.

Select

The following table presents the various SQL statements related to reading records from tables and the corresponding MongoDB statements.

SQL SELECT Statements MongoDB find() Statements Reference
SELECT *
FROM users
db.users.find()
See find() for more information.
SELECT id, user_id, status
FROM users
db.users.find(
    { },
    { user_id: 1, status: 1 }
)
See find() for more information.
SELECT user_id, status
FROM users
db.users.find(
    { },
    { user_id: 1, status: 1, _id: 0 }
)
See find() for more information.
SELECT *
FROM users
WHERE status = "A"
db.users.find(
    { status: "A" }
)
See find() for more information.
SELECT user_id, status
FROM users
WHERE status = "A"
db.users.find(
    { status: "A" },
    { user_id: 1, status: 1, _id: 0 }
)
See find() for more information.
SELECT *
FROM users
WHERE status != "A"
db.users.find(
    { status: { $ne: "A" } }
)
See find() and $ne for more information.
SELECT *
FROM users
WHERE status = "A"
AND age = 50
db.users.find(
    { status: "A",
      age: 50 }
)
See find() and $and for more information.
SELECT *
FROM users
WHERE status = "A"
OR age = 50
db.users.find(
    { $or: [ { status: "A" } ,
             { age: 50 } ] }
)
See find() and $or for more information.
SELECT *
FROM users
WHERE age > 25
db.users.find(
    { age: { $gt: 25 } }
)
See find() and $gt for more information.
SELECT *
FROM users
WHERE age < 25
db.users.find(
   { age: { $lt: 25 } }
)
See find() and $lt for more information.
SELECT *
FROM users
WHERE age > 25
AND   age <= 50
db.users.find(
   { age: { $gt: 25, $lte: 50 } }
)
See find(), $gt, and $lte for more information.
SELECT *
FROM users
WHERE user_id like "%bc%"
db.users.find(
   { user_id: /bc/ }
)
See find() and $regex for more information.
SELECT *
FROM users
WHERE user_id like "bc%"
db.users.find(
   { user_id: /^bc/ }
)
See find() and $regex for more information.
SELECT *
FROM users
WHERE status = "A"
ORDER BY user_id ASC
db.users.find( { status: "A" } ).sort( { user_id: 1 } )
See find() and sort() for more information.
SELECT *
FROM users
WHERE status = "A"
ORDER BY user_id DESC
db.users.find( { status: "A" } ).sort( { user_id: -1 } )
See find() and sort() for more information.
SELECT COUNT(*)
FROM users
db.users.count()

or

db.users.find().count()
See find() and count() for more information.
SELECT COUNT(user_id)
FROM users
db.users.count( { user_id: { $exists: true } } )

or

db.users.find( { user_id: { $exists: true } } ).count()
See find(), count(), and $exists for more information.
SELECT COUNT(*)
FROM users
WHERE age > 30
db.users.count( { age: { $gt: 30 } } )

or

db.users.find( { age: { $gt: 30 } } ).count()
See find(), count(), and $gt for more information.
SELECT DISTINCT(status)
FROM users
db.users.distinct( "status" )
See find() and distinct() for more information.
SELECT *
FROM users
LIMIT 1
db.users.findOne()

or

db.users.find().limit(1)
See find(), findOne(), and limit() for more information.
SELECT *
FROM users
LIMIT 5
SKIP 10
db.users.find().limit(5).skip(10)
See find(), limit(), and skip() for more information.
EXPLAIN SELECT *
FROM users
WHERE status = "A"
db.users.find( { status: "A" } ).explain()
See find() and explain() for more information.

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 update() Statements Reference
UPDATE users
SET status = "C"
WHERE age > 25
db.users.update(
   { age: { $gt: 25 } },
   { $set: { status: "C" } },
   { multi: true }
)
See update(), $gt, and $set for more information.
UPDATE users
SET age = age + 3
WHERE status = "A"
db.users.update(
   { status: "A" } ,
   { $inc: { age: 3 } },
   { multi: true }
)
See update(), $inc, and $set for more information.

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 remove() Statements Reference
DELETE FROM users
WHERE status = "D"
db.users.remove( { status: "D" } )
See remove() for more information.
DELETE FROM users
db.users.remove( )
See remove() for more information.
@ankitsari
Copy link

ankitsari commented Jun 23, 2017

Hi
UPDATE tb1 SET col1 = co1.toLowerCase(), col2 = col2.toLowerCase()

I need convert query into mongodb. is it possible to without foreach function.? or can we do using aggregate function with update.

Loading

@imbenwolf
Copy link

imbenwolf commented Aug 18, 2017

Hi
UPDATE tb1 SET col1 = co1.toLowerCase(), col2 = col2.toLowerCase()

I need convert query into mongodb. is it possible to without foreach function.? or can we do using aggregate function with update.

this link should do the trick: https://docs.mongodb.com/manual/reference/operator/update/rename/

Loading

Copy link

ghost commented Jun 2, 2018

thanks but what about querying and embeded objects ?

Loading

@Nazi89
Copy link

Nazi89 commented Jul 7, 2018

Thanx Mate, really helpful...!

Loading

@anopperl
Copy link

anopperl commented Oct 8, 2018

thanks

Loading

@talha08
Copy link

talha08 commented Dec 17, 2018

Great work man (y)

Loading

@kalaiganeshan
Copy link

kalaiganeshan commented Feb 5, 2019

It will be more helpful if the reference links are updated

Loading

@NJ2046
Copy link

NJ2046 commented May 5, 2019

basic thk

Loading

@jafferali201211
Copy link

jafferali201211 commented Jun 29, 2019

Nice to recall method

Loading

@robihamdani
Copy link

robihamdani commented Jul 5, 2019

thank very helpfull

Loading

@vitorsilv
Copy link

vitorsilv commented Jul 26, 2019

nice work!

Loading

@tuannguyen29
Copy link

tuannguyen29 commented Nov 26, 2019

thanks you

Loading

@dannoso
Copy link

dannoso commented Jan 23, 2020

Adding the JOIN methods equivalents would be great!!

Loading

@jafferali201211
Copy link

jafferali201211 commented Jan 23, 2020

Loading

@dzelionis
Copy link

dzelionis commented Feb 24, 2020

Hey dude, do you know that we call python lazy programming language? I hope, you do...So i am one of these lazy programmers...And i will be doing a talk in Pycon Limerick 2020 (Ireland) on 29th of February. Talk is wrapped around Flask-AppBuilder (again, some framework for laziness, as i claiming that in 4 lines of python code i can get website up which has user access built in and also will give me a listing of data out of mongoDB ), and my question would be...
Could i reference to your stuff in my slides. Because, its pointless for me to do what you just did...:) and i think you did it very well...
With reference, I mean put somewhere in the end:
"If you about to migrate some stuff from SQL to noSQL you should check this: https://gist.github.com/aponxi/4380516"
I hope you ok with that, if not - sue me! :)
Thanks

Loading

@Anushan1508
Copy link

Anushan1508 commented Mar 14, 2021

Nice Work, Thanks for helping

Loading

@JaymesKat
Copy link

JaymesKat commented Sep 9, 2021

Great work, this is super valuable.

Loading

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment