What are the Logical Operators in a Query?

Logical Operators in a Query

More intricate conditional searches are made possible by the use of logical operators to combine several query expressions.

Let’s use a collection called movies with the following sample data:

db.movies.insertMany([
    { "_id": 1, "title": "Inception", "genre": "Sci-Fi", "year": 2010, "rating": 8.8, "director": "Christopher Nolan", "oscarsWon": 4 },
    { "_id": 2, "title": "The Dark Knight", "genre": "Action", "year": 2008, "rating": 9.0, "director": "Christopher Nolan", "oscarsWon": 2 },
    { "_id": 3, "title": "Pulp Fiction", "genre": "Crime", "year": 1994, "rating": 8.9, "director": "Quentin Tarantino", "oscarsWon": 1 },
    { "_id": 4, "title": "Interstellar", "genre": "Sci-Fi", "year": 2014, "rating": 8.6, "director": "Christopher Nolan", "oscarsWon": 1 },
    { "_id": 5, "title": "Forrest Gump", "genre": "Drama", "year": 1994, "rating": 8.8, "director": "Robert Zemeckis", "oscarsWon": 6 },
    { "_id": 6, "title": "Matrix", "genre": "Sci-Fi", "year": 1999, "rating": 8.7, "director": "The Wachowskis", "oscarsWon": 4 },
    { "_id": 7, "title": "Spirited Away", "genre": "Animation", "year": 2001, "rating": 8.6, "director": "Hayao Miyazaki", "oscarsWon": 1 },
    { "_id": 8, "title": "Parasite", "genre": "Thriller", "year": 2019, "rating": 8.5, "director": "Bong Joon-ho", "oscarsWon": 4 }
]);

MongoDB AND operator ( $and )

An AND condition is implicitly created in MongoDB when multiple key-value pairs are specified in the find() method and separated by commas. A document cannot be returned until all of the requirements are met. For instance, in order to locate tutorials titled ‘MongoDB Overview’ and written by ‘tutorials point’:

WHERE by=’tutorials point’ AND title = ‘MongoDB Overview’ is the same as this. Any number of these key-value pairs can be included. The $and operator can be used directly with an array of query selectors, however implicit AND is more popular. This is particularly helpful when combining sophisticated layered logic or conditions that may use the same field more than once. For instance, locating goods that are labelled with “gift” or “holiday” AND “gardening” or “landscaping”:

Make an informed choice because the query optimiser may not optimise the explicit $and as effectively as other operators.

Syntax:

{ $and: [ { <expression1> }, { <expression2> }, ... , { <expressionN> } ] }

Code Example:

Find movies that are ‘Sci-Fi’ AND released after the year 2010:

db.movies.find({
    $and: [
        { "genre": "Sci-Fi" },
        { "year": { $gt: 2010 } }
    ]
}).pretty();

Find movies directed by ‘Christopher Nolan’ AND have a rating greater than or equal to 8.8:

db.movies.find({
    $and: [
        { "director": "Christopher Nolan" },
        { "rating": { $gte: 8.8 } }
    ]
}).pretty();

MongoDB OR operator ( $or )

You must use the $or operator with an array whose elements are all condition documents if you want to query documents based on an OR condition. The document appears in the results if any of the array’s conditions are satisfied. For instance, to locate every lesson titled “MongoDB Overview” OR created by “tutorials point”:

SELECT * FROM students WHERE firstName = “Prosen” OR age >= 23 in SQL is similar to this. You can define alternative keys and values for each condition because to $or’s versatility. For more specific results, combine it with other query parameters. The query optimiser handles $in better than $or for querying many values on a single key.

Syntax:

{ $or: [ { <expression1> }, { <expression2> }, ... , { <expressionN> } ] }

Code Example:

Find movies that are either ‘Crime’ OR have received 6 oscarsWon:

db.movies.find({
    $or: [
        { "genre": "Crime" },
        { "oscarsWon": 6 }
    ]
}).pretty();

Find movies released in 1994 OR directed by ‘Hayao Miyazaki’:

db.movies.find({
    $or: [
        { "year": 1994 },
        { "director": "Hayao Miyazaki" }
    ]
}).pretty();

MongoDB NOT operator ( $not )

As a meta-conditional, the $not operator counteracts the impact of a regular expression query or another MongoDB operator. It returns documents that don’t match an expression, including missing fields. To find papers with an age greater than 30 or no age field:

It can find non-pattern-matching texts with regular expressions.

Syntax:

{ field: { $not: { <operator-expression> } } }

Code Example:

Find movies that are NOT ‘Sci-Fi’ genre:

