Elasticsearch 7.17.4 复杂查询

小码氓

已于 2023-01-12 22:07:21 修改

阅读量1.5k

点赞数 2

分类专栏： springboot elasticsearch 文章标签： elasticsearch 大数据

于 2023-01-12 22:06:32 首次发布

本文链接：https://blog.csdn.net/weixin_44065695/article/details/128666526

版权

springboot 同时被 2 个专栏收录

7 篇文章 1 订阅

订阅专栏

elasticsearch

1 篇文章 0 订阅

订阅专栏

SpringBoot 集成 Elasticsearch 复杂查询

版本

Springboot 2.7.1 + Spring Cloud 3.1.3 + Elasticsearch 7.17.4

集成了Spring data Elasticsearch,简单查询使用类似于JPA的方式快速实现，复杂查询使用ElasticsearchRestTemplate

ElasticsearchRepository 实现简单查询

public interface OrderResp extends ElasticsearchRepository<Order,Long> {

	/**
	 * 
	 * SQL : SELECT * FROM order WHERE order_form_id = #{orderNo} AND state IN(stateList) AND address LIKE '%#{address}%'
	 */
	List findByOrderFormidAndStateInAndaAndAddressContains(String orderNo, List<Integer> stateList, String address);
}

创建DAO层接口，继承ElasticsearchRepository，在接口内findBy会提示相应字段，进行检索。

多条件查询分页

public PageResult<QueryCustomerResponse> queryCustomerList(QueryCustomerRequest request) {

		NativeSearchQueryBuilder nativeSearchQueryBuilder = new NativeSearchQueryBuilder();
		// 执行布尔查询，通过多个条件进行过滤
		BoolQueryBuilder boolQueryBuilder = QueryBuilders.boolQuery();
		// 客户状态
		if(request.getStatus() != null){
			boolQueryBuilder.must(QueryBuilders.termQuery("status", request.getStatus()));
		}
		// 客户等级
		if(CollectionUtil.isNotEmpty(request.getMemberLevelId())){
			boolQueryBuilder.must(QueryBuilders.termsQuery("memberLevelId", request.getMemberLevelId()));
		}
		// 用户性别
		if(CollectionUtil.isNotEmpty(request.getGender())){
			boolQueryBuilder.must(QueryBuilders.termsQuery("gender", request.getGender()));
		}
		// 手机号
		if(StringUtil.isNotBlank(request.getPhone())){
			boolQueryBuilder.must(QueryBuilders.termsQuery("mobileMain", request.getPhone()));
		}
		// 姓名
		if(StringUtil.isNotBlank(request.getUserName())){
			boolQueryBuilder.must(QueryBuilders.wildcardQuery("userName", "*" + request.getUserName()+"*"));
		}
		// 年龄
		if(CollectionUtil.isNotEmpty(request.getAgeType())){
			BoolQueryBuilder boolQuery = QueryBuilders.boolQuery();
			for (Integer integer : request.getAgeType()) {
				AgeTypeEnum typeEnum = AgeTypeEnum.byType(integer);
				boolQuery.should(QueryBuilders.rangeQuery("age").gte(typeEnum.getStart()).lte(typeEnum.getEnd()));
			}
			boolQueryBuilder.must(boolQuery);
		}
		// 客户创建时间
		if(request.getTimeType() != null){
			// 自定义时间
			if(request.getTimeType() == 11){
				boolQueryBuilder.must(QueryBuilders.rangeQuery("createTime").from(request.getStartTime()).to(request.getEndTime()));
			}
		}
		// 积分范围
		if(request.getStartCredit() != null){
			boolQueryBuilder.must(QueryBuilders.rangeQuery("credit").gte(request.getStartCredit()));
		}
		if(request.getEndCredit() != null){
			boolQueryBuilder.must(QueryBuilders.rangeQuery("credit").lte(request.getEndCredit()));
		}
		nativeSearchQueryBuilder.withQuery(boolQueryBuilder);
		// 构建查询
		NativeSearchQuery build = nativeSearchQueryBuilder.build();
		// 构建分页
		build.setPageable(PageRequest.of(request.getPageNo() - 1, request.getPageSize()));
		SearchHits<User> searchHits = restTemplate.search(build, User.class);
		// 构建返回列表
		ArrayList<QueryCustomerResponse> responses = new ArrayList<>();
		List<SearchHit<User>> hits = searchHits.toList();
		// 转为需要返回的DTO
		for (SearchHit<User> hit : hits) {
			responses.add(new QueryCustomerResponse(hit.getContent()));
		}
		PageResult<QueryCustomerResponse> pageResult = new PageResult<>();
		pageResult.setTotal(Integer.parseInt(searchHits.getTotalHits()+""));
		pageResult.setData(responses);
		return pageResult;
	}

聚合查询

