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

查询工具语句

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();