mongo分组查询，代码和工具查询语句

左手的ㄟ右手 2023-06-15 11:26 14阅读 0赞

### **查询工具语句** ###

> db.getCollection('record').aggregate(\[  
>     \{$match:\{"uptime" :\{$gte:1573833599\}\}\},  
>     \{$group:\{\_id:"$name", count: \{$sum: 1\}\}\}  
> \])
> 
> 按名称分组，同时将每行数据的deviceId加入列表deviceId，加入时去重，如果不去重addToSet$改用$push  
> db.getCollection('record').aggregate(\[  
>     \{$match:\{"uptime" :\{$gte:1581091200,$lte: 1581177600\}\}\},  
>     \{$group:\{\_id:"$name", deviceId: \{$addToSet: '$deviceId'\}\}\},  
> \])
> 
>

$match是查询条件

$group是分组依据，其中\_id是分组显示字段，$name中的name是分组字段

### ** java 代码形式** ###

public List<返回对象> find(Long startTime, Long endTime) {
        Criteria criteria = Criteria.where("uptime").gte(startTime).lt(endTime);
        Aggregation agg = Aggregation.newAggregation(
                Aggregation.match(criteria),
                Aggregation.group("deviceId")
                        .first("deviceId").as("deviceId")
                        .count()
                        .as("count")
        );
        AggregationResults<ImsiRecord3DayStats> result = secondaryMongoDBDao.aggregate(agg, 查询对象.class, 返回对象.class);
        List<返回对象> aaa = result.getMappedResults();
        return aaa;
    }
    // 说明：
    //.group("分组字段").first("分组字段").as("查询后返回字段")
    //.count().as("查询后返回字段")

### **java 代码形式 升级版(查询内存限时，分页)** ###

Exceeded memory limit for $group, but didn't allow external sort. Pass allowDiskUse:true  
这个错误是由于Mongodb规定了aggregate管道聚合的返回数据不能超过16M，超过16M就会报异常错误。解决方法就是设置allowDiskUse:true，即允许使用磁盘缓存。

public List<City> find(Long startTime, Long endTime) {
            Criteria criteria = Criteria.where("uptime").gte(startTime()).lte(endTime()));
    
            Sort.Order orders = new Sort.Order(Sort.Direction.DESC, "sum");
            Sort sort = new Sort(orders);
    
            //允许使用磁盘缓存
            AggregationOptions.Builder builder = new AggregationOptions.Builder().allowDiskUse(true);
            AggregationOptions aggregationOptions = builder.build();
    
            TypedAggregation<City> agg = Aggregation.newAggregation(City.class,
                    Aggregation.match(criteria),
                    Aggregation.group("name")
                            .first("name").as("name")
                            .first("uptime").as("uptime")
                            .sum("sum").as("sum"),
                    Aggregation.sort(sort),
                    Aggregation.skip(bo.getPage() * bo.getSize()),
                    Aggregation.limit(bo.getSize())
    
            ).withOptions(aggregationOptions);
    
            AggregationResults<City> aggregate = mongoDBDao.aggregate(agg, City.class, City.class);
            List<City> citys = aggregate.getMappedResults();
    		
            return citys;
    }
    // 这里有个知识点：因为用 sum 来排序，所以底下一定要 返回sum。不然会报错

**新版的spring mongo 对于允许磁盘可以用**  
AggregationOptions aggregationOptions = AggregationOptions.builder().allowDiskUse(true).build();  
**但是低版本的上面会报错，可以这样用**  
AggregationOptions.Builder builder = new AggregationOptions.Builder().allowDiskUse(true);  
AggregationOptions aggregationOptions = builder.build();