0. 引言

因为es非关系型数据库的特性，我们常常需要在实际业务中实现复杂查询，从而来查询到我们想要的数据。

很多同学刚接触java client不知道如何实现各类复杂查询操作。今天我们就来讲讲一些常见的复杂查询如何实现

1. 运行环境

下文演示基于如下环境

spring-data-elasticsearch 4.2.10
elasticsearch 7.13.0
java 1.8
spring-boot 2.3.7.RELEASE

开始讲解之前，先声明我们的索引结构，方便大家后续理解我们的案例

# 订单索引，一个订单下有多个商品
PUT order_test
{
  "mappings": {
    "properties": {
      // 订单状态 0未付款 1未发货 2运输中 3待签收 4已签收 
      "status": {
        "type": "integer"
      },
      // 订单编号
      "no": {
        "type": "keyword"
      },
      // 下单时间
      "create_time": {
        "type": "date",
        "format": "yyyy-MM-dd HH:mm:ss"
      },
      // 订单金额
      "amount": {
        "type": "double"
      },
      // 创建人
      "creator":{
        "type": "keyword"
      },
      // 地址
      "address":{
        "type": "text",
        "analyzer": "ik_max_word"
      },
      // 地址坐标
      "point":{
        "type": "geo_point"
      },
      // 商品信息
      "product":{
        "type": "nested",
        "properties": {
          // 商品ID
          "id": {
            "type": "keyword"
          },
          // 商品名称
          "name":{
            "type": "keyword"
          },
          // 商品价格
          "price": {
            "type": "double"
          },
          // 商品数量
          "quantity": {
            "type": "integer"
          }
        }
      }
    }
  }
}

测试数据，供大家跟练

POST order_test/_bulk
{"index":{}}
{"status":0,"no":"DD202205280001","create_time":"2022-05-01 12:00:00","amount":100.0,"creator":"张三","address":"北京市上海路11号","point":{"lon":116.23128,"lat":40.22077},"product":[{"id":"1","name":"苹果","price":20.0,"quantity":5}]}
{"index":{}}
{"status":0,"no":"DD202205280002","create_time":"2022-05-01 12:00:00","amount":100.0,"creator":"李四","address":"北京市市源路5号","point":{"lon":116.23128,"lat":40.22077},"product":[{"id":"2","name":"香蕉","price":20.0,"quantity":5}]}
{"index":{}}
{"status":1,"no":"DD202205280003","create_time":"2022-05-02 12:00:00","amount":100.0,"creator":"张三","address":"贵阳市北京路1号","point":{"lon":106.62298,"lat":26.67865},"product":[{"id":"2","name":"香蕉","price":20.0,"quantity":5}]}
{"index":{}}
{"status":2,"no":"DD202205280004","create_time":"2022-05-01 12:00:00","amount":150.0,"creator":"王二","address":"贵阳市太平街1号","point":{"lon":106.62298,"lat":26.67865},"product":[{"id":"1","name":"苹果","price":30.0,"quantity":5}]}
{"index":{}}
{"status":2,"no":"DD202205280005","create_time":"2022-05-03 12:00:00","amount":100.0,"creator":"55555","address":"贵阳市北京路12号","point":{"lon":106.62298,"lat":26.67865},"product":[{"id":"2","name":"香蕉","price":20.0,"quantity":5}]}
{"index":{}}
{"status":3,"no":"DD202205280006","create_time":"2022-05-04 12:00:00","amount":150.0,"creator":"李四","address":"上海市宝山路1号","point":{"lon":121.48941,"lat":31.40527},"product":[{"id":"3","name":"榴莲","price":150.0,"quantity":1}]}
{"index":{}}
{"status":4,"no":"DD202205280007","create_time":"2022-05-04 12:00:00","amount":100.0,"creator":"张三","address":"贵阳市观山湖区12号","point":{"lon":106.62298,"lat":26.67865},"product":[{"id":"2","name":"香蕉","price":20.0,"quantity":5}]}
{"index":{}}
{"status":3,"no":"DD202205280008","create_time":"2022-05-01 12:00:00","amount":200.0,"creator":"王二","address":"上海市宝山路11号","point":{"lon":121.48941,"lat":31.40527},"product":[{"id":"1","name":"苹果","price":40.0,"quantity":5}]}
{"index":{}}
{"status":4,"no":"DD202205280009","create_time":"2022-05-03 12:00:00","amount":100.0,"creator":"55555","address":"贵阳市北京路21号","point":{"lon":106.62298,"lat":26.67865},"product":[{"id":"2","name":"香蕉","price":20.0,"quantity":5}]}

如果不知道springboot如何整合spring-data-elasticsearch的，可以参考我之前的文章

从零搭建springboot+spring data elasticsearch4.2.x环境

实体类

