方式二，和must、must_not同级，相当于子查询：
select * from (select * from persons where sect = '明教')) a where sex = '女';
ES查询语句：
{ "query": { "bool": { "must": [ { "term": { "sect.keyword": { "value": "明教", "boost": 1.0 } } } ], "filter": [ { "term": { "sex": { "value": "女", "boost": 1.0 } } } ], "adjust_pure_negative": true, "boost": 1.0 } } }
Java：
SearchSourceBuilder searchSourceBuilder = new SearchSourceBuilder(); // 构建查询语句 searchSourceBuilder.query(QueryBuilders.boolQuery() .must(QueryBuilders.termQuery("sect.keyword", "明教")) .filter(QueryBuilders.termQuery("sex", "女")) );
方式三，将must、must_not置于filter下，这种方式是最常用的：
{ "query": { "bool": { "filter": [ { "bool": { "must": [ { "term": { "sect.keyword": { "value": "明教", "boost": 1.0 } } }, { "range": { "age": { "from": 20, "to": 35, "include_lower": true, "include_upper": true, "boost": 1.0 } } } ], "must_not": [ { "term": { "sex.keyword": { "value": "女", "boost": 1.0 } } } ], "adjust_pure_negative": true, "boost": 1.0 } } ], "adjust_pure_negative": true, "boost": 1.0 } } }
Java:
SearchSourceBuilder searchSourceBuilder = new SearchSourceBuilder(); // 构建查询语句 searchSourceBuilder.query(QueryBuilders.boolQuery() .filter(QueryBuilders.boolQuery() .must(QueryBuilders.termQuery("sect.keyword", "明教")) .must(QueryBuilders.rangeQuery("age").gte(20).lte(35)) .mustNot(QueryBuilders.termQuery("sex.keyword", "女"))) );
基于 Spring Cloud Alibaba + Gateway + Nacos + RocketMQ + Vue & Element 实现的后台管理系统 + 用户小程序，支持 RBAC 动态权限、多租户、数据权限、工作流、三方登录、支付、短信、商城等功能
3 聚合查询
接下来，我们将用一些案例演示ES聚合查询。
3.1 最值、平均值、求和
案例：查询最大年龄、最小年龄、平均年龄。
SQL：
select max(age) from persons;
ES：
GET /person/_search { "aggregations": { "max_age": { "max": { "field": "age" } } } }
Java：
@Autowired private RestHighLevelClient client; @Test public void maxQueryTest() throws IOException { // 聚合查询条件 AggregationBuilder aggBuilder = AggregationBuilders.max("max_age").field("age"); SearchRequest searchRequest = new SearchRequest("person"); SearchSourceBuilder searchSourceBuilder = new SearchSourceBuilder(); // 将聚合查询条件构建到SearchSourceBuilder中 searchSourceBuilder.aggregation(aggBuilder); System.out.println("searchSourceBuilder----->" + searchSourceBuilder); searchRequest.source(searchSourceBuilder); // 执行查询，获取SearchResponse SearchResponse response = client.search(searchRequest, RequestOptions.DEFAULT); System.out.println(JSONObject.toJSON(response)); }
使用聚合查询，结果中默认只会返回10条文档数据（当然我们关心的是聚合的结果，而非文档）。返回多少条数据可以自主控制：
GET /person/_search { "size": 20, "aggregations": { "max_age": { "max": { "field": "age" } } } }
而Java中只需增加下面一条语句即可：
searchSourceBuilder.size(20);
与max类似，其他统计查询也很简单：
AggregationBuilder minBuilder = AggregationBuilders.min("min_age").field("age"); AggregationBuilder avgBuilder = AggregationBuilders.avg("min_age").field("age"); AggregationBuilder sumBuilder = AggregationBuilders.sum("min_age").field("age"); AggregationBuilder countBuilder = AggregationBuilders.count("min_age").field("age");
3.2 去重查询
案例：查询一共有多少个门派。
SQL：
select count(distinct sect) from persons;
ES：
{ "aggregations": { "sect_count": { "cardinality": { "field": "sect.keyword" } } } }
Java：
@Test public void cardinalityQueryTest() throws IOException { // 创建某个索引的request SearchRequest searchRequest = new SearchRequest("person"); // 查询条件 SearchSourceBuilder searchSourceBuilder = new SearchSourceBuilder(); // 聚合查询 AggregationBuilder aggBuilder = AggregationBuilders.cardinality("sect_count").field("sect.keyword"); searchSourceBuilder.size(0); // 将聚合查询构建到查询条件中 searchSourceBuilder.aggregation(aggBuilder); System.out.println("searchSourceBuilder----->" + searchSourceBuilder); searchRequest.source(searchSourceBuilder); // 执行查询，获取结果 SearchResponse response = client.search(searchRequest, RequestOptions.DEFAULT); System.out.println(JSONObject.toJSON(response)); }
3.3 分组聚合
3.3.1 单条件分组
案例：查询每个门派的人数
SQL：
select sect,count(id) from mytest.persons group by sect;
ES：
{ "size": 0, "aggregations": { "sect_count": { "terms": { "field": "sect.keyword", "size": 10, "min_doc_count": 1, "shard_min_doc_count": 0, "show_term_doc_count_error": false, "order": [ { "_count": "desc" }, { "_key": "asc" } ] } } } }
Java：
SearchRequest searchRequest = new SearchRequest("person"); SearchSourceBuilder searchSourceBuilder = new SearchSourceBuilder(); searchSourceBuilder.size(0); // 按sect分组 AggregationBuilder aggBuilder = AggregationBuilders.terms("sect_count").field("sect.keyword"); searchSourceBuilder.aggregation(aggBuilder);
3.3.2 多条件分组
案例：查询每个门派各有多少个男性和女性
SQL：
select sect,sex,count(id) from mytest.persons group by sect,sex;
ES：
{ "aggregations": { "sect_count": { "terms": { "field": "sect.keyword", "size": 10 }, "aggregations": { "sex_count": { "terms": { "field": "sex.keyword", "size": 10 } } } } } }
3.4 过滤聚合
前面所有聚合的例子请求都省略了 query ，整个请求只不过是一个聚合。这意味着我们对全部数据进行了聚合，但现实应用中，我们常常对特定范围的数据进行聚合，例如下例。
案例：查询明教中的最大年龄。这涉及到聚合与条件查询一起使用。
SQL：
select max(age) from mytest.persons where sect = '明教';
ES：
GET /person/_search { "query": { "term": { "sect.keyword": { "value": "明教", "boost": 1.0 } } }, "aggregations": { "max_age": { "max": { "field": "age" } } } }
Java：
SearchRequest searchRequest = new SearchRequest("person"); SearchSourceBuilder searchSourceBuilder = new SearchSourceBuilder(); // 聚合查询条件 AggregationBuilder maxBuilder = AggregationBuilders.max("max_age").field("age"); // 等值查询 searchSourceBuilder.query(QueryBuilders.termQuery("sect.keyword", "明教")); searchSourceBuilder.aggregation(maxBuilder);
另外还有一些更复杂的查询例子。
案例：查询0-20，21-40，41-60，61以上的各有多少人。
SQL：
select sum(case when age<=20 then 1 else 0 end) ageGroup1, sum(case when age >20 and age <=40 then 1 else 0 end) ageGroup2, sum(case when age >40 and age <=60 then 1 else 0 end) ageGroup3, sum(case when age >60 and age <=200 then 1 else 0 end) ageGroup4 from mytest.persons;
ES：
{ "size": 0, "aggregations": { "age_avg": { "range": { "field": "age", "ranges": [ { "from": 0.0, "to": 20.0 }, { "from": 21.0, "to": 40.0 }, { "from": 41.0, "to": 60.0 }, { "from": 61.0, "to": 200.0 } ], "keyed": false } } } }
查询结果：
"aggregations" : { "age_avg" : { "buckets" : [ { "key" : "0.0-20.0", "from" : 0.0, "to" : 20.0, "doc_count" : 3 }, { "key" : "21.0-40.0", "from" : 21.0, "to" : 40.0, "doc_count" : 13 }, { "key" : "41.0-60.0", "from" : 41.0, "to" : 60.0, "doc_count" : 4 }, { "key" : "61.0-200.0", "from" : 61.0, "to" : 200.0, "doc_count" : 1 } ] } }
以上是ElasticSearch查询的全部内容，丰富详实，堪比操作手册，强烈建议收藏！
