mongo关联查询实例

// 创建条件
List<DBObject> pipeline = new ArrayList<>();
String lookupStr = "{\"$lookup\" : { \"from\" : \"risk_model_record\" , \"localField\" : \"riskId\" , \"foreignField\" : \"riskId\" , \"as\" : \"records\"}}";
DBObject lookup = (DBObject) JSON.parse(lookupStr);
String sortStr = "{ \"$sort\" : { \"createTime\" : -1,\"_id\" : -1}}";
DBObject sort = (DBObject) JSON.parse(sortStr);
String unwindStr = "{\"$unwind\" : {\"path\":\"$records\",\"preserveNullAndEmptyArrays\":true}}";
DBObject unwind = (DBObject) JSON.parse(unwindStr);
StringBuffer sb = new StringBuffer("{\"$match\":{$and:[");
boolean matched = false;
if (StringUtils.isNotBlank(param.getAppkey())) {
    sb.append("{\"appkey\":{\"$eq\" :\"").append(param.getAppkey()).append("\"}},");
    matched = true;
}
if (StringUtils.isNotBlank(param.getPlatformId())) {
    sb.append("{\"platformId\":{\"$eq\" :\"").append(param.getPlatformId()).append("\"}},");
    matched = true;
}
if (StringUtils.isNotBlank(param.getModelId())) {
    sb.append("{\"modelId\":{\"$eq\" :\"").append(param.getModelId()).append("\"}},");
    matched = true;
}
if (StringUtils.isNotBlank(param.getStart())) {
    sb.append("{\"createTime\":{\"$gte\" :\"").append(param.getStart()).append("\"}},");
    matched = true;
}
if (StringUtils.isNotBlank(param.getEnd())) {
    sb.append("{\"createTime\":{\"$lte\" :\"").append(param.getEnd()).append("\"}},");
    matched = true;
}
if (matched) {
    sb.deleteCharAt(sb.length() - 1).append("]}}");
} else {
    sb.append("]}}");
    sb.replace(11, 18, "");
}
logger.info("查询条件[match]：{}", sb.toString());
DBObject match = (DBObject) JSON.parse(sb.toString());
String skipStr = "{$skip:" + (param.getCurrPage() - 1) * param.getPageSize() + "}";
DBObject skip = (DBObject) JSON.parse(skipStr);
String limitStr = "{$limit:" + param.getPageSize() + "}";
DBObject limit = (DBObject) JSON.parse(limitStr);
String projectStr = "{\"$project\" : {\"_id\":0,\"riskId\":\"$riskId\", \"appkey\" :\"$appkey\",\"appName\" :\"$appName\",\"platformId\" :\"$platformId\",\"platformName\" :\"$platformName\",\"createTime\" :\"$createTime\",\"modelId\" :\"$records.modelId\" , \"modelName\" : \"$records.modelName\" , \"modelResult\" : \"$records.modelResult\" , \"status\" : \"$records.status\", \"requestParams\" : \"$requestParams\",\"result\" : \"$result\",\"sync\" : \"$sync\", \"message\" : \"$records.message\"}}";
DBObject project = (DBObject) JSON.parse(projectStr);
String groupStr = "{$group:{_id:null,count:{$sum:1}}}";
DBObject group = (DBObject) JSON.parse(groupStr);
pipeline.add(lookup);
pipeline.add(project);
pipeline.add(match);
pipeline.add(group);
logger.info("查询条件[count]：{}", JSONObject.toJSON(pipeline));
Cursor cursor1 = mongoTemplate.getCollection("risk_model_log").aggregate(pipeline,
                                                                         AggregationOptions.builder().outputMode(AggregationOptions.OutputMode.CURSOR).build());
int total = 0;
while (cursor1.hasNext()) {
    try {
        DBObject document = cursor1.next();
        total = JSONObject.parseObject(document.toString()).getIntValue("count");
    } catch (Exception e) {
        logger.error(e.getMessage(), e);
    }
}
pipeline.remove(project);
pipeline.remove(group);
pipeline.remove(match);
pipeline.add(unwind);
pipeline.add(project);
pipeline.add(match);
pipeline.add(sort);
pipeline.add(skip);
pipeline.add(limit);
logger.info("查询条件[list]：{}", JSONObject.toJSON(pipeline));
Cursor cursor2 = mongoTemplate.getCollection("risk_model_log").aggregate(pipeline,
                                                                         AggregationOptions.builder().outputMode(AggregationOptions.OutputMode.CURSOR).build());
// 组装page
List<RiskModelLog> list = new ArrayList<>();
while (cursor2.hasNext()) {
    try {
        DBObject document = cursor2.next();
        RiskModelLog log = JSONObject.parseObject(JSONObject.toJSONString(document), RiskModelLog.class);
        if("1".equals(log.getStatus())){
            log.setStatus("通过");
        } else if ("0".equals(log.getStatus())) {
            log.setStatus("拒绝");
        } else if("-1".equals(log.getStatus())){
            log.setStatus("运行失败");
            JSONObject jsonObject=new JSONObject();
            jsonObject.put("errmsg",log.getMessage());
            log.setResult(jsonObject.toJSONString());
        }else if(log.getResult()!=null){
            JSONObject result=JSONObject.parseObject((String)log.getResult());
            if(result.getString("errcode")!=null){
                log.setStatus("运行失败");
            }else{
                log.setStatus("运行中");
            }
        }



        list.add(log);
    } catch (Exception e) {
        logger.error(e.getMessage(), e);
    }
}
Page<RiskModelLog> page = new Page<RiskModelLog>(list, total, param.getPageSize(), param.getCurrPage());
return page;