$and

Compatibility

You can use $and for deployments hosted in the following environments:

• MongoDB Atlas: The fully managed service for MongoDB deployments in the cloud

• MongoDB Enterprise: The subscription-based, self-managed version of MongoDB

• MongoDB Community: The source-available, free-to-use, and self-managed version of MongoDB

Syntax

The $and has the following syntax:

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

Behavior

When evaluating the clauses in the $and expression, MongoDB's query optimizer considers which indexes are available that could help satisfy clauses of the $and expression when selecting the best plan to execute.

To allow the query engine to optimize queries, $and handles errors as follows:

• If any expression supplied to $and would cause an error when evaluated alone, the $and containing the expression may cause an error but an error is not guaranteed.

• An expression supplied after the first expression supplied to $and may cause an error even if the first expression evaluates to false.

For example, the following query always produces an error if $x is 0:

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

The following query, which contains multiple expressions supplied to $and, may produce an error if there is any document where $x is 0:

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

Most programming languages and drivers, including the MongoDB Shell (mongosh), do not allow the construction of objects with duplicate keys at the same object level. For example:

db.inventory.find( { price: { $in: [ 7.99, 3.99 ], $in: [ 4.99, 1.99 ] } } )

The previous query is invalid because the field name price has duplicate operators at the same object level. The query sent to the server differs from the intent. To make the query work, use an explicit AND:

db.inventory.find( {
   $and: [
      { price: { $in: [ 7.99, 3.99 ] } },
      { price: { $in: [ 4.99, 1.99 ] } }
   ]
} )

The previous query explicitly checks that both conditions are satisfied: the price array must include at least one value from each $in set. For more information, see Examples.

Examples

The examples match multiple expressions on the same field.

Consider this query:

db.inventory.find( { $and: [ { price: { $ne: 1.99 } }, { price: { $exists: true } } ] } )

The query selects all documents in the inventory collection where:

• the price field value is not equal to 1.99 and

• the price field exists.

You can simplify this query by combining the operator expressions for the price field into a single query object with a nested implicit AND:

db.inventory.find( { price: { $ne: 1.99, $exists: true } } )

Rewrites are not always possible, particularly when duplicate conditions exist on the same field. For example:

db.inventory.find( { status: { $ne: "closed", $ne: "archived" } } )

The previous query is invalid because it uses $ne more than once on the status field at the same object level. Use $nin instead:

db.inventory.find( { status: { $nin: [ "closed", "archived" ] } } )

Rewrite the query based on your intent. Consider this query:

db.inventory.find( {
   $and: [
      { status: "new" },
      { status: "processing" }
   ]
} )

To find documents where status is either new or processing, use $in:

db.inventory.find( { status: { $in: [ "new", "processing" ] } } )

If your status field is an array [ "new", "processing" ] and you want to check if the document contains both new and processing, use $all:

db.inventory.find( { status: { $all: [ "new", "processing" ] } } )

The previous query is semantically equivalent to AND, but $all is clearer when querying array fields.

Similar to duplicate field names, the same considerations apply for duplicate operators used in the query.

Learn More

• find()

• $ne

• $exists

• $in

• $all