ElasticSearch中使用“group by“ 统计 （ElasticsearchTemplate进行聚合查询）

我用的es是 6.8.1

如果我们想在es实现sql 中的group by的查询统计效果

SELECT COUNT(id) FROM patientstudy GROUP BY hospitalId;

在kibana中执行命令

POST /patientstudy/_search?size=0
{
    "aggs" : {
        "count" : { "terms" : { "field" : "hospitalId" } }
    }
}

看结果

用java怎么实现呢

 public Result<List> studyCountList() {
        List list=new ArrayList();
        QueryBuilder builder = null;
        builder=QueryBuilders.boolQuery().must(QueryBuilders.termsQuery("hospitalId", "2265"));//这里是测试查询机构id为2265的
        //hospitalId 是分组字段名，hospital是查询结果的别名
        TermsAggregationBuilder  tb1 = AggregationBuilders.terms("hospital").field("hospitalId").
             //分组后以Count聚合函数名字排序   false 倒叙  true 正序
                order(BucketOrder.aggregation("count_id", false));
        ValueCountAggregationBuilder sb = AggregationBuilders.count("count_id").field("hospitalId"); //对hospitalId 进行count
        tb1.subAggregation(sb);

        SearchQuery searchQuery = new NativeSearchQueryBuilder().withIndices("patientstudy").withTypes("_doc").withQuery(builder).addAggregation(tb1).build();  //patientstudy 是索引名称  _doc 是索引类型 一般在es 索引实体里配置的  @Document(indexName = "patientstudy", type = "_doc")

        // 执行语句获取聚合结果
        Aggregations aggregations = elasticsearchTemplate.query(searchQuery, new ResultsExtractor<Aggregations>() {
            @Override
            public Aggregations extract(SearchResponse response) {
                return response.getAggregations();
            }
        });
        // 获取聚合结果
        Terms term = aggregations.get("hospital");// 获取结果后进行解析

        if (term.getBuckets().size() > 0) {
            for (Bucket bk : term.getBuckets()) {
                Map<String,Object> map = new HashMap<>();
                int hospitalId = bk.getKeyAsNumber().intValue();
                map.put("count",bk.getDocCount());
                map.put("hospital",bk.getKeyAsString());
                log.info("typeId:{}" + hospitalId);
                log.info("一次循环结束--------------------------");
                list.add(map);

            }
        }
        return ResultUtil.data(ResultCode.RESULT_SUCCESS.getCode(), "",list);
    }

最后看结果

注意：hospitalId必须是keyword类型