Comparison Operators
Last updated
Last updated
Comparison operators are probably the most used operators when querying any type of database provider. MongoDB provides a set of operators that can cover all type of comparisons you might need for your queries. This section presents samples for the following query operators:
Operator
Matches when
Equal
A field's value is equal to a specified value
Not Equal
A field's value is not equal to a specified value
Greater Than
A field's value is greater than a specified value
Greater Than or Equal
A field's value is greater than or equal to a specified value
Less Than
A field's value is less than a specified value
Less Than or Equal
A field's value is less than or equal to a specified value
In
A field's value is contained among specified values
Not In
A field's value is not contained among specified values
The equal operator is used to match documents having a field value equal to a specific value. You can use it for both top level and embedded documents.
Builders<T>.Filter.Eq(doc => doc.<field>, <value>)
The sample uses an equal operator to find all documents that have the profession field (top level field) equal to "Pilot".
var collection = database
.GetCollection<User>(Constants.UsersCollection);
// Case sensitive!
var equalPilotsFilter = Builders<User>.Filter
.Eq(u => u.Profession, "Pilot");
var pilots = await collection
.Find(equalPilotsFilter).ToListAsync();
var bsonCollection = database
.GetCollection<BsonDocument>(Constants.UsersCollection);
// Case sensitive matters!
var bsonEqualPilotsFilter = Builders<BsonDocument>.Filter
.Eq("profession", "Pilot");
var bsonPilots = await bsonCollection
.Find(bsonEqualPilotsFilter).ToListAsync();
db.users.find({profession: { $eq: "Pilot"}})
// or..
db.users.find({profession: "Pilot"})
--------------------
// sample matched document
{
"_id" : ObjectId("5e91e3ba3c1ba62570a67b97"),
"gender" : 0,
"firstName" : "Gilbert",
"lastName" : "Beer",
"userName" : "Gilbert43",
"avatar" : "https://s3.amazonaws.com/uifaces/faces/twitter/lingeswaran/128.jpg",
"email" : "Gilbert_Beer@yahoo.com",
"dateOfBirth" : ISODate("1950-06-07T20:53:27.758+02:00"),
"address" : {
"street" : "3952 Felicita Garden",
"suite" : "Suite 048",
"city" : "North Pasqualefort",
"state" : "Kansas",
"zipCode" : "56191",
"geo" : {
"lat" : -0.8177,
"lng" : -154.6886
}
},
"phone" : "(973) 473-1826 x2746",
"website" : "virgie.net",
"company" : {
"name" : "Quigley, Mitchell and McGlynn",
"catchPhrase" : "Multi-layered holistic moratorium",
"bs" : "enable front-end markets"
},
"salary" : 3028,
"monthlyExpenses" : 3080,
"favoriteSports" : [
"Cycling",
"MMA",
"Boxing",
"Handball",
"Snooker",
"American Football",
"Volleyball",
"Water Polo",
"Beach Volleyball",
"Ice Hockey",
"Motor Sport",
"Tennis",
"Formula 1"
],
"profession" : "Pilot" // matched here
}
public class User
{
[BsonId]
[BsonIgnoreIfDefault] // required for replace documents
public ObjectId Id { get; set; }
public Gender Gender { get; set; }
public string FirstName {get; set; }
public string LastName {get; set; }
public string UserName {get; set; }
public string Avatar {get; set; }
public string Email {get; set; }
public DateTime DateOfBirth {get; set; }
public AddressCard Address {get; set; }
public string Phone {get; set; }
[BsonIgnoreIfDefault]
public string Website {get; set; }
public CompanyCard Company {get; set; }
public decimal Salary { get; set; }
public int MonthlyExpenses { get; set; }
public List<string> FavoriteSports { get; set; }
public string Profession { get; set; }
}
When you want to match all document that certain field(s) values are not equal to a specific value then you use the Not Equal
operator.
Builders<T>.Filter.Ne(doc => doc.<field>, <value>)
The sample uses a not equal operator to match all documents that their profession field is other than "Doctor".
var collection = database
.GetCollection<User>(Constants.UsersCollection);
var notEqualDoctorsFilter = Builders<User>.Filter
.Ne(u => u.Profession, "Doctor");
var notDoctors = await collection
.Find(notEqualDoctorsFilter).ToListAsync();
var bsonCollection = database
.GetCollection<BsonDocument>(Constants.UsersCollection);
var bsonNotEqualDoctorsFilter = Builders<BsonDocument>.Filter
.Ne("profession", "Doctor");
var bsonNotDoctors = await bsonCollection
.Find(bsonNotEqualDoctorsFilter).ToListAsync();
db.users.find({profession: { $ne: "Doctor"}})
--------------------
// sample matched document
{
"_id" : ObjectId("5e91e3ba3c1ba62570a67b95"),
"gender" : 0,
"firstName" : "Ronnie",
"lastName" : "Weissnat",
"userName" : "Ronnie.Weissnat",
"avatar" : "https://s3.amazonaws.com/uifaces/faces/twitter/dactrtr/128.jpg",
"email" : "Ronnie.Weissnat61@gmail.com",
"dateOfBirth" : ISODate("1981-03-23T18:39:37.569+02:00"),
"address" : {
"street" : "48091 Hazle Trafficway",
"suite" : "Suite 355",
"city" : "South Fayeburgh",
"state" : "New Hampshire",
"zipCode" : "77588",
"geo" : {
"lat" : 19.3213,
"lng" : 19.1313
}
},
"phone" : "719-576-6815 x76397",
"website" : "grayce.org",
"company" : {
"name" : "Davis Inc",
"catchPhrase" : "Mandatory maximized attitude",
"bs" : "grow clicks-and-mortar eyeballs"
},
"salary" : 2578,
"monthlyExpenses" : 2986,
"favoriteSports" : [
"Darts",
"Tennis",
"MMA",
"Snooker",
"Handball",
"Ice Hockey",
"Boxing",
"American Football",
"Beach Volleyball",
"Volleyball",
"Cycling",
"Baseball"
],
"profession" : "Lawyer" // matched here
}
public class User
{
[BsonId]
[BsonIgnoreIfDefault] // required for replace documents
public ObjectId Id { get; set; }
public Gender Gender { get; set; }
public string FirstName {get; set; }
public string LastName {get; set; }
public string UserName {get; set; }
public string Avatar {get; set; }
public string Email {get; set; }
public DateTime DateOfBirth {get; set; }
public AddressCard Address {get; set; }
public string Phone {get; set; }
[BsonIgnoreIfDefault]
public string Website {get; set; }
public CompanyCard Company {get; set; }
public decimal Salary { get; set; }
public int MonthlyExpenses { get; set; }
public List<string> FavoriteSports { get; set; }
public string Profession { get; set; }
}
The greater than operator is used to find all documents that the field value is greater than a specific value.
Builders<T>.Filter.Gt(doc => doc.<field>, <value>)
The sample finds all user documents having their salary field greater than 3500.
var collection = database.GetCollection<User>(Constants.UsersCollection);
var filterGreaterThan = Builders<User>.Filter
.Gt(u => u.Salary, 3500);
var greaterThan3500 = await collection
.Find(filterGreaterThan).ToListAsync();
var bsonCollection = database
.GetCollection<BsonDocument>(Constants.UsersCollection);
var bsonFilterGreaterThan = Builders<BsonDocument>
.Filter.Gt("salary", 3500);
var bsonGreaterThan3500 = await bsonCollection
.Find(bsonFilterGreaterThan).ToListAsync();
db.users.find({salary: { $gt: 3500}})
-------------------
// sample matched document
{
"_id" : ObjectId("5e91e3ba3c1ba62570a67b98"),
"gender" : 0,
"firstName" : "Spencer",
"lastName" : "Swift",
"userName" : "Spencer.Swift",
"avatar" : "https://s3.amazonaws.com/uifaces/faces/twitter/daykiine/128.jpg",
"email" : "Spencer61@hotmail.com",
"dateOfBirth" : ISODate("1955-01-08T04:19:35.543+02:00"),
"address" : {
"street" : "9336 Walker Crest",
"suite" : "Suite 144",
"city" : "South Tate",
"state" : "South Carolina",
"zipCode" : "46603",
"geo" : {
"lat" : 39.9979,
"lng" : -117.9544
}
},
"phone" : "1-377-659-2465 x7862",
"website" : "barbara.com",
"company" : {
"name" : "Wisoky, Lynch and Torphy",
"catchPhrase" : "Diverse dedicated customer loyalty",
"bs" : "scale sexy technologies"
},
"salary" : 4325, // matched here
"monthlyExpenses" : 3023,
"favoriteSports" : [
"Basketball",
"Table Tennis",
"Moto GP",
"Tennis",
"Boxing",
"Motor Sport",
"Ice Hockey",
"Baseball",
"American Football",
"Water Polo",
"Volleyball",
"Golf",
"Cycling",
"Formula 1",
"Cricket"
],
"profession" : "Pilot"
}
public class User
{
[BsonId]
[BsonIgnoreIfDefault] // required for replace documents
public ObjectId Id { get; set; }
public Gender Gender { get; set; }
public string FirstName {get; set; }
public string LastName {get; set; }
public string UserName {get; set; }
public string Avatar {get; set; }
public string Email {get; set; }
public DateTime DateOfBirth {get; set; }
public AddressCard Address {get; set; }
public string Phone {get; set; }
[BsonIgnoreIfDefault]
public string Website {get; set; }
public CompanyCard Company {get; set; }
public decimal Salary { get; set; }
public int MonthlyExpenses { get; set; }
public List<string> FavoriteSports { get; set; }
public string Profession { get; set; }
}
The greater than or equal operator is used to find all documents that the field value is greater than or equal a specific value.
Builders<T>.Filter.Gte(doc => doc.<field>, <value>)
The sample finds all user documents having their salary field greater than or equal to 4500.
var collection = database
.GetCollection<User>(Constants.UsersCollection);
// create a greater than or equal filter on salary
var filterGreaterOrEqualThan = Builders<User>.Filter
.Gte(u => u.Salary, 4500);
var greaterOrEqualThan4500 = await collection
.Find(filterGreaterOrEqualThan).ToListAsync();
var bsonCollection = database
.GetCollection<BsonDocument>(Constants.UsersCollection);
var bsonFilterGreaterOrEqualThan = Builders<BsonDocument>
.Filter.Gte("salary", 4500);
var bsonGreaterOrEqualThan4500 = await bsonCollection
.Find(bsonFilterGreaterOrEqualThan).ToListAsync();
db.users.find({salary: { $gte: 4500}})
--------------------------
// sample matched document
{
"_id" : ObjectId("5e91e3ba3c1ba62570a67b9d"),
"gender" : 0,
"firstName" : "Phil",
"lastName" : "Dooley",
"userName" : "Phil_Dooley78",
"avatar" : "https://s3.amazonaws.com/uifaces/faces/twitter/silvanmuhlemann/128.jpg",
"email" : "Phil.Dooley@yahoo.com",
"dateOfBirth" : ISODate("1973-06-12T09:59:35.607+02:00"),
"address" : {
"street" : "659 Amely Overpass",
"suite" : "Apt. 122",
"city" : "South Leopoldmouth",
"state" : "California",
"zipCode" : "26394-8391",
"geo" : {
"lat" : 36.5683,
"lng" : -94.7289
}
},
"phone" : "(683) 482-7837 x693",
"website" : "saul.info",
"company" : {
"name" : "O'Conner LLC",
"catchPhrase" : "Integrated bottom-line internet solution",
"bs" : "deploy compelling supply-chains"
},
"salary" : 4500, // matched document
"monthlyExpenses" : 6018,
"favoriteSports" : [
"Cycling",
"Baseball",
"Darts",
"Volleyball",
"Table Tennis",
"Boxing",
"Cricket",
"Moto GP",
"Ice Hockey",
"Soccer",
"Snooker",
"American Football",
"Basketball",
"Golf",
"Tennis",
"Handball"
],
"profession" : "Marketing Manager"
}
public class User
{
[BsonId]
[BsonIgnoreIfDefault] // required for replace documents
public ObjectId Id { get; set; }
public Gender Gender { get; set; }
public string FirstName {get; set; }
public string LastName {get; set; }
public string UserName {get; set; }
public string Avatar {get; set; }
public string Email {get; set; }
public DateTime DateOfBirth {get; set; }
public AddressCard Address {get; set; }
public string Phone {get; set; }
[BsonIgnoreIfDefault]
public string Website {get; set; }
public CompanyCard Company {get; set; }
public decimal Salary { get; set; }
public int MonthlyExpenses { get; set; }
public List<string> FavoriteSports { get; set; }
public string Profession { get; set; }
}
The less than operator is used to find all documents that the field value is less than a specific value.
Builders<T>.Filter.Lt(doc => doc.<field>, <value>)
The sample finds all user documents having their salary field less than 2500.
var collection = database
.GetCollection<User>(Constants.UsersCollection);
var filterLessThan = Builders<User>.Filter
.Lt(u => u.Salary, 2500);
var lessThan2500 = await collection
.Find(filterLessThan).ToListAsync();
var bsonCollection = database
.GetCollection<BsonDocument>(Constants.UsersCollection);
var bsonFilterLessThan = Builders<BsonDocument>.Filter
.Lt("salary", 2500);
var bsonLessThan2500 = await bsonCollection
.Find(bsonFilterLessThan).ToListAsync();
db.users.find({salary: { $lt: 2500}})
--------------------------
// sample matched document
{
"_id" : ObjectId("5e91e3ba3c1ba62570a67b96"),
"gender" : 1,
"firstName" : "Eloise",
"lastName" : "Hermann",
"userName" : "Eloise_Hermann59",
"avatar" : "https://s3.amazonaws.com/uifaces/faces/twitter/jarsen/128.jpg",
"email" : "Eloise85@yahoo.com",
"dateOfBirth" : ISODate("1958-10-18T03:06:14.611+02:00"),
"address" : {
"street" : "721 Kadin Branch",
"suite" : "Apt. 587",
"city" : "Lake Koby",
"state" : "West Virginia",
"zipCode" : "61497-7657",
"geo" : {
"lat" : 86.285,
"lng" : 146.4607
}
},
"phone" : "720.248.1068 x9001",
"website" : "lou.com",
"company" : {
"name" : "Schowalter - Kulas",
"catchPhrase" : "User-friendly holistic open architecture",
"bs" : "mesh impactful supply-chains"
},
"salary" : 1997, // matched here
"monthlyExpenses" : 5139,
"favoriteSports" : [
"Soccer",
"Cycling",
"Ice Hockey",
"Tennis",
"Moto GP",
"Motor Sport"
],
"profession" : "Physical Therapist"
}
public class User
{
[BsonId]
[BsonIgnoreIfDefault] // required for replace documents
public ObjectId Id { get; set; }
public Gender Gender { get; set; }
public string FirstName {get; set; }
public string LastName {get; set; }
public string UserName {get; set; }
public string Avatar {get; set; }
public string Email {get; set; }
public DateTime DateOfBirth {get; set; }
public AddressCard Address {get; set; }
public string Phone {get; set; }
[BsonIgnoreIfDefault]
public string Website {get; set; }
public CompanyCard Company {get; set; }
public decimal Salary { get; set; }
public int MonthlyExpenses { get; set; }
public List<string> FavoriteSports { get; set; }
public string Profession { get; set; }
}
The less than or equal operator is used to find all documents that the field value is less than or equal to a specific value.
Builders<T>.Filter.Lte(doc => doc.<field>, <value>)
The sample finds all user documents having their salary field less than or equal to 1500.
var collection = database
.GetCollection<User>(Constants.UsersCollection);
// create a less than or equal filter on salary
var filterLessOrEqualThan = Builders<User>.Filter
.Lte(u => u.Salary, 1500);
var lessThanOrEqual1500 = await collection
.Find(filterLessOrEqualThan).ToListAsync();
var bsonCollection = database
.GetCollection<BsonDocument>(Constants.UsersCollection);
var bsonFilterLessOrEqualThan = Builders<BsonDocument>.Filter
.Lte("salary", 1500);
var bsonLessThanOrEqual1500 = await bsonCollection
.Find(bsonFilterLessOrEqualThan).ToListAsync();
db.users.find({salary: { $lte: 1500}})
---------------------------
// sample matched document
{
"_id" : ObjectId("5e91e3ba3c1ba62570a67ba6"),
"gender" : 1,
"firstName" : "Katherine",
"lastName" : "Koch",
"userName" : "Katherine_Koch",
"avatar" : "https://s3.amazonaws.com/uifaces/faces/twitter/aaronkwhite/128.jpg",
"email" : "Katherine.Koch2@gmail.com",
"dateOfBirth" : ISODate("1987-08-28T21:46:31.628+03:00"),
"address" : {
"street" : "9963 Clovis Loaf",
"suite" : "Apt. 981",
"city" : "East Maynard",
"state" : "Iowa",
"zipCode" : "50598-1128",
"geo" : {
"lat" : 37.3124,
"lng" : -112.172
}
},
"phone" : "1-914-575-5000 x961",
"website" : "dannie.net",
"company" : {
"name" : "Fritsch, Heller and Hansen",
"catchPhrase" : "Balanced zero defect knowledge user",
"bs" : "cultivate proactive markets"
},
"salary" : 1500, // matched here <= 1500
"monthlyExpenses" : 4140,
"favoriteSports" : [
"Basketball",
"Ice Hockey",
"Water Polo",
"Snooker",
"Cycling",
"MMA",
"Boxing",
"Motor Sport",
"Tennis",
"Darts"
],
"profession" : "Model"
}
public class User
{
[BsonId]
[BsonIgnoreIfDefault] // required for replace documents
public ObjectId Id { get; set; }
public Gender Gender { get; set; }
public string FirstName {get; set; }
public string LastName {get; set; }
public string UserName {get; set; }
public string Avatar {get; set; }
public string Email {get; set; }
public DateTime DateOfBirth {get; set; }
public AddressCard Address {get; set; }
public string Phone {get; set; }
[BsonIgnoreIfDefault]
public string Website {get; set; }
public CompanyCard Company {get; set; }
public decimal Salary { get; set; }
public int MonthlyExpenses { get; set; }
public List<string> FavoriteSports { get; set; }
public string Profession { get; set; }
}
The In operator finds documents having a field value contained in a specified array of values.
Builders<T>.Filter.In(doc => doc.<field>,
[<value1>,<value2>,..<valueN>])
var collection = database
.GetCollection<User>(Constants.UsersCollection);
// create an In operator filter on profession
var medicalProfessionsFilter = Builders<User>.Filter
.In(u => u.Profession, new[] { "Dentist", "Pharmacist", "Nurse" });
var medicalUsers = await collection
.Find(medicalProfessionsFilter).ToListAsync();
var bsonCollection = database
.GetCollection<BsonDocument>(Constants.UsersCollection);
var bsonMedicalProfessionsFilter = Builders<BsonDocument>.Filter
.In("profession", new[] { "Dentist", "Pharmacist", "Nurse" });
var bsonMedicalUsers = await bsonCollection
.Find(bsonMedicalProfessionsFilter).ToListAsync();
db.users.find({profession: { $in: ["Dentist", "Pharmacist", "Nurse"]}})
---------------------------
// sample matched document
{
"_id" : ObjectId("5e91e3ba3c1ba62570a67b9b"),
"gender" : 0,
"firstName" : "Lloyd",
"lastName" : "Grant",
"userName" : "Lloyd32",
"avatar" : "https://s3.amazonaws.com/uifaces/faces/twitter/begreative/128.jpg",
"email" : "Lloyd32@gmail.com",
"dateOfBirth" : ISODate("1954-11-29T01:29:24.386+02:00"),
"address" : {
"street" : "03072 Olin Valleys",
"suite" : "Apt. 164",
"city" : "East Dell",
"state" : "Illinois",
"zipCode" : "19276-8871",
"geo" : {
"lat" : -81.0643,
"lng" : 71.2636
}
},
"phone" : "353-472-3461",
"website" : "emanuel.name",
"company" : {
"name" : "Boyer Group",
"catchPhrase" : "Upgradable radical archive",
"bs" : "cultivate viral technologies"
},
"salary" : 2829,
"monthlyExpenses" : 4349,
"favoriteSports" : [
"Formula 1",
"Volleyball",
"Ice Hockey",
"Baseball"
],
"profession" : "Pharmacist" // matched here
}
public class User
{
[BsonId]
[BsonIgnoreIfDefault] // required for replace documents
public ObjectId Id { get; set; }
public Gender Gender { get; set; }
public string FirstName {get; set; }
public string LastName {get; set; }
public string UserName {get; set; }
public string Avatar {get; set; }
public string Email {get; set; }
public DateTime DateOfBirth {get; set; }
public AddressCard Address {get; set; }
public string Phone {get; set; }
[BsonIgnoreIfDefault]
public string Website {get; set; }
public CompanyCard Company {get; set; }
public decimal Salary { get; set; }
public int MonthlyExpenses { get; set; }
public List<string> FavoriteSports { get; set; }
public string Profession { get; set; }
}
The Not In operator finds documents having a field value not contained in a specified array of values.
Builders<T>.Filter.Nin(doc => doc.<field>,
[<value1>,<value2>,..<valueN>])
var collection = database.GetCollection<User>(Constants.UsersCollection);
// create an Not In operator filter on profession
var nonMedicalProfessionsFilter = Builders<User>.Filter
.Nin(u => u.Profession, new[] { "Dentist", "Pharmacist", "Nurse" });
var nonMedicalUsers = await collection
.Find(nonMedicalProfessionsFilter).ToListAsync();
var bsonCollection = database
.GetCollection<BsonDocument>(Constants.UsersCollection);
var bsonNotMedicalProfessionsFilter = Builders<BsonDocument>.Filter
.Nin("profession", new[] { "Dentist", "Pharmacist", "Nurse" });
var bsonNotMedicalUsers = await bsonCollection
.Find(bsonNotMedicalProfessionsFilter).ToListAsync();
db.users.find({
profession: {
$nin: ["Dentist", "Pharmacist", "Nurse"]
}})
--------------------------
// sample matched document
{
"_id" : ObjectId("5e91e3ba3c1ba62570a67b95"),
"gender" : 0,
"firstName" : "Ronnie",
"lastName" : "Weissnat",
"userName" : "Ronnie.Weissnat",
"avatar" : "https://s3.amazonaws.com/uifaces/faces/twitter/dactrtr/128.jpg",
"email" : "Ronnie.Weissnat61@gmail.com",
"dateOfBirth" : ISODate("1981-03-23T18:39:37.569+02:00"),
"address" : {
"street" : "48091 Hazle Trafficway",
"suite" : "Suite 355",
"city" : "South Fayeburgh",
"state" : "New Hampshire",
"zipCode" : "77588",
"geo" : {
"lat" : 19.3213,
"lng" : 19.1313
}
},
"phone" : "719-576-6815 x76397",
"website" : "grayce.org",
"company" : {
"name" : "Davis Inc",
"catchPhrase" : "Mandatory maximized attitude",
"bs" : "grow clicks-and-mortar eyeballs"
},
"salary" : 2578,
"monthlyExpenses" : 2986,
"favoriteSports" : [
"Darts",
"Tennis",
"MMA",
"Snooker",
"Handball",
"Ice Hockey",
"Boxing",
"American Football",
"Beach Volleyball",
"Volleyball",
"Cycling",
"Baseball"
],
"profession" : "Lawyer" // matched here
}
public class User
{
[BsonId]
[BsonIgnoreIfDefault] // required for replace documents
public ObjectId Id { get; set; }
public Gender Gender { get; set; }
public string FirstName {get; set; }
public string LastName {get; set; }
public string UserName {get; set; }
public string Avatar {get; set; }
public string Email {get; set; }
public DateTime DateOfBirth {get; set; }
public AddressCard Address {get; set; }
public string Phone {get; set; }
[BsonIgnoreIfDefault]
public string Website {get; set; }
public CompanyCard Company {get; set; }
public decimal Salary { get; set; }
public int MonthlyExpenses { get; set; }
public List<string> FavoriteSports { get; set; }
public string Profession { get; set; }
}
The sample finds all user documents where their profession field value is either "Dentist", "Pharmacist", or "Nurse" . This means that all documents matched will have profession value one of the above.
The sample finds all user documents where their profession field value is different than "Dentist", "Pharmacist", or "Nurse" . This means that all documents matched will not have profession value one of the above.