问题描述
实现两个集合的关联并分页查询。
假设:
collection1中有deviceId等字段,collection2 中有deviceId、unitName等字段, 关联这两个colltion,并分页查询
代码实现
public ResponseEntity<String> getPageList(@RequestBody DevQO qo) {
Aggregation aggregation;
int pageNo = qo.getPageNo(), pageSize = 2;
List<AggregationOperation> operations = new ArrayList<>();
if (qo.getDeviceId() != null) {
operations.add(Aggregation.match(Criteria.where("deviceId").is(qo.getDeviceId())));
}
operations.add(Aggregation.lookup("collection2", "deviceId", "deviceId", "collection2"));
if (qo.getUnitName() != null) {
operations.add(Aggregation.match(Criteria.where("collection2.unitName").is(qo.getUnitName())));
}
operations.add(Aggregation.sort(Sort.Direction.DESC, "startTime"));
operations.add(Aggregation.skip((long) (pageNo - 1) * pageSize));// 跳过之前页面的文档
operations.add(Aggregation.limit(pageSize));// 跳过之前页面的文档
aggregation = Aggregation.newAggregation(operations);
AggregationResults<DeviceStatusInfo> pageData =
mongoTemplate.aggregate(aggregation, "alarm_data", DeviceStatusInfo.class);
List<DeviceInfo> list = pageData.getMappedResults();
JSONObject res = new JSONObject();
resJson.put("list", list);
return new ResponseEntity<>(JSON.toJSONString(res, SerializerFeature.WriteDateUseDateFormat), HttpStatus.OK);
}
返回json
{
"list": [
{
"deviceId": "deviceId",
"startTime": 1713408948096,
"colletion2": {
"unitName": "华润北京unitName公司",
"unitCode": "unitCode",
},
}
]
}
进一步优化代码
在聚合查询结果中将collection2中的unitName,移动到与“startTime”同级的位置,并删除“collection2”字段
public ResponseEntity<String> getPageList(@RequestBody DevQO qo) {
Aggregation aggregation;
int pageNo = qo.getPageNo(), pageSize = 2;
List<AggregationOperation> operations = new ArrayList<>();
if (qo.getDeviceId() != null) {
operations.add(Aggregation.match(Criteria.where("deviceId").is(qo.getDeviceId())));
}
operations.add(Aggregation.lookup("collection2", "deviceId", "deviceId", "collection2"));
if (qo.getUnitName() != null) {
operations.add(Aggregation.match(Criteria.where("collection2.unitName").is(qo.getUnitName())));
}
operations.add(Aggregation.project()
.andInclude("deviceId", "startTime")
.and("collection2.unitName").as("unitName"));
operations.add(Aggregation.sort(Sort.Direction.DESC, "startTime"));
operations.add(Aggregation.skip((long) (pageNo - 1) * pageSize));// 跳过之前页面的文档
operations.add(Aggregation.limit(pageSize));// 跳过之前页面的文档
aggregation = Aggregation.newAggregation(operations);
AggregationResults<DeviceStatusInfo> pageData =
mongoTemplate.aggregate(aggregation, "alarm_data", DeviceStatusInfo.class);
List<DeviceInfo> list = pageData.getMappedResults();
JSONObject res = new JSONObject();
resJson.put("list", list);
return new ResponseEntity<>(JSON.toJSONString(res, SerializerFeature.WriteDateUseDateFormat), HttpStatus.OK);
}
返回json
{
"list": [
{
"deviceId": "deviceId",
"startTime": 1713408948096,
"unitName": "华润北京unitName公司"
}
]
}