How to Query With an OR Condition in MongoDB

1. the $or Operator in MongoDB
2. Clauses and Indexes for the $or Operator
3. Single Queries and the $or Operator
4. the $or Operator VS the $in Operator
5. Error Handling With the $or Operator

This tutorial article will teach you how to use the $or operator with a MongoDB query.

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

the $or Operator in MongoDB

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

1. You can also use this operator with text queries, GeoSpatial queries, and sort operations.
2. When MongoDB evaluates the clauses in the $or expression, it performs a collection scan. If indexes support all the clauses in the $or expression, then MongoDB performs index scans.
3. You can also nest $or operation.

The $or operator chooses documents that meet at least one of the expressions by performing a logical OR operation on an array that consists of two or more expressions.

Syntax:

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

Query:

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

All documents will be returned in this query in the inventory collection with a quantity field value of less than 50 or a price field value of less than 20.

Clauses and Indexes for the $or Operator

MongoDB either does a collection scan or index scan when evaluating the clauses in the $or expression. If indexes support all of the clauses, MongoDB performs index scans.

Therefore, all clauses in the $or expression must be supported by indexes for MongoDB to use to evaluate the statement. MongoDB will execute a collection scan if this is not the case.

Each clause of a $or query can utilize its index when employing indexes with $or queries.

Query:

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

Instead of a compound index, you’d establish one index for quantity and another for the price to handle this query.

Query:

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

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

Single Queries and the $or Operator

1. The $text queries and the $or operator.

   An index must support all clauses in the $or array if $or includes a $text query. A $text query must utilize an index, and $or can only use indexes if all of its clauses are supported by the indexes.

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

2. GeoSpatial queries and the $or operator.

Except for the near clause (which includes `$nearSphere` and `$near`), `$or` allows geographic clauses. There can't be a near clause with any other clause in `$or.`

3. Sort operations and the $or operator.

   MongoDB may now use indexes that support the $or clauses when running ‘$or’ queries with a ‘sort().’ The indices were not used in previous versions of MongoDB.

the $or Operator VS the $in Operator

Instead of using the $or operator with expressions that are equality checks for the value of the same field, use the $in operator. Use the $in operator to pick all documents in the 'inventory' collection where the quantity field value is 40 or 1000.

Query:

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

Also, you can nest the $or operator.

Error Handling With the $or Operator

The $or operator handles mistakes in the following way to help the query engine to improve queries.

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

If $x is 0, for example, the following query will always return an error.

Query:

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

Suppose there is any document where $x has the value 0, which involves multiple expressions passed to $or. The following example might produce an error.

Query:

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

This MongoDB tutorial teaches you how to use the $or operator with a MongoDB query to perform logical OR operations on the array, which consists of two or more expressions, and also select or retrieve only the documents that match at least one of the expressions given in the array.