Convert SQL IN and NOT IN to MongoDB $in and $nin

IN / NOT IN to $in / $nin

SQL's IN operator checks whether a column value matches any value in a list. MongoDB provides the $in operator for the same purpose, and $nin for the negated form.

Example SQL

SELECT * FROM users
WHERE role IN ('admin', 'editor', 'moderator')

Generated MongoDB Query

db.users.find({
  role: { $in: ["admin", "editor", "moderator"] }
})

NOT IN Example

SELECT * FROM products
WHERE category NOT IN ('archived', 'draft')

db.products.find({
  category: { $nin: ["archived", "draft"] }
})

$in with Mixed Types

Unlike SQL where IN values typically share the same type, MongoDB's $in can contain mixed types:

db.items.find({ tag: { $in: ["featured", 1, true] } })

This flexibility comes from MongoDB's dynamic schema, but it is best to keep types consistent for predictable results.

$in on Array Fields

A powerful MongoDB-specific behavior is that $in works on array fields too. If a document has a field containing an array, $in matches if any element of the array is in the provided list. This has no direct SQL equivalent and is one of MongoDB's strengths for document-oriented data.

Performance

Both $in and $nin can use indexes. However, $nin typically requires a full collection scan because it must check that no value matches, making it less efficient than $in.