MongoDB Comparison Query Operators - $gt, $lt, $gte, $lte
Description
A comparison operator compares two expressions and fetched documents from mongodb collection. In this page we are going to discuss about the comparison operators and usage of comparison operators.
In MongoDB the conditional operators are :
(>) greater than - $gt
(<) less than - $lt
(>=) greater than equal to - $gte
(<= ) less than equal to - $lte
Our database name is 'myinfo' and our collection name is 'testtable'. Here, is the collection bellow.
Sample collection "testtable"
{
"_id" : ObjectId("528f34950fe5e6467e58ae77"),
"user_id" : "user1",
"password" : "1a2b3c",
"sex" : "Male",
"age" : 17,
"date_of_join" : "16/10/2010",
"education" : "M.C.A.",
"profession" : "CONSULTANT",
"interest" : "MUSIC",
"extra" : {
"community_name" : [
"MODERN MUSIC",
"CLASSICAL MUSIC",
"WESTERN MUSIC"
],
"community_moder_id" : [
"MR. Alex",
"MR. Dang",
"MR Haris"
],
"community_members" : [
700,
200,
1500
],
"friends" : {
"valued_friends_id" : [
"kumar",
"harry",
"anand"
],
"ban_friends_id" : [
"Amir",
"Raja",
"mont"
]
}
}
}
{
"_id" : ObjectId("528f34fa0fe5e6467e58ae78"),
"user_id" : "user2",
"password" : "11aa1a",
"sex" : "Male",
"age" : 24,
"date_of_join" : "17/10/2009",
"education" : "M.B.A.",
"profession" : "MARKETING",
"interest" : " MUSIC",
"extra" : {
"community_name" : [
"MODERN MUSIC",
"CLASSICAL MUSIC",
"WESTERN MUSIC"
],
"co mmunity_moder_id" : [
"MR. Roy",
"MR. Das",
"MR Doglus"
],
"community_members" : [
500,
300,
1400
],
"friends" : {
"valued_friends_id" : [
"pal",
"viki",
"john"
],
"ban_friends_id" : [
"jalan",
"mono j",
"evan"
]
}
}
}
{
"_id" : ObjectId("528f35450fe5e6467e58ae79"),
"user_id" : "user3",
"password" : "b1c1d1",
"sex" : "Female",
"age" : 19,
"date_of_join" : "16/10/2010",
"education" : "M.C.A.",
"profession" : "IT COR.",
"interest" : "AR T",
"extra" : {
"community_name" : [
"MODERN ART",
"CLASSICAL ART",
"WESTERN ART"
],
"community_mo der_id" : [
"MR. Rifel",
"MR. Sarma",
"MR Bhatia"
],
"community_members" : [
5000,
2000,
1500
],
"friends" : {
"valued_friends_id" : [
"philip",
"anant",
"alan"
],
"ban_friends_id" : [
"Amir",
"Raja",
"mont"
]
}
}
}
{
"_id" : ObjectId("528f35860fe5e6467e58ae7a"),
"user_id" : "user4",
"password" : "abczyx",
"sex" : "Female",
"age" : 22,
"date_of_join" : "17/8/2009",
"education" : "M.B.B.S.",
"profession" : "DOCTOR",
"interest" : "SPORTS",
"extra" : {
"community_name" : [
"ATHELATIC",
"GAMES FAN GYES",
"FAVOURIT GAMES"
],
"community_moder_id" : [
"MR. Paul",
"MR. Das",
"MR Doglus"
],
"community_members" : [
2500,
2200,
3500
],
"friends" : {
"valued_friends_id" : [
"vinod",
"viki",
"john"
],
"ban_friends_id" : [
"jalan",
"monoj",
"evan"
]
}
}
}
Document written in command prompt.
MongoDB (>) greater than operator - $gt
If we want to fetch documents from the collection "testtable" which contains the value of "age " is more than 22, the following mongodb command can be used :
>db.testtable.find({age : {$gt : 22}}).pretty();
N.B. find() method displays the documents in a non structured format but to display the results in a formatted way, the pretty() method can be used.
Sql equivalent command is
SELECT * FROM testtable WHERE age >22;
Output of the command
{
"_id" : ObjectId("528f34fa0fe5e6467e58ae78"),
"user_id" : "user2",
"password" : "11aa1a",
"sex" : "Male",
"age" : 24,
"date_of_join" : "17/10/2009",
"education" : "M.B.A.",
"profession" : "MARKETING",
"interest" : " MUSIC",
"extra" : {
"community_name" : [
"MODERN MUSIC",
"CLASSICAL MUSIC",
"WESTERN MUSIC"
],
"co mmunity_moder_id" : [
"MR. Roy",
"MR. Das",
"MR Doglus"
],
"community_members" : [
500,
300,
1400
],
"friends" : {
"valued_friends_id" : [
"pal",
"viki",
"john"
],
"ban_friends_id" : [
"jalan",
"mono j",
"evan"
]
}
}
}
Document written in command prompt.
MongoDB (>=) greater than equal to operator - $gte
If we want to fetch documents from the collection "testtable" which contains the value of "age " is more than or equal to 22, the following mongodb command can be used :
>db.testtable.find({age : {$gte : 22}}).pretty();
N.B. find() method displays the documents in a non structured format but to display the results in a formatted way, the pretty() method can be used.
Sql equivalent command is
SELECT * FROM testtable WHERE age >=22;
Output of the command
{
"_id" : ObjectId("528f34fa0fe5e6467e58ae78"),
"user_id" : "user2",
"password" : "11aa1a",
"sex" : "Male",
"age" : 24,
"date_of_join" : "17/10/2009",
"education" : "M.B.A.",
"profession" : "MARKETING",
"interest" : " MUSIC",
"extra" : {
"community_name" : [
"MODERN MUSIC",
"CLASSICAL MUSIC",
"WESTERN MUSIC"
],
"co mmunity_moder_id" : [
"MR. Roy",
"MR. Das",
"MR Doglus"
],
"community_members" : [
500,
300,
1400
],
"friends" : {
"valued_friends_id" : [
"pal",
"viki",
"john"
],
"ban_friends_id" : [
"jalan",
"mono j",
"evan"
]
}
}
}
{
"_id" : ObjectId("528f35860fe5e6467e58ae7a"),
"user_id" : "user4",
"password" : "abczyx",
"sex" : "Female",
"age" : 22,
"date_of_join" : "17/8/2009",
"education" : "M.B.B.S.",
"profession" : "DOCTOR",
"interest" : "SPORTS",
"extra" : {
"community_name" : [
"ATHELATIC",
"GAMES FAN GYES",
"FAVOURIT GAMES"
],
"community_moder_id" : [
"MR. Paul",
"MR. Das",
"MR Doglus"
],
"community_members" : [
2500,
2200,
3500
],
"friends" : {
"valued_friends_id" : [
"vinod",
"viki",
"john"
],
"ban_friends_id" : [
"jalan",
"monoj",
"evan"
]
}
}
}
Document written in command prompt.
MongoDB (<) less than operator - $lt
If we want to fetch documents from the collection "testtable" which contains the value of "age " is less than 19, the following mongodb command can be used :
>db.testtable.find({age : {$lt : 19}}).pretty();
N.B. find() method displays the documents in a non structured format but to display the results in a formatted way, the pretty() method can be used.
Sql equivalent command is
SELECT * FROM testtable WHERE age <19;
Output of the command
{
"_id" : ObjectId("528f34950fe5e6467e58ae77"),
"user_id" : "user1",
"password" : "1a2b3c",
"sex" : "Male",
"age" : 17,
"date_of_join" : "16/10/2010",
"education" : "M.C.A.",
"profession" : "CONSULTANT",
"interest" : "MUSIC",
"extra" : {
"community_name" : [
"MODERN MUSIC",
"CLASSICAL MUSIC",
"WESTERN MUSIC"
],
"community_moder_id" : [
"MR. Alex",
"MR. Dang",
"MR Haris"
],
"community_members" : [
700,
200,
1500
],
"friends" : {
"valued_friends_id" : [
"kumar",
"harry",
"anand"
],
"ban_friends_id" : [
"Amir",
"Raja",
"mont"
]
}
}
}
Document written in command prompt.
MongoDB (<=) less than equal to operator - $lte
If we want to fetch documents from the collection "testtable" which contains the value of "age " is less than or equal to 19, the following mongodb command can be used :
>db.testtable.find({age : {$lte : 19}}).pretty();
N.B. find() method displays the documents in a non structured format but to display the results in a formatted way, the pretty() method can be used.
Sql equivalent command is
SELECT * FROM testtable WHERE age <=19;
Output of the command
{
"_id" : ObjectId("528f34950fe5e6467e58ae77"),
"user_id" : "user1",
"password" : "1a2b3c",
"sex" : "Male",
"age" : 17,
"date_of_join" : "16/10/2010",
"education" : "M.C.A.",
"profession" : "CONSULTANT",
"interest" : "MUSIC",
"extra" : {
"community_name" : [
"MODERN MUSIC",
"CLASSICAL MUSIC",
"WESTERN MUSIC"
],
"community_moder_id" : [
"MR. Alex",
"MR. Dang",
"MR Haris"
],
"community_members" : [
700,
200,
1500
],
"friends" : {
"valued_friends_id" : [
"kumar",
"harry",
"anand"
],
"ban_friends_id" : [
"Amir",
"Raja",
"mont"
]
}
}
}
{
"_id" : ObjectId("528f35450fe5e6467e58ae79"),
"user_id" : "user3",
"password" : "b1c1d1",
"sex" : "Female",
"age" : 19,
"date_of_join" : "16/10/2010",
"education" : "M.C.A.",
"profession" : "IT COR.",
"interest" : "AR T",
"extra" : {
"community_name" : [
"MODERN ART",
"CLASSICAL ART",
"WESTERN ART"
],
"community_mo der_id" : [
"MR. Rifel",
"MR. Sarma",
"MR Bhatia"
],
"community_members" : [
5000,
2000,
1500
],
"friends" : {
"valued_friends_id" : [
"philip",
"anant",
"alan"
],
"ban_friends_id" : [
"Amir",
"Raja",
"mont"
]
}
}
}
Document written in command prompt.
MongoDB query using (<) and (>) operator - $lt and $gt
If we want to fetch documents from the collection "testtable" which contains the value of "age " is greater than 17 and less than 24, the following mongodb command can be used :
>db.testtable.find({age : {$lt :24, $gt : 17}}).pretty();
N.B. find() method displays the documents in a non structured format but to display the results in a formatted way, the pretty() method can be used.
Sql equivalent command is
SELECT * FROM testtable WHERE age <24 and age>17;
Output of the command:
{
"_id" : ObjectId("528f35450fe5e6467e58ae79"),
"user_id" : "user3",
"password" : "b1c1d1",
"sex" : "Female",
"age" : 19,
"date_of_join" : "16/10/2010",
"education" : "M.C.A.",
"profession" : "IT COR.",
"interest" : "AR T",
"extra" : {
"community_name" : [
"MODERN ART",
"CLASSICAL ART",
"WESTERN ART"
],
"community_mo der_id" : [
"MR. Rifel",
"MR. Sarma",
"MR Bhatia"
],
"community_members" : [
5000,
2000,
1500
],
"friends" : {
"valued_friends_id" : [
"philip",
"anant",
"alan"
],
"ban_friends_id" : [
"Amir",
"Raja",
"mont"
]
}
}
}
{
"_id" : ObjectId("528f35860fe5e6467e58ae7a"),
"user_id" : "user4",
"password" : "abczyx",
"sex" : "Female",
"age" : 22,
"date_of_join" : "17/8/2009",
"education" : "M.B.B.S.",
"profession" : "DOCTOR",
"interest" : "SPORTS",
"extra" : {
"community_name" : [
"ATHELATIC",
"GAMES FAN GYES",
"FAVOURIT GAMES"
],
"community_moder_id" : [
"MR. Paul",
"MR. Das",
"MR Doglus"
],
"community_members" : [
2500,
2200,
3500
],
"friends" : {
"valued_friends_id" : [
"vinod",
"viki",
"john"
],
"ban_friends_id" : [
"jalan",
"monoj",
"evan"
]
}
}
}
Document written in command prompt.
Previous:
MongoDB Query and Projection Operators/a>
Next:
$ne
It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.
https://w3resource.com/mongodb/mongodb-conditional-operators.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics