IN AND OR Operator in MongoDB

Specify Conditions Using Query Operators

Use query operators in a query filter document

 to perform more complex comparisons and evaluations. 

To return all movies from the sample_mflix.movies collection which are either rated PG or PG-13:

use sample_mflix
db.movies.find( { rated: { $in: [ "PG", "PG-13" ] } } )

This operation corresponds to the following SQL statement:

SELECT * FROM movies WHERE rated in ("PG", "PG-13")

 

Specify Logical Operators (AND / OR)

A compound query can specify conditions for more than one field in the collection's documents. Implicitly, a logical AND conjunction connects the clauses of a compound query so that the query selects the documents in the collection that match all the conditions.

To return movies which were released in Mexico and have an IMDB rating of at least 7:

use sample_mflix

db.movies.find( { countries: "Mexico", "imdb.rating": { $gte: 7 } } )

Use the $or operator to specify a compound query that joins each clause with a logical OR conjunction so that the query selects the documents in the collection that match at least one condition.

To return movies from the sample_mflix.movies collection which were released in 2010 and either won at least 5 awards or have a genre of Drama:

use sample_mflix

db.movies.find( {

year: 2010,

$or: [ { "awards.wins": { $gte: 5 } }, { genres: "Drama" } ]

} )

Comparison Operators

MongoDB comparison operators can be used to compare values in a document. The following table contains the common comparison operators.

Operator	Description
$eq	Matches values that are equal to the given value.
$gt	Matches if values are greater than the given value.
$lt	Matches if values are less than the given value.
$gte	Matches if values are greater or equal to the given value.
$lte	Matches if values are less or equal to the given value.
$in	Matches any of the values in an array.
$ne	Matches values that are not equal to the given value.
$nin	Matches none of the values specified in an array.

$eq Operator

In this example, we retrieve the document with the exact _id value “LS0009100”.

Copy

db.inventory.find({"_id": { $eq: "LS0009100"}}).pretty()

Result:

$gt and $lt Operators

In this example, we retrieve the documents where the `quantity` is greater than 5000.

Copy

db.inventory.find({"quantity": { $gt: 5000}}).pretty()

Result:

Let’s find the documents with the ‘quantity’ less than 5000.

Copy

db.inventory.find({"quantity": { $lt: 5000}}).pretty()

Result:

$gte and $lte Operators

Find documents with ‘quantity’ greater than or equal to 5000.

Copy

db.inventory.find({"quantity": { $gte: 12000}}).pretty()

Result:

The following query returns documents where the quantity is less than or equal to 1000.

Copy

db.inventory.find({"quantity": { $lte: 1000}}).pretty()

Result:

$in and $nin Operators

The following query returns documents where the price field contains the given values.

Copy

db.inventory.find({"price": { $in: [3, 6]}}).pretty()

Result:

If you want to find documents where the price fields do not contain the given values, use the following query.

Copy

db.inventory.find({"price": { $nin: [5.23, 3, 6, 3.59, 4.95]}}).pretty()

Result:

$ne Operator

Find documents where the value of the price field is not equal to 5.23 in the inventory collection.

Copy

db.inventory.find({"price": { $ne: 5.23}})

Result:

Logical Operators

MongoDB logical operators can be used to filter data based on given conditions. These operators provide a way to combine multiple conditions. Each operator equates the given condition to a true or false value.

Here are the MongoDB logical operators:

Operator	Description
$and	Joins two or more queries with a logical AND and returns the documents that match all the conditions.
$or	Join two or more queries with a logical OR and return the documents that match either query.
$nor	The opposite of the OR operator. The logical NOR operator will join two or more queries and return documents that do not match the given query conditions.
$not	Returns the documents that do not match the given query expression.

$and Operator

Find documents that match both the following conditions

• job_role is equal to “Store Associate”
• emp_age is between 20 and 30

Copy

db.employees.find({ $and: [{"job_role": "Store Associate"}, {"emp_age": {$gte: 20, $lte: 30}}]}).pretty()

Result:

$or and $nor Operators

Find documents that match either of the following conditions.

• job_role is equal to “Senior Cashier” or “Store Manager”

Copy

db.employees.find({ $or: [{"job_role": "Senior Cashier"}, {"job_role": "Store Manager"}]}).pretty()

Result:

Find documents that do not match either of the following conditions.

• job_role is equal to “Senior Cashier” or “Store Manager”

Copy

db.employees.find({ $nor: [{"job_role": "Senior Cashier"}, {"job_role": "Store Manager"}]}).pretty()

Result:

$not Operator

Find documents where they do not match the given condition.

• emp_age is not greater than or equal to 40

Copy

db.employees.find({ "emp_age": { $not: { $gte: 40}}})

Result:

Element Operators

The element query operators are used to identify documents using the fields of the document. The table given below lists the current element operators.

