How to perform SQL where clause equivalents in MongoDB
Comparison query operators provides a way to perform SQL where clause equivalents in MongoDB. This tutorial explains you on how to perform query on specific operators, including syntax and examples.
Let us insert the following documents in a collection called “posts” before we run the following examples.
db.posts.insert({"_id":"5063114bd386d8fadbd6b004","title":"MongoDB OverView","description":"Overview and Introduction about MongoDB","by_user":"mongodb","url":"www.docs.mongodb.com","tags":["#mongodb","#mongo_tutor","#tutorials"],"likes":20,"comments":[{"user":"Mishra","message":"Nice tutorial","dateCreated":"2011-02-25T02:15:00.000Z","like":10},{"user":"Sonam","message":"Not completely correct. Partially right.","dateCreated":"2011-02-25T02:15:00.000Z","like":12}]})
db.posts.insert({"_id":"5063114bd386d8fadbd6b005","title":"MongoDB OverView 2","description":"2 Overview and Introduction about MongoDB","by_user":"mongodb","url":"www.docs.mongodb.com","tags":["#mongodb","#mongo_tutor","#tutorials"],"likes":30,"comments":[{"user":"Mishra","message":"Nice tutorial","dateCreated":"2011-02-25T02:15:00.000Z","like":10},{"user":"Sonam","message":"Not completely correct. Partially right.","dateCreated":"2011-02-25T02:15:00.000Z","like":12}]})
db.posts.insert({"_id":"5063114bd386d8fadbd6b008","title":"MongoDB Advantages","description":"Advantages of MongoDB","by_user":"sneppets","url":"https://docs.mongodb.com","tags":["#mongodb","#mongo_tutor","#tutorials"],"likes":50,"comments":[{"user":"John","message":"Nice tutorial","dateCreated":"2019-02-25T02:15:00.000Z","like":10},{"user":"Paul","message":"Good one","dateCreated":"2019-02-25T02:15:00.000Z","like":12}]})
1. Equality: $eq ( = operator in SQL WHERE Clause)
$eq comparison query operator is used to match values that are equal to a specified value. Below is the syntax and example.
Syntax: {<field>: { $eq: <value> } }
Example:
> db.posts.find({ “by_user”: { $eq: “sneppets” } }).pretty()
{
"_id" : ObjectId("5063114bd386d8fadbd6b008"),
"title" : "MongoDB Advantages",
"description" : "Advantages of MongoDB",
"by_user" : "sneppets",
"url" : "https://docs.mongodb.com",
"tags" : [
"#mongodb",
"#mongo_tutor",
"#tutorials"
],
"likes" : 50,
"comments" : [
{
"user" : "John",
"message" : "Nice tutorial",
"dateCreated" : ISODate("2019-02-25T02:15:00Z"),
"like" : 10
},
{
"user" : "Paul",
"message" : "Good one",
"dateCreated" : ISODate("2019-02-25T02:15:00Z"),
"like" : 12
}
]
}
2. Less Than: $lt (< operator in SQL WHERE Clause)
$lt comparison query operator is used to match values that are less than a specified value. Below is the syntax and example.
Syntax: {<field>: { $lt: <value> } }
Example:
> db.posts.find({ “likes”: { $lt: 30 } }).pretty()
{
"_id" : ObjectId("5063114bd386d8fadbd6b004"),
"title" : "MongoDB OverView",
"description" : "Overview and Introduction about MongoDB",
"by_user" : "mongodb",
"url" : "www.docs.mongodb.com",
"tags" : [
"#mongodb",
"#mongo_tutor",
"#tutorials"
],
"likes" : 20,
"comments" : [
{
"user" : "Mishra",
"message" : "Nice tutorial",
"dateCreated" : ISODate("2011-02-25T02:15:00Z"),
"like" : 10
},
{
"user" : "Sonam",
"message" : "Not completely correct. Partially right.",
"dateCreated" : ISODate("2011-02-25T02:15:00Z"),
"like" : 12
}
]
}
3. Less Than Equals: $lte (<= operator in SQL WHERE Clause)
$lte comparison query operator is used to match values that are less than or equal to a specified value. Below is the syntax and example.
Syntax: {<field>: { $lte: <value> } }
Example:
> db.posts.find({ “likes”: { $lte: 30 } }).pretty()
{
"_id" : ObjectId("5063114bd386d8fadbd6b004"),
"title" : "MongoDB OverView",
"description" : "Overview and Introduction about MongoDB",
"by_user" : "mongodb",
"url" : "www.docs.mongodb.com",
"tags" : [
"#mongodb",
"#mongo_tutor",
"#tutorials"
],
"likes" : 20,
"comments" : [
{
"user" : "Mishra",
"message" : "Nice tutorial",
"dateCreated" : ISODate("2011-02-25T02:15:00Z"),
"like" : 10
},
{
"user" : "Sonam",
"message" : "Not completely correct. Partially right.",
"dateCreated" : ISODate("2011-02-25T02:15:00Z"),
"like" : 12
}
]
}
{
"_id" : ObjectId("5063114bd386d8fadbd6b005"),
"title" : "MongoDB OverView 2",
"description" : "2 Overview and Introduction about MongoDB",
"by_user" : "mongodb",
"url" : "www.docs.mongodb.com",
"tags" : [
"#mongodb",
"#mongo_tutor",
"#tutorials"
],
"likes" : 30,
"comments" : [
{
"user" : "Mishra",
"message" : "Nice tutorial",
"dateCreated" : ISODate("2011-02-25T02:15:00Z"),
"like" : 10
},
{
"user" : "Sonam",
"message" : "Not completely correct. Partially right.",
"dateCreated" : ISODate("2011-02-25T02:15:00Z"),
"like" : 12
}
]
}
4. Greater Than: $gt (> operator in SQL WHERE Clause)
$gt comparison query operator is used to match values that greater than a specified value. Below is the syntax and example.
Syntax: {<field>: { $gt: <value> } }
Example:
> db.posts.find({ “likes”: { $gt: 30 } }).pretty()
{
"_id" : ObjectId("5063114bd386d8fadbd6b008"),
"title" : "MongoDB Advantages",
"description" : "Advantages of MongoDB",
"by_user" : "sneppets",
"url" : "https://docs.mongodb.com",
"tags" : [
"#mongodb",
"#mongo_tutor",
"#tutorials"
],
"likes" : 50,
"comments" : [
{
"user" : "John",
"message" : "Nice tutorial",
"dateCreated" : ISODate("2019-02-25T02:15:00Z"),
"like" : 10
},
{
"user" : "Paul",
"message" : "Good one",
"dateCreated" : ISODate("2019-02-25T02:15:00Z"),
"like" : 12
}
]
}
5. Greater Than Equals: $gte (>= operator in SQL WHERE Clause)
$gte comparison query operator is used to match values that greater than or equal to a specified value. Below is the syntax and example.
Syntax: {<field>: { $gte: <value> } }
Example:
> db.posts.find({ “likes”: { $gte: 50 } }).pretty()
{
"_id" : ObjectId("5063114bd386d8fadbd6b008"),
"title" : "MongoDB Advantages",
"description" : "Advantages of MongoDB",
"by_user" : "sneppets",
"url" : "https://docs.mongodb.com",
"tags" : [
"#mongodb",
"#mongo_tutor",
"#tutorials"
],
"likes" : 50,
"comments" : [
{
"user" : "John",
"message" : "Nice tutorial",
"dateCreated" : ISODate("2019-02-25T02:15:00Z"),
"like" : 10
},
{
"user" : "Paul",
"message" : "Good one",
"dateCreated" : ISODate("2019-02-25T02:15:00Z"),
"like" : 12
}
]
}
6. Not Equals: $ne (<> operator in SQL WHERE Clause)
$ne comparison query operator is used to match values that are not equal to a specified value. Below is the syntax and example.
Syntax: {<field>: { $ne: <value> } }
Example:
> db.posts.find({ “by_user”: { $ne: “mongodb” } }).pretty()
{
"_id" : ObjectId("5063114bd386d8fadbd6b008"),
"title" : "MongoDB Advantages",
"description" : "Advantages of MongoDB",
"by_user" : "sneppets",
"url" : "https://docs.mongodb.com",
"tags" : [
"#mongodb",
"#mongo_tutor",
"#tutorials"
],
"likes" : 50,
"comments" : [
{
"user" : "John",
"message" : "Nice tutorial",
"dateCreated" : ISODate("2019-02-25T02:15:00Z"),
"like" : 10
},
{
"user" : "Paul",
"message" : "Good one",
"dateCreated" : ISODate("2019-02-25T02:15:00Z"),
"like" : 12
}
]
}
7. In: $in ( IN operator in SQL WHERE Clause)
$in comparison query operator is used to match any of the values that are specified in an array. Below is the syntax and example.
Syntax: { field: { $in: [<value1>, <value2>, ... <valueN> ] } }
Example:
> db.posts.find({ “by_user”: { $in: [“sneppets”,”sneppet”,”google”] } }).pretty()
{
"_id" : ObjectId("5063114bd386d8fadbd6b008"),
"title" : "MongoDB Advantages",
"description" : "Advantages of MongoDB",
"by_user" : "sneppets",
"url" : "https://docs.mongodb.com",
"tags" : [
"#mongodb",
"#mongo_tutor",
"#tutorials"
],
"likes" : 50,
"comments" : [
{
"user" : "John",
"message" : "Nice tutorial",
"dateCreated" : ISODate("2019-02-25T02:15:00Z"),
"like" : 10
},
{
"user" : "Paul",
"message" : "Good one",
"dateCreated" : ISODate("2019-02-25T02:15:00Z"),
"like" : 12
}
]
}
8. Not In: $nin (NOT IN operator in SQL WHERE Clause)
$nin comparison query operator matches none of the values specified in an array. Below is the syntax and example.
Syntax: { field: { $nin: [ <value1>, <value2> ... <valueN> ]} }
Example:
> db.posts.find({ “by_user”: { $nin: [“mongodb”,”google”] } }).pretty()
{
"_id" : ObjectId("5063114bd386d8fadbd6b008"),
"title" : "MongoDB Advantages",
"description" : "Advantages of MongoDB",
"by_user" : "sneppets",
"url" : "https://docs.mongodb.com",
"tags" : [
"#mongodb",
"#mongo_tutor",
"#tutorials"
],
"likes" : 50,
"comments" : [
{
"user" : "John",
"message" : "Nice tutorial",
"dateCreated" : ISODate("2019-02-25T02:15:00Z"),
"like" : 10
},
{
"user" : "Paul",
"message" : "Good one",
"dateCreated" : ISODate("2019-02-25T02:15:00Z"),
"like" : 12
}
]
}
Further Learning
- MongoDB – Advantages and When to Use ?
- E QUERY [js] SyntaxError: identifier starts immediately after numeric literal @(shell) in MongoDB