$and (query predicate operator)

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, if you specify conditions like { $ne: "R", $ne: "PG" }, the second value overrides the first.

To confirm multiple conditions, use an explicit AND:

db.movies.find( {
   $and: [
      { genres: { $in: [ "News", "Talk-Show" ] } },
      { genres: { $in: [ "History", "Western" ] } }
   ]},
   { title: 1, genres: 1, year: 1 }
)

The previous query explicitly checks that both conditions are satisfied: the genres array must include at least one value from each $in set.

Examples

The examples on this page use data from the sample_mflix sample dataset. For details on how to load this dataset into your self-managed MongoDB deployment, see Load the sample dataset. If you made any modifications to the sample databases, you may need to drop and recreate the databases to run the examples on this page.

$and matches multiple expressions on the same field.

Consider this query:

db.movies.find( 
   { $and: [ { year: { $gte: 1960 } }, { year: { $lte: 1970 } }, { directors: "Martin Scorsese" } ] }, 
   { title: 1, directors: 1, year: 1 })

The query selects all documents in the movies collection where:

• the year is 1960 or later and

• the year is 1970 or earlier and

• the directors include Martin Scorsese

It also projects the results to only include the title, directors, and year fields. The query returns the following documents:

[
 {
  "_id": "573a1395f29313caabce2f95",
  "title": "The Big Shave",
  "directors": [ "Martin Scorsese" ],
  "year": 1968
 },
 {
  "_id": "573a1396f29313caabce3889",
  "title": "Who's That Knocking at My Door",
  "directors": [ "Martin Scorsese" ],
  "year": 1967
 }
]

You can simplify this query by combining the operators for the year field into a single query object, and using implicit AND operators:

db.movies.find( 
   { year: { $gte:1960, $lte:1970 }, directors:'Martin Scorsese' }, 
   { title: 1, directors: 1, year: 1 })

Always review your query to confirm it matches intended behavior. Using the earlier genres example:

db.movies.find( { genres: { $in: [ "News", "Talk-Show" ], $in: [ "History", "Western" ] } } )

To find documents where the genres array contains either News or Talk-Show and either History or Western, use $in:

db.movies.find( {
   $and: [
      { genres: { $in: [ "News", "Talk-Show" ] } },
      { genres: { $in: [ "History", "Western" ] } }
   ]},
   { title: 1, genres: 1, year: 1 }
)

If a field is an array, such as directors: [ "Jack Conway", "Howard Hawks", "William A. Wellman" ], and you want to check if the document contains multiple values instead of one, use $all:

db.movies.find( { directors: { $all: ['John Murray Anderson','Pèl Fejès'] } }, { title: 1, directors: 1, year: 1 })

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