MongoDB: Can't canonicalize query: BadValue Projection cannot have a mix of inclusion and exclusion

Question

I am newer in MongoDB with CakePHP.

When I write the following query it will execute very well.

db.testData.find()
{
    "_id" : ObjectId("53d1f79db8173a625961ff3d"),
    "name" : "sadikhasan",
    "created" : ISODate("2014-07-25T06:22:21.701Z")
}

When I run the following query to get only name, it returns an error:

db.testData.find({},{name:1, created:0})
error: {
    "$err" : "Can't canonicalize query: BadValue Projection cannot 
              have a mix of inclusion and exclusion.",
    "code" : 17287
}

When I run the following query to get only name with _id:0, then it executes well:

db.testData.find({},{name:1, _id:0})
{ "name" : "sadikhasan" }

My question is, why I am getting an error when I write created:0 in the projection list. Thanks for help in advance.

Answer 1

You cannot mix inclusion and exclusion, the only exception is the _id field.

For example if you have this:

{    "_id": ObjectId("53d1fd30bdcf7d52c0d217de"),    "name": "bill",    "birthdate": ISODate("2014-07-80T00:00:00.000Z"),    "created": ISODate("2014-07-25T06:44:38.641Z") } 

If all you want is the "name" and "birthdate" you need to do this:

db.collection.find({},{ "_id": 0, "name": 1, "birthdate": 1 }) 

Or this:

db.collection.find({},{ "_id": 0, "created": 0 }) 

But it is not allowed to "mix" any other operations other than "_id"

db.collection.find({},{ "_id": 0, "name": 1, "created": 0 }) 

That would also produce an error.

This is all covered in the manual pages.

Answer 2

It is throwing error "Can't canonicalize query: BadValue Projection cannot have a mix of inclusion and exclusion." becuase you are mixing both inclusion and exlusion. 1 stands for inclusion and 0 stands for exclusion. You can use either 0 or 1 in your query. So, in case you wish to see , say, only _id and name fields, use can either use: 1) Inclusion:

              db.testdata.find({}, {_id:1,name:1}) 

Or 2) Exclusion:

              db.testdata.find({},{created:0}) 

In both the above scenarios, it will show only _id and name field.

Answer 3

I ran into the same problem. It means that you cannot tell MongoDB to select a particular field and unselect another field at the same time.

this is wrong, cannot specify like this in the select options Error: -Projection cannot have a mix of inclusion and exclusion.

reviewSchema.pre(/^find/, function (next) {
    this.populate({
        path: 'tour',
        select: 'name -__v'
    })
    next();
});

Correct format.

reviewSchema.pre(/^find/, function (next) {
    this.populate({
        path: 'tour',
        select: 'name' // Removed -__v
    })
    next();
});