mongodb 实现两个集合的关联并分页查询

问题描述

实现两个集合的关联并分页查询。
假设：
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公司"
		}
	]
}