db.movies.find({
    "genre": { $not: { $eq: "Sci-Fi" } }
}).pretty();

Find movies that do NOT have a rating greater than 8.9:

db.movies.find({
    "rating": { $not: { $gt: 8.9 } }
}).pretty();

MongoDB NOR operator ( $nor )

The $nor operator performs a logical NOR operation on an array of two or more <expression>s and selects the documents that fail to satisfy all of the expressions in the array. This includes documents that do not contain the field(s) in the expressions.

Syntax:

{ $nor: [ { <expression1> }, { <expression2> }, ... , { <expressionN> } ] }

Code Examples:

Find movies that are NEITHER ‘Action’ genre NOR released in 1994:

db.movies.find({
    $nor: [
        { "genre": "Action" },
        { "year": 1994 }
    ]
}).pretty();

Find movies that are NEITHER directed by ‘Christopher Nolan’ NOR have a rating greater than 8.7:

db.movies.find({
    $nor: [
        { "director": "Christopher Nolan" },
        { "rating": { $gt: 8.7 } }
    ]
}).pretty();

Combining AND and OR

Implicit AND and explicit $or criteria can be combined in a single query for more complex and accurate filtering. For example, to locate pages with more than 100 likes AND (title is ‘MongoDB Overview’ OR by is ‘tutorials point’…):

db.mycol.find({
    likes: {$gt: 100},
    $or:[
        {"by":"tutorials point"},
        {"title": "MongoDB Overview"}
    ]
})

This is a more complex query equivalent to WHERE likes > 10 AND (by = ‘tutorials point’ OR title = ‘MongoDB Overview’).

Query Operators in MongoDB

A powerful JSON-based query language lets you obtain and alter data in MongoDB. To specify conditions, you utilise query documents with different operators instead of the conventional SQL WHERE clauses. These operators improve your comprehension of how MongoDB works with your data and are essential for accurate data filtering and retrieval.

Comparison Query Operators

By comparing the value of a field with a predetermined value, comparison operators allow you to query documents. In relational database management systems (RDBMS), they are the functional counterparts of common comparison procedures.

• Equality ({<key>:<value>}) You can directly define the key-value pair within your query document to locate documents where a field precisely matches a certain value. An example would be to use the by field ‘tutorials point’ to find every document in a collection called mycol:
• In a SQL environment, this operation is similar to WHERE by = ‘tutorials point’.
• Less Than ($lt) Documents where a field’s value is strictly less than the designated value are selected using this operator. For example, to locate documents with fewer than fifty likes:
• This would be WHERE likes < 50 on an RDBMS.
• Less Than or Equal ($lte) The $lte operator selects documents with field values below or equal to the provided value. Example searching for likes under 50:
• WHERE likes <= 50 is the same as this.
• Greater Than ($gt) Documents where a field’s value is strictly greater than the designated value are identified by this operator. To locate documents with more than fifty likes:
• WHERE likes > 50 is the equivalent of this.
• Greater Than or Equal ($gte) The $gte operation picks documents with increased or equal field values. An example of 50+ likes:
• WHERE likes >= 50 is the same as this.
• Not Equal ($ne) This operator fetches documents with non-specified field values. To locate documents without 50 likes:
• This is comparable to WHERE likes != 50. It’s crucial to note that $ne can be less efficient as it typically needs scanning a big section of the index or collection, performing comparable to a complete collection scan even when an index is available. Combining it with additional selected criteria is generally recommended.
• Combining Range Operators Multiple comparison operators on the same field define a range. To search papers released between 1990 and 2010:
• Finding users aged 18–30 is another example:
• Between the range conditions, an AND condition is used implicitly.

Key Performance Considerations

• $where Clause: For complex queries, you can embed any JavaScript using this operator. Nevertheless, compared to native MongoDB operators, $where queries are substantially slower. This is because, crucially, they are unable to use indexes and each document needs to be transformed into a JavaScript object and processed by a single-threaded JavaScript interpreter. $where should only be used when there is no other way to define a query. If you must use it, filter down the result set before JavaScript evaluation by combining it with other indexable query operators. The $expr operator was added to MongoDB 3.6 as a quicker substitute for aggregating expressions that accomplish comparable conditional reasoning without the need for JavaScript.
• Query Optimisation Tools: Tools such as explain() are essential for comprehending and enhancing query performance. explain() gives information about the query execution strategy, including performance metrics and index utilisation. Hint() can also be used to have MongoDB use a certain index, which is useful for query optimisation and testing various index techniques. Since indexes greatly speed up selective searching and sorting on huge collections, it is essential to understand how to use them.