@Data
@Document(indexName = "order_test")
@Setting(replicas = 0)
public class Order {

    @Id
    private String id;

    // 订单状态 0未付款 1未发货 2运输中 3待签收 4已签收
    @Field(type = FieldType.Integer, name = "status")
    private Integer status;

    @Field(type = FieldType.Keyword, name = "no")
    private String no;

    @Field(type = FieldType.Date, name = "create_time", pattern = "yyyy-MM-dd HH:mm:ss")
    private Date createTime;

    @Field(type = FieldType.Double, name = "amount")
    private Double amount;

    @Field(type = FieldType.Keyword, name = "creator")
    private String creator;

    @GeoPointField
    @Field(name = "point")
    private GeoPoint point;

    @Field(type = FieldType.Text, name = "address", analyzer = "ik_max_word")
    private String address;
    
    @Field(type = FieldType.Nested, name = "creator")
    private List<Product> product;

}

@Data
public class Product implements Serializable {

    @Field(type = FieldType.Long, name = "id")
    private Long id;

    @Field(type = FieldType.Keyword, name = "name")
    private String name;

    @Field(type = FieldType.Double, name = "price")
    private Double price;

    @Field(type = FieldType.Integer, name = "quantity")
    private Double quantity;

}

2. 查询指南

2.1 精确查询 Term

案例:

查询编号为DD202205280003的订单

DSL:

GET order_test/_search
{
  "query": {
    "term": {
      "no": {
        "value": "DD202205280003"
      }
    }
  }
}

java:

    @GetMapping("getByNo")
    public Order getByNo(String no){
        NativeSearchQueryBuilder queryBuilder = new NativeSearchQueryBuilder();
        queryBuilder.withQuery(QueryBuilders.termsQuery("no",no));
        SearchHit<Order> searchRes = restTemplate.searchOne(queryBuilder.build(), Order.class);
        return searchRes == null ? null : searchRes.getContent();
    }

2.2 多值查询 Terms

案例：

查询未付款、未发货的订单

DSL:

GET order_test/_search
{
  "query": {
    "terms": {
      "status": [
        0,
        1
      ]
    }
  }
}

java:

@GetMapping("pageByStatus")
    public PageResult<Order> pageByStatus(int page,int size,int ...status){
        NativeSearchQueryBuilder queryBuilder = new NativeSearchQueryBuilder();
        queryBuilder.withQuery(QueryBuilders.termsQuery("status",status)).withPageable(PageRequest.of(page, size));
        SearchHits<Order> search = restTemplate.search(queryBuilder.build(), Order.class);
        List<Order> collect = search.getSearchHits().stream().map(SearchHit::getContent).collect(Collectors.toList());
        return PageResult.data(collect,search.getTotalHits());
    }

@Data
@AllArgsConstructor
@NoArgsConstructor
public class PageResult<T> implements Serializable {

    private List<T> data;

    private long total;

    public static <T> PageResult<T> data(List<T> data,long count){
      return new PageResult<>(data,count);
    }

}

2.3 范围查询 Range

案例：

查询金额大于100的订单

DSL:

GET order_test/_search
{
  "query": {
    "range": {
      "amount": {
        "gt": 100
      }
    }
  }
}

java:

@GetMapping("listGreaterThanAmount")
    public List<Order> listGreaterThanAmount(int page,int size,Double amount){
        NativeSearchQueryBuilder queryBuilder = new NativeSearchQueryBuilder();
        queryBuilder.withQuery(QueryBuilders.rangeQuery("amount").gt(amount)).withPageable(PageRequest.of(page, size));
        SearchHits<Order> search = restTemplate.search(queryBuilder.build(), Order.class);
        return search.getSearchHits().stream().map(SearchHit::getContent).collect(Collectors.toList());
    }

2.4 模糊查询 Match

案例：

插叙地址中包含‘北京‘的订单

DSL:

GET order_test/_search
{
  "query": {
    "match": {
      "address": "北京"
    }
  }
}

java:

@GetMapping("listMatchAddress")
    public List<Order> listMatchAddress(int page,int size,String address){
        NativeSearchQueryBuilder queryBuilder = new NativeSearchQueryBuilder();
        queryBuilder.withQuery(QueryBuilders.matchQuery("address",address)).withPageable(PageRequest.of(page, size));
        SearchHits<Order> search = restTemplate.search(queryBuilder.build(), Order.class);
        return search.getSearchHits().stream().map(SearchHit::getContent).collect(Collectors.toList());
    }

2.5 嵌套查询 Boolean

案例：

查询5月份下单的待签收的订单

DSL:

GET order_test/_search
{
  "query": {
    "bool": {
      "must": [
        {
          "term": {
            "status": {
              "value": 3
            }
          }
        },
        {
          "range": {
            "create_time": {
              "gte": "2022-05-01 00:00:00",
              "lt": "2022-06-01 00:00:00"
            }
          }
        }
      ]
    }
  }
}

