MongoDB: How to Use a “NOT IN” Query


You can use the following syntax to query for all documents where the value for a particular field is not in a certain list of values:

db.collection.find({field1: {$nin: ["value1", "value2", "value3"]}}) 

This particular query finds all documents where the value in field1 is not equal to value1, value2, or value3.

The following examples show how to use this syntax in practice.

Example 1: Query for “NOT IN” with One Value

Suppose we have a collection teams with the following documents:

db.teams.insertOne({team: "Mavs", position: "Guard", points: 31})
db.teams.insertOne({team: "Spurs", position: "Guard", points: 22})
db.teams.insertOne({team: "Rockets", position: "Center", points: 19})
db.teams.insertOne({team: "Warriors", position: "Forward", points: 26})
db.teams.insertOne({team: "Cavs", position: "Guard", points: 33})

We can use the following code to find all documents where the “team” field is not equal to the value “Rockets”:

db.teams.find({team: {$nin: ["Rockets"]}}) 

This query returns the following documents:

{ _id: ObjectId("619527e467d6742f66749b72"),
  team: 'Cavs',
  position: 'Guard',
  points: 33 }

{ _id: ObjectId("619527e467d6742f66749b6e"),
  team: 'Mavs',
  position: 'Guard',
  points: 31 }

{ _id: ObjectId("619527e467d6742f66749b6f"),
  team: 'Mavs',
  position: 'Guard',
  points: 22 }

Notice that the only documents returned are the ones where the “team” field is not equal to “Rockets.”

Example 2: Query for “NOT IN” with List of Values

Suppose we have a collection teams with the following documents:

db.teams.insertOne({team: "Mavs", position: "Guard", points: 31})
db.teams.insertOne({team: "Spurs", position: "Guard", points: 22})
db.teams.insertOne({team: "Rockets", position: "Center", points: 19})
db.teams.insertOne({team: "Warriors", position: "Forward", points: 26})
db.teams.insertOne({team: "Cavs", position: "Guard", points: 33})

We can use the following code to find all documents where the “team” field is not equal to the value “Rockets” or “Cavs”:

db.teams.find({team: {$nin: ["Rockets", "Cavs"]}}) 

This query returns the following documents:

{ _id: ObjectId("619527e467d6742f66749b6e"),
  team: 'Mavs',
  position: 'Guard',
  points: 31 }

{ _id: ObjectId("619527e467d6742f66749b6f"),
  team: 'Mavs',
  position: 'Guard',
  points: 22 }

Notice that the only documents returned are the ones where the “team” field is not equal to “Rockets” or “Cavs.”

Note: You can find the complete documentation for the $nin function here.

Additional Resources

The following tutorials explain how to perform other common operations in MongoDB:

MongoDB: How to Query with “Like” Regex
MongoDB: How to Check if Field Contains a String
MongoDB: How to Add a New Field in a Collection
MongoDB: How to Remove a Field from Every Document

Leave a Reply

Your email address will not be published.