MongoDB: Cannot do exclusion in inclusion projection

What it means

A projection says which fields a query returns. It works in one of two modes:

Inclusion: { name: 1, email: 1 } returns only those fields (and _id).
Exclusion: { password: 0 } returns everything except those fields.

MongoDB can’t do both at once, because it wouldn’t know what to do with the fields you didn’t mention. So a projection with 1 for one field and 0 for another is refused. Which message you get depends on which kind came first:

Cannot do exclusion on field password in inclusion projection (code 31254): an inclusion projection with a 0 in it.
Cannot do inclusion on field name in exclusion projection (code 31253): the reverse.

The one exception is _id: { name: 1, _id: 0 } is allowed.

Common causes

Hiding a secret and picking fields at once: { name: 1, email: 1, password: 0 }. In inclusion mode password is already left out.
A Mongoose select() that mixes signs: select("name -password") or select({ name: 1, password: 0 }).
A projection built from pieces, where one part of the code adds inclusions and another adds exclusions.
$project in a pipeline with both, which fails the same way after Invalid $project :: caused by ::.

How to fix it

Pick one mode

To return a few fields, list only those:

db.users.find({}, { name: 1, email: 1 })

To return everything but a few, list only those:

db.users.find({}, { password: 0 })

Add _id: 0 to either if you don’t want _id.

In Mongoose

Use only plain names or only - names in a select() string: select("name email") or select("-password"). -_id can go with either. To keep a field out of every query by default, give it select: false in the schema instead of excluding it each time.

In pipelines, use $unset to drop fields

$unset removes fields and can sit before or after a $project that includes fields:

db.users.aggregate([
  { $unset: "password" },
  { $project: { name: 1, email: 1 } }
])

Computed fields count as inclusions

{ name: 1, email: { $toUpper: "$email" } } works in an inclusion projection. In an exclusion projection, only $meta is allowed, so { password: 0, email: { $toUpper: "$email" } } fails with Cannot use expression other than $meta in exclusion projection.

Reproduce it

MongoDB 8.0.32, mongosh 2.12.0, a user with name, email, password and address:

db.users.find({}, { name: 1, password: 0 })

MongoServerError[Location31254]: Cannot do exclusion on field password in inclusion projection

With the order reversed, { password: 0, name: 1 }:

MongoServerError[Location31253]: Cannot do inclusion on field name in exclusion projection

{ name: 1, _id: 0 } returned { name: 'Ada' }, and { password: 0, _id: 0 } everything except those two. { name: true, password: false } failed like the first. In a pipeline:

MongoServerError[Location31254]: Invalid $project :: caused by :: Cannot do exclusion on field password in inclusion projection

$unset before $project returned { _id: 1, name: 'Ada' }. Excluding address and including address.city gave Path collision at address.city remaining portion city (code 31249).

With Mongoose 8.23.0, find().select("email -plan") and select({ email: 1, plan: 0 }) both sent { email: 1, plan: 0 } and got the 31254 error from the server; select("email -_id") worked.

In Inlet

Queries use mongosh syntax, so db.users.find({}, { name: 1, email: 1 }) runs as it does in mongosh. Inlet shows the collection as a table with the fields the query returns, and each whole document in the inspector.

Cannot do exclusion on field in inclusion projection