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

1问题简介

由于在组里项目开发时使用了Mongo数据库和MySQL数据库存储业务数据，基于Spring框架进行开发，因此使用Java代码存取Mongo中的数据是非常常见的编码实现。在使用Mongodb进行编码过程中，需要进行统计某个字段的所有列表，同时按照年月排序，并过滤不需要的字段，却爆出了标题的异常

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

该问题如何解决呢？

2代码演示

@RequestMapping(value = "/partydues/getPartyDuesProcessing", method = RequestMethod.POST)
    @ResponseBody
    public String getPartyDuesProcessing(@RequestBody JSONObject form) {

        JSONObject ret = new JSONObject();
        if (!form.containsKey("page") || !form.containsKey("pageSize")) {
            ret.element("result", FAILURE).element("msg", "请指定分页参数page和pageSize");
            return ret.toString();
        }
        int page = form.getInt("page");
        int pageSize = form.getInt("pageSize");
        if(page<1) {page = 1;}
        if(pageSize<10) {pageSize=10;}
		//核心代码, 构件queryObject
        QueryBuilder queryBuilder = new QueryBuilder();
        queryBuilder.and("map.dataType").is("processInstance");
        //构件字段过滤对象
        BasicDBObject fieldsObject = new BasicDBObject();
        fieldsObject.put("_class", 0);
        fieldsObject.put("name", 0);
        fieldsObject.put("cname", 0);
	    //**fieldsObject.put("map.userId", 1);**
        fieldsObject.put("map.bid", 0);
        fieldsObject.put("map.pid", 0);
        //构造排序对象
        BasicDBObject sortObject = new BasicDBObject();
        sortObject.put("map.year", -1);
        sortObject.put("map.month", -1);
       //构造Query对象
        BasicQuery query1 = new BasicQuery(queryBuilder.get(), fieldsObject);
        query1.setSortObject(sortObject);
//        Query query = new Query();
//        query.addCriteria(new Criteria("map.dataType").is("processInstance"));
         //也可以顺序执行
//        query1.with(new Sort(Sort.Direction.DESC, "map.year").and(new Sort(Sort.Direction.DESC, "map.month")));

        List<FreeModel> freeModels = null;
        freeModels = freeDao.find(query1, PARTYDUES_TABLE);
        if (freeModels.size() == 0) {
            logger.info("Program cannot find any partydue processes");
            return ret.element("result", FAILURE).element("msg", "无法找到相关的党费收缴进程历史信息").toString();
        }
        List<PageData> pageDatas = new ArrayList<>();
        for (int i=0; i<freeModels.size(); i++) {
            pageDatas.add(freeModels.get(i).getMap());
        }
        long countRecords = pageDatas.size();
        long totalPages = countRecords % pageSize == 0 ? countRecords / pageSize : (countRecords / pageSize + 1);

        List<PageData> rows = partyDuesBusiness.getListByPage(pageDatas, page, pageSize);
        JSONObject data = new JSONObject();
        data.put("totalPage", totalPages);
        data.put("currentPage", page);
        data.put("pageSize", pageSize);
        data.put("totalCount", countRecords);
        data.put("rows", rows);
        ret.element("result", SUCCESS).element("msg", "查询党费收缴历史信息成功");
        ret.element("data", data);
        return ret.toString();
    }

上述代码导致了BadValue Projection异常。
是以为其中出现了

fieldsObject.put("map.userId", 1);

在fieldsObject中不能同时出现为1的值和为0的值。

这种用法是因为有时候不需要将文档中所有键值对都返回。遇到这种情况，可以通过find或者findOne的第二个参数来指定想要的键。这样做即会节省传输的数据量，又能节省客户端解码文档的时间和内存消耗。

A projection cannot contain both include and exclude specifications, except for the exclusion of the _id field. In projections that explicitly include fields, the _id field is the only field that you can explicitly exclude.

Definition

db.collection.find(query, projection)

    Selects documents in a collection or view and returns a cursor to the selected documents.
    
    Parameter 	Type 	Description
    query 	document 	Optional. Specifies selection filter using query operators. To return all documents in a collection, omit this parameter or pass an empty document ({}).
    projection 	document 	Optional. Specifies the fields to return in the documents that match the query filter. To return all fields in the matching documents, omit this parameter. For details, see Projection.
    Returns:	A cursor to the documents that match the query criteria. When the find() method “returns documents,” the method is actually returning a cursor to the documents

.
投影里除了_id以外，要么全是1，要么全是0，否则就报错

> db.user.find({x:{$lt: 3}}, {x:0, y:1})
Error: error: {
        "$err" : "Can't canonicalize query: BadValue Projection cannot have a mix of inclusion and exclusion.",
        "code" : 17287
} 

因此将上述的代码

fieldsObject.put("map.userId", 1)；

注释掉即可正常执行。
指定排序字段时可以使用如下两种方式

BasicDBObject sortObject = new BasicDBObject();
            sortObject.put("map.year", -1);
            sortObject.put("map.month", -1);
    
            BasicQuery query1 = new BasicQuery(queryBuilder.get(), fieldsObject);
            query1.setSortObject(sortObject);

也可以通过Query类的With函数进行

query1.with(new Sort(Sort.Direction.DESC, "map.year").and(new Sort(Sort.Direction.DESC, "map.month")));

达到相同的目的

3 问题总结

总之，MongoDB的根本仍然是shell命令，熟练掌握MongoDB的增删改查，聚合框架等仍是最基础的东西。深刻理解Query对象，BasicQuery对象的继承关系，也是写出最符合自己预期的代码的关键所在。

4参考

http://blog.itpub.net/26239116/viewspace-1485417/