JIYIK CN >

This article will explain how to use the $or operator in MongoDB queries.

MongoDB provides users with different types of logical query operators, and the $or operator is one of them. This operator performs a logical OR operation on an array of two or more expressions and selects or retrieves only the documents that match at least one of the given expressions in the array.

$or operator in MongoDB

We can use the $or operator in methods like find(), or.update()

1. You can also use this operator for text queries, geospatial queries, and sort operations.
2. When MongoDB evaluates the clauses in the $or expression, it performs a collection scan. If an index supports all the clauses in the $or expression, MongoDB performs an index scan.
3. It is also possible to nest $or operations.

$orThe operator selects documents that satisfy at least one expression by performing a logical OR operation on an array of two or more expressions.

grammar:

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

Query:

db.inventory.find( { $or: [ { quantity: { $lt: 50 } }, { price: 20 } ] } )

This query will return all documents in the inventory collection where the quantity field value is less than 50 or the price field value is less than 20.

Clauses and Indexes for the $or Operator

When evaluating the clauses in an $or expression, MongoDB performs either a collection scan or an index scan. If the index supports all clauses, MongoDB performs an index scan.

Therefore, all clauses in an $or expression must be backed by an index for MongoDB to use to evaluate the statement. If this is not the case, MongoDB will perform a collection scan.

When you use an index in an $or query, each clause of the $or query can use its index.

Query:

db.inventory.find( { $or: [ { quantity: { $lt: 50 } }, { price: 20 } ] } )

Instead of a compound index, you could have one index for quantity and another for price to handle this query.

Query:

db.inventory.createIndex( { quantity: 1 } )
db.inventory.createIndex( { price: 1 } )

To enable the $or clause, MongoDB can use any index except the geoHaystack index.

Single query and the $or operator

1. $text query and $or operator.

   If $or contains a $text query, the index must support all clauses in the $or array. $text queries must use an index, and $or can only use an index if the index supports all of its clauses.

   If a $text query cannot use an index, the query will generate an error.

2. GeoSpatial queries and the $or operator.

   In addition to the near clause (including $nearSphere and $near), $or allows geographic clauses. $or cannot have a near clause with any other clause.

3. Sorting operations and the $or operator.

   MongoDB now uses indexes that support $or clauses when running "$or" queries with "sort()". These indexes were not used in previous versions of MongoDB.

$or Operator vs. $in Operator

Instead of using the $or operator with expressions that check for equality on values ​​of the same field, use the $in operator. Use the $in operator to select all documents in the 'inventory' collection that have a quantity field value of either 40 or 1000.

Query:

db.inventory.find ( { quantity: { $in: [40, 1000] } } )

Additionally, you can nest $or operators.

Using the $or operator to handle errors

The $or operator handles errors in the following ways to help the query engine improve the query.

1. If any expression is supplied to the $or operator, it will cause an error when evaluated alone. The $or operator containing an expression may cause an error, but it is not guaranteed.
2. Even if the first expression supplied to $or evaluates to true, expressions submitted after it may trigger an error.

For example, if $x is 0, the following query will always return an error.

Query:

db.example.find( {
   $expr: { $eq: [ { $divide: [ 1, "$x" ] }, 3 ] }
} )

Assume there is any document where $x evaluates to 0, which involves multiple expressions passed to $or. The following example may produce an error.

Query:

db.example.find( {
   $or: [
      { x: { $eq: 0 } },
      { $expr: { $eq: [ { $divide: [ 1, "$x" ] }, 3 ] } }
   ]
} )

This MongoDB article explains how to use the $or operator in MongoDB queries to perform a logical OR operation on an array of two or more expressions and select or retrieve only the documents in the array that match at least one of the given expressions.