根据用户ID汇总该用户订单统计信息

		// 查询累计订单值
		NativeSearchQueryBuilder accumulativeOrderQuery = new NativeSearchQueryBuilder();
		// 执行布尔查询，通过多个条件进行过滤
		BoolQueryBuilder recentBoolQuery = QueryBuilders.boolQuery();
		// 状态为2-待发货 3-待收货 4-已完成的最新订单
		recentBoolQuery.must(QueryBuilders.termQuery("userId", request.getId())).must(QueryBuilders.termsQuery("state", CrmConstant.ORDER_VALID_STATE));
		// 查询条件
		accumulativeOrderQuery.withQuery(recentBoolQuery);
		// SUM聚合查询累计 
		accumulativeOrderQuery.withAggregations(
			// 根据UserId分组
			AggregationBuilders.terms("parentId").field("userId").
			// SUM总金额
			subAggregation(AggregationBuilders.sum("orderPriceSum").field("orderPrice")).
			subAggregation(AggregationBuilders.sum("priceSum").field("price")).
			subAggregation(AggregationBuilders.sum("productNumber").field("productNumber")));
		SearchHits<Order> search = restTemplate.search(accumulativeOrderQuery.build(), Order.class);
		Aggregations aggregations = (Aggregations) Objects.requireNonNull(search.getAggregations()).aggregations();
		Terms terms = (Terms) aggregations.asMap().get("parentId");
		// 遍历取出聚合字段列的值，与对应的数量
		for (Terms.Bucket bucket : terms.getBuckets()) {
			// 解析嵌套聚合
			Aggregations sumAggregations = bucket.getAggregations();
			if (sumAggregations != null) {
				for (Aggregation aggregation : sumAggregations.asList()) {
					if("orderPriceSum".equals(aggregation.getName())){
						ParsedSum sum = (ParsedSum) aggregation;
						// 订单累计总金额
						orderInfo.setOrderPriceSum(sum.getValue()+"");
					}
					if("priceSum".equals(aggregation.getName())){
						ParsedSum sum = (ParsedSum) aggregation;
						// 订单累计总金额
						orderInfo.setPriceSum(sum.getValue()+"");
					}
					if("productNumber".equals(aggregation.getName())){
						ParsedSum sum = (ParsedSum) aggregation;
						// 订单累计总金额
						orderInfo.setSumProductNumber((int)sum.getValue());
					}
				}
			}
		}
		// 消费次数
		orderInfo.setOrderCount((int)search.getTotalHits());

根据聚合结果过滤/分页

业务场景：根据用户消费金额筛选出对应用户

public PageResult<QueryCustomerResponse> queryCustomerListByConsume(QueryCustomerRequest request) {
		NativeSearchQueryBuilder queryBuilder = new NativeSearchQueryBuilder();
		// 有效订单
		queryBuilder.withQuery(QueryBuilders.termsQuery("state", CrmConstant.ORDER_VALID_STATE));
		// 声明BucketPath, 用于bucket筛选
		HashMap<String, String> hashMap = new HashMap<>();
		String scriptDSL = "";
		// 设置过滤条件和查询脚本 订单累计总金额
		if(StringUtil.isNotBlank(request.getAccumulativeMoneyStart()) && StringUtil.isNotBlank(request.getAccumulativeMoneyEnd())){
			hashMap.put("orderPrice", "orderPrice");
			scriptDSL += "";
			scriptDSL += StringUtil.isNotBlank(scriptDSL) ? " && " : "";
			scriptDSL += "params.orderPrice >=" + request.getAccumulativeMoneyStart() + " && " + "params.orderPrice <=" + request.getAccumulativeMoneyEnd();
		}
		// 订单累计支付金额
		if(StringUtil.isNotBlank(request.getAccumulativePayMoneyStart()) && StringUtil.isNotBlank(request.getAccumulativePayMoneyEnd())){
			hashMap.put("payPrice", "payPrice");
			scriptDSL += "";
			scriptDSL += StringUtil.isNotBlank(scriptDSL) ? " && " : "";
			scriptDSL += "params.payPrice >=" + request.getAccumulativePayMoneyStart() + " && " + "params.payPrice <=" + request.getAccumulativePayMoneyEnd();
		}
		// 订单累计支付金额
		if(request.getAccumulativeNumberStart() != null && request.getAccumulativeNumberEnd() != null){
			hashMap.put("cou", "cou");
			scriptDSL += "";
			scriptDSL += StringUtil.isNotBlank(scriptDSL) ? " && " : "";
			scriptDSL += "params.cou >=" + request.getAccumulativeNumberStart() + " && " + "params.cou <=" + request.getAccumulativeNumberEnd();
		}

		// 设置条件脚本
		Script script = new Script(scriptDSL);
		// 聚合统计 分页
		TermsAggregationBuilder aggregationBuilder = AggregationBuilders.terms("id").field("userId")
			// 累计总金额orderPrice
			.subAggregation(AggregationBuilders.sum("orderPrice").field("orderPrice"))
			// 累计消费次数
			.subAggregation(AggregationBuilders.count("cou").field("id"))
			// 累计支付金额
			.subAggregation(AggregationBuilders.sum("payPrice").field("price"))
			// 分页（可以分页 若获取总页数需要单独查询，这里没有使用，在下面使用分页工具类）
			//.subAggregation(new BucketSortPipelineAggregationBuilder("id", null).from(request.getPageNo()).size(request.getPageSize()))
			// 排序
			.order(BucketOrder.aggregation("orderPrice", false));
		if(StringUtil.isNotBlank(scriptDSL)){
			// having 过滤条件
			aggregationBuilder.subAggregation(PipelineAggregatorBuilders.bucketSelector("having", hashMap, script));
		}
		// 根据routing分组
		queryBuilder.withAggregations(aggregationBuilder);

		SearchHits<Order> search = restTemplate.search(queryBuilder.build(), Order.class);

		Aggregations aggregations = (Aggregations) Objects.requireNonNull(search.getAggregations()).aggregations();
		Terms terms = (Terms) aggregations.asMap().get("id");
		ArrayList<Long> userIds = new ArrayList<>();
		// 分页工具类分页
		PageUtil<Terms.Bucket> page = new PageUtil<>();
		page.queryPager(request.getPageNo(), request.getPageSize(), terms.getBuckets());
		// 遍历取出聚合字段列的值，与对应的数量
		for (Terms.Bucket bucket : page.getList()) {
			// 解析嵌套聚合
			Aggregations sumAggregations = bucket.getAggregations();
			if (sumAggregations != null) {
				userIds.add(Long.parseLong(bucket.getKey().toString()));
				System.out.println("userId: " + bucket.getKey() + ", value:" + JsonUtil.toJson(sumAggregations.getAsMap()));
			}
		}
		// 根据ID查询会员信息
		Iterable<CrmDataToC> dataToCS = crmDataCResp.findAllById(userIds);
		// 构建返回列表
		ArrayList<QueryCustomerResponse> responses = new ArrayList<>();
		for (CrmDataToC dataToC : dataToCS) {
			responses.add(new QueryCustomerResponse(dataToC));
		}
		PageResult<QueryCustomerResponse> pageResult = new PageResult<>();
		pageResult.setTotal(Integer.parseInt(page.getTotalRecords()+""));
		pageResult.setData(responses);
		return pageResult;
	}