Operator	Description
$exists	Matches documents that have the specified field.
$type	Matches documents according to the specified field type. These field types are specified BSON types and can be defined either by type number or alias.

$exists Operator

Find documents where the job_role field exists and equal to “Cashier”.

Copy

db.employees.find({ "emp_age": { $exists: true, $gte: 30}}).pretty()

Result:

Find documents with an address field. (As the current dataset does not contain an address field, the output will be null.)

Copy

db.employees.find({ "address": { $exists: true}}).pretty()

Result:

$type Operator

The following query returns documents if the emp_age field is a double type. If we specify a different data type, no documents will be returned even though the field exists as it does not correspond to the correct field type.

Copy

db.employees.find({ "emp_age": { $type: "double"}})

Result:

Copy

db.employees.find({ "emp_age": { $type: "bool"}})

Result:

Evaluation Operators

The MongoDB evaluation operators can evaluate the overall data structure or individual field in a document. We are only looking at the basic functionality of these operators as each of these operators can be considered an advanced MongoDB functionality. Here is a list of common evaluation operators in MongoDB.

Operator	Description
$jsonSchema	Validate the document according to the given JSON schema.
$mod	Matches documents where a given field’s value is equal to the remainder after being divided by a specified value.
$regex	Select documents that match the given regular expression.
$text	Perform a text search on the indicated field. The search can only be performed if the field is indexed with a text index.
$where	Matches documents that satisfy a JavaScript expression.

$jsonSchema Operator

Find documents that match the following JSON schema in the promo collection.

The $let aggregation is used to bind the variables to a results object for simpler output. In the JSON schema, we have specified the minimum value for the “period” field as 7, which will filter out any document with a lesser value.

Copy

let promoschema = {
bsonType: "object",
required: [ "name", "period", "daily_sales" ],
properties: {
"name": {
bsonType: "string",
description: "promotion name"
},
"period": {
bsonType: "double",
description: "promotion period",
minimum: 7,
maximum: 30
},
"daily_sales": {
bsonType: "array"
}
}
}

Copy

db.promo.find({ $jsonSchema: promoschema }).pretty()

Result:

$mod Operator

Find documents where the remainder is 1000 when divided by 3000 in the inventory collection.

Note that the document “Milk Non-Fat – 1lt” is included in the output because the quantity is 1000, which cannot be divided by 3000, and the remainder is 1000.

Copy

db.inventory.find({"quantity": {$mod: [3000, 1000]}}).pretty()

Result:

$regex Operator

Find documents that contain the word “Packed” in the name field in the inventory collection.

Copy

db.inventory.find({"name": {$regex: '.Packed.'}}).pretty()

Result:

$text Operator

Find documents by using a text searching for “Non-Fat” in the name field. If the field is not indexed, you must create a text index before searching.

Copy

db.inventory.createIndex({ "name": "text"})

Result:

Copy

db.inventory.find({ $text: { $search: "Non-Fat"}}).pretty()

Result:

$where Operator

Find documents from the “payments” collection where the _id field is a string type and equals the given md5 hash defined as a JavaScript function.

Copy

db.payments.find({ $where: function() { var value =  isString(this._id) && hex_md5(this._id) == '57fee1331906c3a8f0fa583d37ebbea9'; return value; }}).pretty()

Result:

Array Operators

MongoDB array operators are designed to query documents with arrays. Here are the array operators provided by MongoDB.

Operator	Description
$all	Matches arrays that contain all the specified values in the query condition.
$size	Matches the documents if the array size is equal to the specified size in a query.
$elemMatch	Matches documents that match specified $elemMatch conditions within each array element.

$all Operator

Find documents where the category array field contains “healthy” and “organic” values.

Copy

db.inventory.find({ "category": { $all: ["healthy", "organic"]}}).pretty()

Result:

$size Operator

Find documents where the category array field has two elements.

Copy

db.inventory.find({ "category": { $size: 2}}).pretty()

Result:

$elemMatch Operator

Find documents where at least a single element in the “daily_sales” array is less than 200 and greater than 100.

Copy

db.promo.find({ "daily_sales": { $elemMatch: {$gt: 100, $lt: 200}}}).pretty()

Result:

Comment Operator

The MongoDB comment query operator associates a comment to any expression taking a query predicate. Adding comments to queries enables database administrators to trace and interpret MongoDB logs using the comments easily.

$comment Operator

Find documents where the period is equal to 7 in promo collection while adding a comment to the find operation.

Copy

db.promo.find({ "period": { $eq: 7}, $comment: "Find Weeklong Promos"}).pretty()

Result:

Adding comments lets users easily identify commands in MongoDB logs. The above operation will be logged as follows.

Copy

db.adminCommand( { getLog:'global'} ).log.forEach(x => {print(x)})

Result:

000000