MongoDB: Can't canonicalize query: BadValue Pr

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

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();
});

Answer 3

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 4

In simple words to describe the solution, you can tell the MongoDB either what are all the fields you want or you don't want from MongoDB.

which means in the select method you have to mention only required or not required fields. like below

find({<!--query here--!>}).select({

        password: 0,
        allowed_ip: 0,
        username: 0,
        password: 0,
        __v: 0,
        _id: 0
    });

It should be either zero(exclusions/ not required) or one(inclusions/required fields.) cheers!!!