引言
最近小编负责的一个语音质检 项目中用到了ES，下面结合实例代码分享一下java操作ES查用的查询写法。
关于es的基本操作，例如新建索引、查询封装类，请参考前面博客：【ElasticSearch实战】——封装java操作es基础架构
1、ES在项目中的位置
2、项目查询需求
从上面查询条件来看，其中包括了精确查询，模糊查询及时间范围查询等，这些查询 都会在下面 样例代码中给出，并且还有部分统计的写法，例如机器质检不合格的数量， 人工审核不合格的数量等。
3、查询代码
package com.jack.search.es.service; import com.jack.common.constant.SearchConstants; import com.jack.search.es.model.CaseCallCheckListQueryInfo; import org.apache.commons.lang.StringUtils; import org.elasticsearch.action.search.SearchRequest; import org.elasticsearch.action.search.SearchResponse; import org.elasticsearch.index.query.BoolQueryBuilder; import org.elasticsearch.index.query.QueryBuilders; import org.elasticsearch.index.query.RangeQueryBuilder; import org.elasticsearch.search.SearchHit; import org.elasticsearch.search.SearchHits; import org.elasticsearch.search.aggregations.Aggregation; import org.elasticsearch.search.aggregations.AggregationBuilders; import org.elasticsearch.search.aggregations.Aggregations; import org.elasticsearch.search.aggregations.bucket.filter.Filter; import org.elasticsearch.search.aggregations.bucket.filter.FilterAggregationBuilder; import org.elasticsearch.search.aggregations.metrics.cardinality.Cardinality; import org.elasticsearch.search.aggregations.metrics.sum.Sum; import org.elasticsearch.search.builder.SearchSourceBuilder; import org.elasticsearch.search.sort.FieldSortBuilder; import org.elasticsearch.search.sort.SortBuilders; import org.elasticsearch.search.sort.SortOrder; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; import java.util.stream.Collectors; import java.util.stream.Stream; /** * @author zhenghao * @description: 录音库查询 * @date 2019/10/1115:49 */ @Service public class CallCheckQueryService { @Autowired private QueryService queryService; @Autowired private ElasticSearchService elasticSearchService; public Map<String, Object> getCallCheckList(CaseCallCheckListQueryInfo queryInfo) { if (queryInfo == null) { return null; } //构造查询条件 BoolQueryBuilder boolQueryBuilder = this.getCallCheckQueryBuilder(queryInfo); //排序 String orderBy = queryInfo.getOrderBy(); if (StringUtils.isEmpty(orderBy)) { orderBy = String.format("%s:%s", "telTime", "desc"); } //分页 Integer pageNo = queryInfo.getPageNo(); if ((pageNo == null) || (pageNo <= 0)) { pageNo = 0; } else { pageNo -= 1; } Integer pageSize = queryInfo.getPageSize(); if ((pageSize == null) || (pageSize < 1)) { pageSize = 10; } //构造排序构造器FieldSortBuilder，设置排序参数 String[] orderStr = orderBy.split(":"); FieldSortBuilder sort = SortBuilders.fieldSort(orderStr[0]).order("desc".equalsIgnoreCase(orderStr[1])? SortOrder.DESC:SortOrder.ASC); //AggregationBuilder 相当于 mysql中的 group by //第一级机器质检不合格数据聚合 聚合名称为 hasError ，聚合数据条件为 字段 hasError>=1 FilterAggregationBuilder hasErrorAggregationBuilder = AggregationBuilders.filter("hasError", QueryBuilders.rangeQuery("hasError").gte(1)); //构造第二级子聚合 每一个质检项的值 >=1 注意：每一个checkItem 是一个字段，代表一个质检项 0代表没有命中该质检项，大于0代表表中 for (int i = SearchConstants.CHECK_ITEM_MIN; i<= SearchConstants.CHECK_ITEM_MAX; i++) { String checkItemName = String.format("checkItem%d", i); hasErrorAggregationBuilder.subAggregation(AggregationBuilders.filter(checkItemName, QueryBuilders.rangeQuery(checkItemName).gte(1))); } //第一级人工审核不合格的数据聚合 FilterAggregationBuilder auditHasErrorAggregationBuilder = AggregationBuilders.filter("auditHasError", QueryBuilders.rangeQuery("auditHasError").gte(1)); //整合查询条件，相当于组长sql cardinality 表示去重 FilterAggregationBuilder allCheckAggregationBuilder = AggregationBuilders.filter("allCheck", boolQueryBuilder) .subAggregation(hasErrorAggregationBuilder) .subAggregation(auditHasErrorAggregationBuilder) .subAggregation(AggregationBuilders.sum("totalTelLength").field("telLength")) .subAggregation(AggregationBuilders.cardinality("userId").field("userId")); SearchSourceBuilder searchSourceBuilder = new SearchSourceBuilder(); searchSourceBuilder.query(boolQueryBuilder).sort(sort).from(pageNo*pageSize).size(pageSize).aggregation(allCheckAggregationBuilder); //执行查询 SearchRequest searchRequest = new SearchRequest(); searchRequest.indices(SearchConstants.CALL_INDEX_NAME).types(SearchConstants.CALL_INDEX_TYPE).source(searchSourceBuilder); SearchResponse searchResponse = elasticSearchService.search(searchRequest); if (searchResponse == null) { return null; } //从查询结果中获得想要数据 SearchHits searchHits = searchResponse.getHits(); //获得查询结果条数 long total = searchHits.getTotalHits(); Map<String, Object> resultMap = new HashMap<>(10); resultMap.put("total", total); resultMap.put("tookInMillis", searchResponse.getTook().getMillis()); //获得查询结果并转换为map if (total > 0) { SearchHit[] searchHitsArr = searchHits.getHits(); List<Map<String, Object>> resultMapList = new ArrayList<>(searchHitsArr.length); for (int i = 0; i < searchHitsArr.length; i++) { Map<String, Object> map = searchHitsArr[i].getSourceAsMap(); resultMapList.add(map); } resultMap.put("list", resultMapList); } //获得聚合桶数据 Filter allCheckFilter = searchResponse.getAggregations().get("allCheck"); Aggregations allCheckAggregations = allCheckFilter.getAggregations(); //通话总时长 Sum sum = allCheckAggregations.get("totalTelLength"); Cardinality userIdCardinality = allCheckAggregations.get("userId"); //机器审核错误的数量 Filter hasErrorFilter = allCheckAggregations.get("hasError"); Aggregations hasErrorAggregations = hasErrorFilter.getAggregations(); //人工审核错误的数量 Filter auditHasErrorFilter = allCheckAggregations.get("auditHasError"); long errorCount = hasErrorFilter.getDocCount(); long auditErrorCount = auditHasErrorFilter.getDocCount(); resultMap.put("errorCount", errorCount); resultMap.put("auditErrorCount", auditErrorCount); resultMap.put("goodRate", total>0?1.0*(total-errorCount)/total:0); resultMap.put("auditGoodRate", total>0?1.0*(total-auditErrorCount)/total:0); resultMap.put("totalTelLength", sum.getValue()); resultMap.put("averageTelLength", total>0?sum.getValue()/total:0); resultMap.put("userCount", userIdCardinality.getValue()); for (int k = SearchConstants.CHECK_ITEM_MIN; k<= SearchConstants.CHECK_ITEM_MAX; k++) { Filter checkItemFilter = hasErrorAggregations.get(String.format("checkItem%d", k)); resultMap.put(String.format("checkItem%dCount", k), checkItemFilter.getDocCount()); } return resultMap; } /** * @Description: 构造查询条件 * @author: zhenghao * @date: 2019/10/11 15:54 */ private BoolQueryBuilder getCallCheckQueryBuilder(CaseCallCheckListQueryInfo queryInfo) { /** * BoolQueryBuilder 是es提供的一个查询接口，可以对其进行参数设置完成对应的查询 */ BoolQueryBuilder boolQueryBuilder = new BoolQueryBuilder(); //根据主键id查询 Long[] callRecordIds = queryInfo.getCallRecordIds(); if ((callRecordIds != null) && (callRecordIds.length > 0)) { String[] strCallRecordIds = new String[callRecordIds.length]; for (int i = 0; i < callRecordIds.length; i++) { strCallRecordIds[i] = String.valueOf(callRecordIds[i]); } //idsQuery查询多个id 相当于mysql中的 where id in(1,2,3) //filter必须匹配，但他以不评分、过滤模式来进行，这些语句对评分没有贡献，只是根据过滤标准来排除或包含文档 boolQueryBuilder.filter(QueryBuilders.idsQuery().addIds(strCallRecordIds)); } //根据caseId精确查询 termQuery 完全匹配 Long caseId = queryInfo.getCaseId(); if (caseId != null) { boolQueryBuilder.filter(QueryBuilders.termQuery("caseId", caseId)); } //termsQuery 一次匹配多个值，查询多个公司的数据 Long[] partnerIds = queryInfo.getPartnerIds(); if ((partnerIds != null) && (partnerIds.length > 0)) { long[] pids = new long[partnerIds.length]; for (int i = 0; i < pids.length; i++) { pids[i] = partnerIds[i]; } boolQueryBuilder.filter(QueryBuilders.termsQuery("partnerId", pids)); } //注意在es中是long类型 非包装类型Long Long[] departmentIds = queryInfo.getDepartmentIds(); if ((departmentIds != null) && (departmentIds.length > 0)) { long[] dids = new long[departmentIds.length]; for (int i = 0; i < dids.length; i++) { dids[i] = departmentIds[i]; } boolQueryBuilder.filter(QueryBuilders.termsQuery("departmentId", dids)); } //根据web端传递的参数不同，区分对应的userId不同的值 Integer allotState = queryInfo.getAllotState(); if (allotState != null) { if (allotState == 0) { //精确查询 boolQueryBuilder.filter(QueryBuilders.termQuery("userId", -1)); } else { Long userId = queryInfo.getUserId(); if (userId != null) { //精确查询 boolQueryBuilder.filter(QueryBuilders.termQuery("userId", userId)); } else { //范围查询 gt 代表大于 boolQueryBuilder.filter(QueryBuilders.rangeQuery("userId").gt(0)); } } } else { Long userId = queryInfo.getUserId(); if (userId != null) { boolQueryBuilder.filter(QueryBuilders.termQuery("userId", userId)); } else { Integer auditHasError = queryInfo.getAuditHasError(); // 查询已经分配质检用户的质检列表 if ((auditHasError != null) && (auditHasError == -1)) { boolQueryBuilder.filter(QueryBuilders.rangeQuery("userId").gt(0)); } } } //将string类型的字段值 去掉*匹配符及空格 String userName = queryService.trimWildcardQuery(queryInfo.getUserName()); if (!StringUtils.isEmpty(userName)) { //精确查询 boolQueryBuilder.filter(QueryBuilders.termQuery("userName", userName)); } Long auditUserId = queryInfo.getAuditUserId(); if (auditUserId != null) { boolQueryBuilder.filter(QueryBuilders.termQuery("auditUserId", auditUserId)); } String auditUserName = queryService.trimWildcardQuery(queryInfo.getAuditUserName()); if (!StringUtils.isEmpty(auditUserName)) { boolQueryBuilder.filter(QueryBuilders.termQuery("auditUserName", auditUserName)); } Long clientId = queryInfo.getClientId(); if (clientId != null) { boolQueryBuilder.filter(QueryBuilders.termQuery("clientId", clientId)); } Long batchId = queryInfo.getBatchId(); if (batchId != null) { boolQueryBuilder.filter(QueryBuilders.termQuery("batchId", batchId)); } String caseCode = queryService.trimWildcardQuery(queryInfo.getCaseCode()); if (caseCode != null) { //wildcardQuery模糊查询，相当于mysql中的like boolQueryBuilder.filter(QueryBuilders.wildcardQuery("caseCode", String.format("*%s*", caseCode))); } String borrowerName = queryService.trimWildcardQuery(queryInfo.getBorrowerName()); if (!StringUtils.isEmpty(borrowerName)) { //wildcardQuery模糊查询，相当于mysql中的like boolQueryBuilder.filter(QueryBuilders.wildcardQuery("borrowerName", String.format("*%s*", borrowerName))); } String contactName = queryService.trimWildcardQuery(queryInfo.getContactName()); if (!StringUtils.isEmpty(contactName)) { boolQueryBuilder.filter(QueryBuilders.wildcardQuery("contactName", String.format("*%s*", contactName))); } String contactTel = queryService.trimWildcardQuery(queryInfo.getContactTel()); if (!StringUtils.isEmpty(contactTel)) { boolQueryBuilder.filter(QueryBuilders.wildcardQuery("contactTel", String.format("*%s*", contactTel))); } String fileName = queryService.trimWildcardQuery(queryInfo.getFileName()); if (!StringUtils.isEmpty(fileName)) { boolQueryBuilder.filter(QueryBuilders.wildcardQuery("fileName", String.format("*%s*", fileName))); } //常见需求范围查询 查询某个时间段范围内的数据 Long telTimeMin = queryInfo.getTelTimeMin(); Long telTimeMax = queryInfo.getTelTimeMax(); if ((telTimeMin != null) || (telTimeMax != null)) { //获得范围查询builder RangeQueryBuilder rangeQueryBuilder = QueryBuilders.rangeQuery("telTime"); if (telTimeMin != null) { //gte 代表大于等于 rangeQueryBuilder.gte(telTimeMin); } if (telTimeMax != null) { //lt 代表小于某个值 lte代表小于等于某个值 rangeQueryBuilder.lt(telTimeMax); } boolQueryBuilder.filter(rangeQueryBuilder); } //查询某个时间段范围内的数据 Long telMillSecMin = queryInfo.getTelMillSecMin(); Long telMillSecMax = queryInfo.getTelMillSecMax(); if ((telMillSecMin != null) || telMillSecMax != null) { RangeQueryBuilder rangeQueryBuilder = QueryBuilders.rangeQuery("telMillSec"); if (telMillSecMin != null) { rangeQueryBuilder.gte(telMillSecMin); } if (telMillSecMax != null) { rangeQueryBuilder.lt(telMillSecMax); } } //时间范围查询 Long auditTimeMin = queryInfo.getAuditTimeMin(); Long auditTimeMax = queryInfo.getAuditTimeMax(); if ((auditTimeMin != null) || (auditTimeMax != null)) { RangeQueryBuilder rangeQueryBuilder = QueryBuilders.rangeQuery("auditTime"); if (auditTimeMin != null) { rangeQueryBuilder.gte(auditTimeMin); } if (auditTimeMax != null) { rangeQueryBuilder.lt(auditTimeMax); } boolQueryBuilder.filter(rangeQueryBuilder); } String contactRelationName = queryService.trimWildcardQuery(queryInfo.getContactRelationName()); if (!StringUtils.isEmpty(contactRelationName)) { boolQueryBuilder.filter(QueryBuilders.wildcardQuery("contactRelationName", String.format("*%s*", contactRelationName))); } String telNumber = queryService.trimWildcardQuery(queryInfo.getTelNumber()); if (!StringUtils.isEmpty(telNumber)) { boolQueryBuilder.filter(QueryBuilders.wildcardQuery("telNumber", String.format("*%s*", telNumber))); } Integer hasError = queryInfo.getHasError(); if (hasError == null) { boolQueryBuilder.filter(QueryBuilders.rangeQuery("hasError").gte(-1)); } else { if (hasError >= 0) { boolQueryBuilder.filter(QueryBuilders.termQuery("hasError", hasError)); } else { boolQueryBuilder.filter(QueryBuilders.rangeQuery("hasError").gte(0)); } } Integer auditHasError = queryInfo.getAuditHasError(); if (auditHasError == null) { boolQueryBuilder.filter(QueryBuilders.rangeQuery("auditHasError").gte(-1)); } else if (auditHasError >= -1) { boolQueryBuilder.filter(QueryBuilders.termQuery("auditHasError", auditHasError)); } String auditRemark = queryService.trimWildcardQuery(queryInfo.getAuditRemark()); if (!StringUtils.isEmpty(contactName)) { boolQueryBuilder.filter(QueryBuilders.wildcardQuery("auditRemark", String.format("*%s*", auditRemark))); } Long[] recheckDepartmentIds = queryInfo.getRecheckDepartmentIds(); if ((recheckDepartmentIds != null) && (recheckDepartmentIds.length > 0)) { long[] rdids = new long[recheckDepartmentIds.length]; for (int i = 0; i < rdids.length; i++) { rdids[i] = recheckDepartmentIds[i]; } boolQueryBuilder.filter(QueryBuilders.termsQuery("recheckDepartmentId", rdids)); } //根据数据记录中是否存在某个字段进行查询 Long recheckUserId = queryInfo.getRecheckUserId(); if (recheckUserId != null) { boolQueryBuilder.filter(QueryBuilders.termQuery("recheckUserId", recheckUserId)); } else { Integer allotRecheckState = queryInfo.getAllotRecheckState(); if (allotRecheckState != null) { if (allotRecheckState == 0) { //mustNot 档必须不匹配这些条件才能被包含进来，existsQuery 存在指定字段 BoolQueryBuilder recheckUserIdQueryBuilder = new BoolQueryBuilder(); //本条件是：返回不包含recheckUserId的字段的数据记录 recheckUserIdQueryBuilder.mustNot(QueryBuilders.existsQuery("recheckUserId")); boolQueryBuilder.filter(recheckUserIdQueryBuilder); } else { //本条件是：返回包含recheckUserId 这个字段的数据记录 boolQueryBuilder.filter(QueryBuilders.existsQuery("recheckUserId")); } } Integer recheckHasError = queryInfo.getRecheckHasError(); if (recheckHasError != null) { boolQueryBuilder.filter(QueryBuilders.termQuery("recheckHasError", recheckHasError)); } else { Integer hasRecheck = queryInfo.getHasRecheck(); if (hasRecheck != null) { if (hasRecheck == -1) { boolQueryBuilder.filter(QueryBuilders.existsQuery("recheckUserId")); } else if (hasRecheck == 0) { //和上面不同的写法 boolQueryBuilder.mustNot(QueryBuilders.existsQuery("recheckHasError")); } else { boolQueryBuilder.filter(QueryBuilders.existsQuery("recheckHasError")); } } } Long recheckTimeMin = queryInfo.getRecheckTimeMin(); Long recheckTimeMax = queryInfo.getRecheckTimeMax(); if ((recheckTimeMin != null) || (recheckTimeMax != null)) { RangeQueryBuilder rangeQueryBuilder = QueryBuilders.rangeQuery("recheckTime"); if (recheckTimeMin != null) { rangeQueryBuilder.gte(recheckTimeMin); } if (recheckTimeMax != null) { rangeQueryBuilder.lt(recheckTimeMax); } boolQueryBuilder.filter(rangeQueryBuilder); } } return boolQueryBuilder; } }
小编在项目中将代码做了详细的 注释，通过注释可以看到所有的写法。
如果 本项目对您有帮助，记得给 星星。本项目后续会继续完善java对ES的操作，包括各种复杂查询和统计。
