es进阶语法查询

在看这篇前，先看下es基础语法，比较容易上手。

针对于最近做的车机平台系统一些常用的写法,做了一些记录。

数据格式如下：

{
    "took": 16,
    "timed_out": false,
    "_shards": {
        "total": 1,
        "successful": 1,
        "skipped": 0,
        "failed": 0
    },
    "hits": {
        "total": {
            "value": 2,
            "relation": "eq"
        },
        "max_score": 1.0,
        "hits": [{
            "_index": "vehicle_vip_data_202209",
            "_type": "_doc",
            "_id": "UZo9eIMBUZW80fpzWLp9",
            "_score": 1.0,
            "_source": {
                "user_id": "1",
                "vehicle_id": "2c",
                "app_id": "12",
                "os_ver": "ES6_kylo_BL1.0.1_101.6_20220720",
                "tracking_type": "perf_vip_stat",
                "timestamp": 1663149893,
                "datetime": "2022091418",
                "day": "20220914",
                "track_data": [{
                    "key": "com.nio.carsystemui",
                    "cpu": 0.21635656,
                    "mem": 202447.0
                }, {
                    "key": "com.android.car",
                    "cpu": 0.2805656,
                    "mem": 25879.0
                }]
            }
        }]
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42

1.查询某些字段列表下拉框

group_ver是自定义的名称，os_ver就是数据中的字段，根据这个字段分组得到你想要去重（分组）的list

@Override
public SearchRequest buildRequest() {
    SearchSourceBuilder searchSourceBuilder = new SearchSourceBuilder();
    SearchRequest searchRequest = new SearchRequest();
    searchSourceBuilder.size(0);
    TermsAggregationBuilder verTerms = AggregationBuilders.terms("group_ver")
    .field("os_ver")
    .size(10000)
    .order(BucketOrder.key(true));
    searchSourceBuilder.aggregation(verTerms);
    // 查询的索引
    //目前索引写死，后期与当前时间相关动态调整
    searchRequest.indices("vehicle_system_data_202209");
    searchRequest.source(searchSourceBuilder);
    return searchRequest;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

2.精确条件查询

查询day的时间范围

精确查询vehicle_id和os_ver

@Override
public SearchRequest buildRequest(AppMemDataQueryDTO appMemDataQueryDTO) {
    SearchSourceBuilder searchSourceBuilder = new SearchSourceBuilder();
    SearchRequest searchRequest = new SearchRequest();
 
    QueryBuilder daytime = QueryBuilders.rangeQuery("day")
    .gte(appMemDataQueryDTO.getStartDay())
    .lte(appMemDataQueryDTO.getEndDay());
    BoolQueryBuilder boolQueryBuilder = QueryBuilders.boolQuery().filter(daytime);
    if(StringUtils.isNotBlank(appMemDataQueryDTO.getVehicleId())) {
        boolQueryBuilder.must(QueryBuilders.termQuery("vehicle_id",
        appMemDataQueryDTO.getVehicleId()));
    }
    if(StringUtils.isNotBlank(appMemDataQueryDTO.getOsVer())) {
        boolQueryBuilder.must(QueryBuilders.termQuery("os_ver", appMemDataQueryDTO.getOsVer()));
    }
    //最多展示10条
    searchSourceBuilder.size(10);
    searchSourceBuilder.query(boolQueryBuilder);
 
    // 查询的索引
    //目前索引写死，后期与当前时间相关动态调整
    searchRequest.indices("vehicle_vip_data_202209");
 
    searchRequest.source(searchSourceBuilder);
    return searchRequest;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

3.聚合查询

时间范围

os_ver分组

求count，求平均数，求中位数

track_data.mem是一条数据中的子对象的属性

@Override
public SearchRequest buildMemRequest(MemDataQueryDTO memDataQueryDTO){
    SearchSourceBuilder searchSourceBuilder = new SearchSourceBuilder();
    SearchRequest searchRequest = new SearchRequest();
 
    BoolQueryBuilder boolQueryBuilder = QueryBuilders.boolQuery().filter(
    QueryBuilders.rangeQuery("day")
    .from(memDataQueryDTO.getStartDay())
    .to(memDataQueryDTO.getEndDay())
    .format("yyMMdd"));
 
    searchSourceBuilder.size(0);
    searchSourceBuilder.query(boolQueryBuilder);
 
    // 查询的索引
    //目前索引写死，后期与当前时间相关动态调整
    searchRequest.indices("vehicle_system_data_202209");
    //分组
    TermsAggregationBuilder verTerms = 
    AggregationBuilders.terms("ver_terms").field("os_ver").size(10000);
    //求count
    ValueCountAggregationBuilder memCount = 
    AggregationBuilders.count("mem_count").field("track_data.mem");
    //平均数
    AggregationBuilder memAvg = 
    AggregationBuilders.avg("avg_cpu").field("track_data.mem");
    //中位数
    AggregationBuilder pointRanks = AggregationBuilders.percentiles("ranks")
    .field("track.perf_system_mem")
    .percentiles(25,50,75,90);
     
    searchSourceBuilder.aggregation(
    verTerms.subAggregation(pointRanks)
    .subAggregation(memCount)
    .subAggregation(memAvg));
    searchRequest.source(searchSourceBuilder);
    return searchRequest;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

4.聚合查询（nested嵌套分组）

和第3条很像，具体参考nested和object区别

区别就是用nested就会把子对象的属性都会join关联上。默认子对象是不会关联，但是效率就会更差点

当你需要对一条数据的子对象分组并做聚合参数时，需要使用到es的nested特性

@Override
public SearchRequest buildCpuRequest(AppCpuDataQueryDTO query) {
    SearchSourceBuilder searchSourceBuilder = new SearchSourceBuilder();
    SearchRequest searchRequest = new SearchRequest();
 
    QueryBuilder daytime = QueryBuilders.rangeQuery("day")
    .gte(query.getStartDay())
    .lte(query.getEndDay());
    BoolQueryBuilder boolQueryBuilder = QueryBuilders.boolQuery().filter(daytime);
    
    searchSourceBuilder.size(0);
    searchSourceBuilder.query(boolQueryBuilder);
 
    // 查询的索引
    //目前索引写死，后期与当前时间相关动态调整
    searchRequest.indices("vehicle_vip_data_202209");
    //分组
    TermsAggregationBuilder verTerms = 
    AggregationBuilders.terms("ver_terms").field("os_ver").size(10000);
    //申明一个nested对象，名字自定义，属性是子对象名称track_data
    NestedAggregationBuilder trackNested = 
    AggregationBuilders.nested("track_data_nested", "track_data");
    TermsAggregationBuilder trackKey = 
    AggregationBuilders.terms("data_key").field("track_data.key").size(10000);
    //根据子对象的filter条件精确查询（条件优化）
    FilterAggregationBuilder filterKey = 
    AggregationBuilders.filter("nested_key",
    QueryBuilders.termQuery("track_data.key",query.getAppName()));
    //平均数
    AggregationBuilder cpuAvg = 
    AggregationBuilders.avg("avg_cpu").field("track_data.cpu");
    //count
    ValueCountAggregationBuilder memCount = 
    AggregationBuilders.count("count").field("track_data.cpu");
 
    AggregationBuilder pointRanks = AggregationBuilders
    .percentiles("ranks")
    .field("track_data.cpu")
    .percentiles(25,50,75,90);
    searchSourceBuilder.aggregation(
            verTerms.subAggregation(
             trackNested.subAggregation(
              filterKey.subAggregation(
               trackKey.subAggregation(memCount)
               .subAggregation(pointRanks
               ).subAggregation(cpuAvg)))));
 
    searchRequest.source(searchSourceBuilder);
    return searchRequest;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50

4.聚合查询（nested嵌套分组）

和第3条很像，具体参考nested和object区别

区别就是用nested就会把子对象的属性都会join关联上。默认子对象是不会关联，但是效率就会更差点

当你需要对一条数据的子对象分组并做聚合参数时，需要使用到es的nested特性

@Override
public SearchRequest buildCpuRequest(AppCpuDataQueryDTO query) {
    SearchSourceBuilder searchSourceBuilder = new SearchSourceBuilder();
    SearchRequest searchRequest = new SearchRequest();
 
    QueryBuilder daytime = QueryBuilders.rangeQuery("day")
    .gte(query.getStartDay())
    .lte(query.getEndDay());
    BoolQueryBuilder boolQueryBuilder = QueryBuilders.boolQuery().filter(daytime);
    
    searchSourceBuilder.size(0);
    searchSourceBuilder.query(boolQueryBuilder);
 
    // 查询的索引
    //目前索引写死，后期与当前时间相关动态调整
    searchRequest.indices("vehicle_vip_data_202209");
    //分组
    TermsAggregationBuilder verTerms = 
    AggregationBuilders.terms("ver_terms").field("os_ver").size(10000);
    //申明一个nested对象，名字自定义，属性是子对象名称track_data
    NestedAggregationBuilder trackNested = 
    AggregationBuilders.nested("track_data_nested", "track_data");
    TermsAggregationBuilder trackKey = 
    AggregationBuilders.terms("data_key").field("track_data.key").size(10000);
    //根据子对象的filter条件精确查询（条件优化）
    FilterAggregationBuilder filterKey = 
    AggregationBuilders.filter("nested_key",
    QueryBuilders.termQuery("track_data.key",query.getAppName()));
    //平均数
    AggregationBuilder cpuAvg = 
    AggregationBuilders.avg("avg_cpu").field("track_data.cpu");
    //count
    ValueCountAggregationBuilder memCount = 
    AggregationBuilders.count("count").field("track_data.cpu");
 
    AggregationBuilder pointRanks = AggregationBuilders
    .percentiles("ranks")
    .field("track_data.cpu")
    .percentiles(25,50,75,90);
    searchSourceBuilder.aggregation(
            verTerms.subAggregation(
             trackNested.subAggregation(
              filterKey.subAggregation(
               trackKey.subAggregation(memCount)
               .subAggregation(pointRanks
               ).subAggregation(cpuAvg)))));
 
    searchRequest.source(searchSourceBuilder);
    return searchRequest;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50