Select
The following table presents the various SQL statements related toreading 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 formore 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()andsort()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()andsort()for more information. |
SELECT COUNT(*) FROM users |
db.users.count() or db.users.find().count() |
See find()andcount() formore 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()anddistinct()for more information. |
SELECT * FROM users LIMIT 1 |
db.users.findOne() or db.users.find().limit(1) |
See find(),findOne(),andlimit()for more information. |
SELECT * FROM users LIMIT 5 SKIP 10 |
db.users.find().limit(5).skip(10) |
See find(),limit(), andskip() formore information. |
EXPLAIN SELECT * FROM users WHERE status = "A" |
db.users.find( { status: "A" } ).explain(
|