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之家，回复 ‘复杂查询源码’ 获取文中项目源码