java:

@GetMapping("listRangeTimeAndStatus")
    public List<Order> listRangeTimeAndStatus(int page, int size, Date startTime,Date endTime, Integer status){
        BoolQueryBuilder boolQueryBuilder = new BoolQueryBuilder();
        boolQueryBuilder.must(QueryBuilders.termsQuery("status",status))
                .must(QueryBuilders.rangeQuery("create_time").gte(startTime).gt(endTime));
        NativeSearchQueryBuilder queryBuilder = new NativeSearchQueryBuilder();
        queryBuilder.withQuery(boolQueryBuilder).withPageable(PageRequest.of(page, size));
        SearchHits<Order> search = restTemplate.search(queryBuilder.build(), Order.class);
        return search.getSearchHits().stream().map(SearchHit::getContent).collect(Collectors.toList());
    }

如果这里出现日期类型转换报错，需要添加转换类

@Component
public class DateConverter implements Converter<String, Date> {

    @Override
    public Date convert(@NonNull String source) {
        try {
            return new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(source);
        } catch (ParseException e) {
            try {
                return new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss'+08:00'").parse(source);
            } catch (ParseException ex) {
                try {
                    return new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss").parse(source);
                } catch (ParseException ex2) {
                    ex2.printStackTrace();
                }
            }
        }
        return null;
    }
}

2.6 json数组查询 Nested

案例：

查询购买了香蕉的订单

DSL:

GET order_test/_search
{
  "query": {
    "nested": {
      "path": "product",
      "query": {
        "term": {
          "product.name": {
            "value": "香蕉"
          }
        }
      }
    }
  }
}

java:

@GetMapping("listByProductName")
    public List<Order> listByProductName(int page,int size,String name){
        NativeSearchQueryBuilder queryBuilder = new NativeSearchQueryBuilder();
        queryBuilder.withQuery(QueryBuilders.nestedQuery("product",
                QueryBuilders.termQuery("product.name",name),ScoreMode.Max))
                .withPageable(PageRequest.of(page, size));
        SearchHits<Order> search = restTemplate.search(queryBuilder.build(), Order.class);
        return search.getSearchHits().stream().map(SearchHit::getContent).collect(Collectors.toList());
    }

2.7 坐标查询 Geo

案例：

查询地址在北京20km范围内的订单

DSL:

GET order_test/_search
{
  "query": {
    "geo_distance":{
      "distance": "20km",
      "point":{
        "lon": 116.23128,
        "lat": 40.22077
      }
    }
  }
}

java:

 @GetMapping("listByPointAndDistance")
    public List<Order> listByPointAndDistance(int page, int size, Double lat,Double lon, String distance){
        NativeSearchQueryBuilder queryBuilder = new NativeSearchQueryBuilder();
        GeoDistanceQueryBuilder distanceQueryBuilder = new GeoDistanceQueryBuilder("point");
        distanceQueryBuilder.point(new GeoPoint(lat,lon)).distance(distance);
        queryBuilder.withQuery(distanceQueryBuilder).withPageable(PageRequest.of(page, size));
        SearchHits<Order> search = restTemplate.search(queryBuilder.build(), Order.class);
        return search.getSearchHits().stream().map(SearchHit::getContent).collect(Collectors.toList());
    }

3. spring-data-elasticsearch3.x版本实现

我们上述的演示都是在4.2.x版本完成的，但很多公司还在使用3.x版本，两者之间实现略有区别。

两个版本之间构建queryBuilder是相同的，只是ElasticsearchRestTemplate的调用接口不一样。

在3.x版本下要执行查询需要调用queryXXX方法，可以看到支持多种，根据方法名我们可以知道有支持分页的、别名查询的、ID查询的、集合查询的。可以根据具体的业务选择不同的方法

比如我们上述的案例：

查询购买了香蕉的订单

3.2.12.RELEASE版本的实现如下：

@GetMapping("listByProductName")
    public List<Order> listByProductName(int page, int size, String name){
        NativeSearchQueryBuilder queryBuilder = new NativeSearchQueryBuilder();
        queryBuilder.withQuery(QueryBuilders.nestedQuery("product",
                        QueryBuilders.termQuery("product.name",name), ScoreMode.Max))
                .withPageable(PageRequest.of(page, size));
        AggregatedPage<Order> orders = restTemplate.queryForPage(queryBuilder.build(), Order.class);
        return orders.getContent();
    }

针对复杂查询的介绍就到此为止了，只是列举了其中比较常用的，如果有你不知道怎么实现的，可以留言讨论

关注公众号Elasticsearch之家，回复 ‘复杂查询源码’ 获取文中项目源码