父子关联查询

官方文档

这里使用RestHighLevelClient 进行查询

根据父ID查询所有子文档

/**
  * indexName 索引名称
  * childType 子类型
  * parentId 父ID
  * valueType 返回类型 这里做了封装
  */
public <T> List<T> queryByParentId (String indexName, String childType, String parentId, Class<T> valueType){
		ArrayList<T> list = new ArrayList<>();
		SearchRequest search= new SearchRequest(indexName);
		SearchSourceBuilder searchSourceBuilder = new SearchSourceBuilder();
		ParentIdQueryBuilder build = JoinQueryBuilders.parentId(childType ,parentId);
		searchSourceBuilder.query(build);
		searchSourceBuilder.sort("createTime", SortOrder.ASC);
		search.source(searchSourceBuilder);
		SearchResponse response = null;
		try {
			response = client.search(search, RequestOptions.DEFAULT);
			response.getHits().forEach(hi ->{
				T parse = JsonUtil.parse(hi.getSourceAsString(), valueType);
				list.add(parse);
			});
		} catch (IOException e) {
			e.printStackTrace();
		}
		return list;
	}

根据参数查询符合条件的父文档关联的子文档（父查子）

/**
	 *
	 * @param indexName 索引名
	 * @param parentType 父文档类型
	 * @param queryBuilder 查询参数
	 * @param valueType 返回值类型
	 */
	public <T> List<T> hasParent(String indexName, String parentType, QueryBuilder queryBuilder, Class<T> valueType) throws Exception {
		ArrayList<T> list = new ArrayList<>();
		SearchRequest search = new SearchRequest(indexName);
		SearchSourceBuilder searchSourceBuilder = new SearchSourceBuilder();
		// 根据parent中的条件查询子文档, type 为父文档类型
		HasParentQueryBuilder build= JoinQueryBuilders.hasParentQuery(parentType, queryBuilder, false);
		searchSourceBuilder.query(build);
		search.source(searchSourceBuilder);
		SearchResponse response=client.search(search, RequestOptions.DEFAULT);
		response.getHits().forEach(hi ->{
			T parse = JsonUtil.parse(hi.getSourceAsString(), valueType);
			list.add(parse);
		});
		return list;
	}

根据参数查询符合条件的子文档关联的父文档（子查父）

/**
 *@param indexName 索引名
 * @param childType 子文档类型
 * @param queryBuilder 查询参数
 * @param valueType 返回值
 */
public <T> List<T> hasChild(String indexName, String childType, QueryBuilder queryBuilder, Class<T> valueType) throws Exception {
	ArrayList<T> list = new ArrayList<>();
	SearchRequest search= new SearchRequest(indexName);
	SearchSourceBuilder searchSourceBuilder = new SearchSourceBuilder();
	HasChildQueryBuilder build=JoinQueryBuilders.hasChildQuery(childType, queryBuilder, ScoreMode.None);
	searchSourceBuilder.query(build);
	search.source(searchSourceBuilder);
	SearchResponse response=client.search(search, RequestOptions.DEFAULT);
	response.getHits().forEach(hi ->{
		T t = JsonUtil.parse(hi.getSourceAsString(), valueType);
		list.add(t);
	});
	return list;
}