用mongo $elemMatch 筛选嵌套列表所有符合条件的对象

mongo中存的数据格式如下

// 1
{
    "_id": "1",
    "personCerts": [
        {
            "perId": NumberInt("1"),
            "qualityNum": NumberInt("3"),
            "performance": NumberInt("5"),
            "tenderNum": NumberInt("0"),
            "certifications": [
                {
                    "regType": "LB_051",
                    "regMajor": "ZY_001"
                },
                {
                    "regType": "LB_072",
                    "regMajor": "ZY_006"
                },
                {
                    "regType": "LB_072",
                    "regMajor": "ZY_002"
                }
            ]
        },
        {
            "perId": NumberInt("2"),
            "qualityNum": NumberInt("3"),
            "performance": NumberInt("0"),
            "tenderNum": NumberInt("0"),
            "certifications": [
                {
                    "regType": "LB_072",
                    "regMajor": "ZY_001"
                },
                {
                    "regType": "LB_072",
                    "regMajor": "ZY_006"
                },
                {
                    "regType": "LB_072",
                    "regMajor": "ZY_007"
                }
            ]
        }
    ]
}

当前需求是查询出该企业人员证书类型为LB_072，专业为ZY_001，类型为LB_072，专业为ZY_006的人员及其符合条件的证书。通过数据可以知道，该企业只有1人同时拥有这俩本证书，但是如果不用elemmatch，只是使用普通的match，这俩个人都可以被筛选出来，

即

db.getCollection("company_person_cert_test").aggregate([
    {
        "$match": {
            "_id": "1"
        }
    },
    {
        "$unwind": "$personCerts"
    },
    {
        //这一层是筛选符合条件的人员
        "$match": {
            "$and": [
                {
                    "personCerts.certifications.regType": "LB_072",
                    "personCerts.certifications.regMajor": "ZY_001"
                },
                {
                    "personCerts.certifications.regType": "LB_072",
                    "personCerts.certifications.regMajor": "ZY_006"
                }
            ]
        }
    },
    {
        "$unwind": "$personCerts.certifications"
    },
    {
        //这一层是筛选符合条件的证书（之所以用or，是因为上一层已经筛选出了符合的人员，
        //所以这一层只需要筛选出该人员符合条件的证书即可）
        "$match": {
            "$or": [
                {
                    "personCerts.certifications.regType": "LB_072",
                    "personCerts.certifications.regMajor": "ZY_001"
                },
                {
                    "personCerts.certifications.regType": "LB_072",
                    "personCerts.certifications.regMajor": "ZY_006"
                }
            ]
        }
    }
])

结果

// 1
{
    "_id": "1",
    "personCerts": {
        "perId": NumberInt("1"),
        "qualityNum": NumberInt("3"),
        "performance": NumberInt("5"),
        "tenderNum": NumberInt("0"),
        "certifications": {
            "regType": "LB_072",
            "regMajor": "ZY_006"
        }
    }
}

// 2
{
    "_id": "1",
    "personCerts": {
        "perId": NumberInt("2"),
        "qualityNum": NumberInt("3"),
        "performance": NumberInt("0"),
        "tenderNum": NumberInt("0"),
        "certifications": {
            "regType": "LB_072",
            "regMajor": "ZY_001"
        }
    }
}

// 3
{
    "_id": "1",
    "personCerts": {
        "perId": NumberInt("2"),
        "qualityNum": NumberInt("3"),
        "performance": NumberInt("0"),
        "tenderNum": NumberInt("0"),
        "certifications": {
            "regType": "LB_072",
            "regMajor": "ZY_006"
        }
    }
}

可以看到，结果并不准确，多了id为1的人员，这是因为，id为1的人员存在类型为LB_072和专业为ZY_001的证书（但是并没有证书同时符合这2个条件），即这种筛选并没将嵌套的list中的元素当作单独的对象，所以，我开始尝试$elemmatch,但是$elemmach一次只能带出一个符合条件的嵌套对象，百度+google后，依然是overstack给力，可以解决我的需求

https://stackoverflow.com/questions/25014699/mongodb-multiple-elemmatch

根据上述地址，我对我的查询做出调整

db.getCollection("company_person_cert_test").aggregate([
    {
        "$match": {
            "_id": "1"
        }
    },
    {
        "$unwind": "$personCerts"
    },
    {
        "$match": {
            "$and": [
		{
                    "personCerts.certifications": {
                        "$elemMatch": {
                            "regMajor": "ZY_001",
                            "regType": "LB_072"
                        }
                    }
                },
                {
                    "personCerts.certifications": {
                        "$elemMatch": {
                            "regMajor": "ZY_006",
                            "regType": "LB_072"
                        }
                    }
                }
            ]
        }
    },
    {
        "$unwind": "$personCerts.certifications"
    },
    {
        "$match": {
            "$or": [
                {
                    "personCerts.certifications.regType": "LB_072",
                    "personCerts.certifications.regMajor": "ZY_001"
                },
                {
                    "personCerts.certifications.regType": "LB_072",
                    "personCerts.certifications.regMajor": "ZY_006"
                }
            ]
        }
    }
])

结果

// 1
{
    "_id": "1",
    "personCerts": {
        "perId": NumberInt("2"),
        "qualityNum": NumberInt("3"),
        "performance": NumberInt("0"),
        "tenderNum": NumberInt("0"),
        "certifications": {
            "regType": "LB_072",
            "regMajor": "ZY_001"
        }
    }
}

// 2
{
    "_id": "1",
    "personCerts": {
        "perId": NumberInt("2"),
        "qualityNum": NumberInt("3"),
        "performance": NumberInt("0"),
        "tenderNum": NumberInt("0"),
        "certifications": {
            "regType": "LB_072",
            "regMajor": "ZY_006"
        }
    }
}

ok,正确

在java中这样写

Criteria criteria = Criteria.where("_id").is(1);

String[] typeAndMajorArray = "LB_072,ZY_001:LB_072,ZY_006".split(":") ;
Criteria[] criteriaArray = new Criteria[typeAndMajorArray.length];
Criteria[] criteriaArray1 = new Criteria[typeAndMajorArray.length];

for (int i = 0; i < typeAndMajorArray.length ; i++) {
      String regType = typeAndMajorArray[i].split(",")[0];
      String regMajor = typeAndMajorArray[i].split(",")[1];
      criteriaArray[i] = Criteria
                            .where("personCerts.certifications")
                            .elemMatch(Criteria.where("regMajor").is(regMajor).and("regType").is(regType));
      criteriaArray1[i] = Criteria
                            .where("personCerts.certifications.regType").is(regType)
                            .and("personCerts.certifications.regMajor").is(regMajor);
}                  

     Criteria criteria1 = new Criteria();
     Criteria criteria2 = new Criteria();

     criteria1.andOperator(criteriaArray);
     criteria2.orOperator(criteriaArray1);

Aggregation aggregation =
                        newAggregation(
                                match(criteria),
                                unwind("personCerts"),
                                match(criteria1),
                                limit(10),
                                unwind("personCerts.certifications"),
                                sort(new Sort(Sort.Direction.DESC,"major")),
                                match(criteria2)
                        );

return mongoTemplate.aggregate(aggregation, collectionName,JSONObject.class)