JAVA es查询 java es查询语法

案例1:普通的条件查询，多条件查询
查询表订单表order中orderStatus为4，orderType为9，billSupplierId 为(11111,2222,33333) ,billCreateTime在1603728000000与1603814400000之间的数据
数据库查询语句

select * from order 
where 
orderStatus = 4 
and orderType = 9 
and billSupplierId in (11111,2222,33333)
and billCreateTime between 1603728000000 and 1603814400000；

ES语法

POST /order/_search
{
	"query": {
		"bool": {
			"must": [{
				"term": {
					"orderStatus": 4
				}
			},{
			  "term": {
			    "orderType": {
			      "value": 9
			    }
			  }
			},{
			  "terms": {
			    "billSupplierId": [
			      11111,2222,33333
			    ]
			  }
			}],
			"filter": {
				"range": {
					"billCreateTime": {
						"gte": 1603728000000,
						"lte": 1603814400000
					}
				}
			}
		}
	}
}

在java对应的写法

//构建一个ES的查询客户端，restClientManager这个是每个公司自己定义或者封装,就是用来获取一个客户端的
RestHighLevelClient client = restClientManager.getClient();
//在java中，先构造一个查询，这个就是把所有的where条件塞进去
BoolQueryBuilder bool = QueryBuilders.boolQuery();
//接下来就是往这个查询对象里面加入我们的条件
//orderStatus为4
bool.must(QueryBuilders.termQuery("orderStatus", 4));
//orderType为9
bool.must(QueryBuilders.termQuery("orderType", 9));
bool.must(QueryBuilders.termsQuery("billSupplierId", Lists.newArrayList(11111,2222,33333)));
//billCreateTime在1603728000000与1603814400000(get是大于，lte是小于)
bool.filter(QueryBuilders.rangeQuery("billCreateTime").gte(1603728000000L));
bool.filter(QueryBuilders.rangeQuery("billCreateTime").lte(1603814400000L));

//创建一个查询请求，这个里面传入的order是es中的索引,相当于表名
SearchRequest searchRequest = new SearchRequest("order");
//创建一个查询构造器
SearchSourceBuilder searchSourceBuilder = new SearchSourceBuilder();
//将上面设置的条件加入到查询构造器里
searchSourceBuilder.query(bool);
//再把查询构造起器设置到searchRequest中
searchRequest.source(searchSourceBuilder);
//requestOptions,请求的验签，可以通过RequestOptions.DEFAULT.toBuilder().build()来生成，同时可以设置账号秘密等信息
SearchResponse search = client.search(searchRequest,requestOptions);
//hits里面就是我们的查询结果
SearchHits hits = search.getHits();

案例2:多条件查询，并聚合查询结果
查询表订单表order中orderStatus为4，orderType为9，billSupplierId 为(11111,2222,33333) ,billCreateTime在1603728000000与1603814400000之间的数据
并根据billSupplierId，transporterId进行分组，并统计个数

select 
billSupplierId,transporterId,count(1)
from order 
where 
orderStatus = 4 
and orderType = 9 
and billSupplierId in (11111,2222,33333)
and billCreateTime between 1603728000000 and 1603814400000
group by billSupplierId,transporterId

对应的ES查询语句

POST /order/_search
{
	"size": 0,
	"query": {
		"bool": {
			"must": [{
				"term": {
					"orderStatus": 4
				}
			}, {
				"term": {
					"orderType": {
						"value": 9
					}
				}
			}, {
				"terms": {
					"billSupplierId": [
						11111,2222,33333
					]
				}
			}],
			"filter": {
				"range": {
					"billCreateTime": {
						"gte": 1603728000000,
						"lte": 1603814400000
					}
				}
			}
		}
	},
	"aggs": {
		"sexprof": {
			"terms": {
				"script": {
					"inline": "doc['billSupplierId'].value +','+ doc['transporterId'].value "
				}
			}
		}
	}
}

对应java中的api（理论上是这样，但是我没调通，具体原因待排查）

//构建一个ES的查询客户端，restClientManager这个是每个公司自己定义或者封装,就是用来获取一个客户端的
RestHighLevelClient client = restClientManager.getClient();
//在java中，先构造一个查询，这个就是把所有的where条件塞进去
BoolQueryBuilder bool = QueryBuilders.boolQuery();
//接下来就是往这个查询对象里面加入我们的条件
//orderStatus为4
bool.must(QueryBuilders.termQuery("orderStatus", 4));
//orderType为9
bool.must(QueryBuilders.termQuery("orderType", 9));
bool.must(QueryBuilders.termsQuery("billSupplierId", Lists.newArrayList(11111,2222,33333)));
//billCreateTime在1603728000000与1603814400000(get是大于，lte是小于)
bool.filter(QueryBuilders.rangeQuery("billCreateTime").gte(1603728000000L));
bool.filter(QueryBuilders.rangeQuery("billCreateTime").lte(1603814400000L));

//构建group by条件
Script script = new Script(ScriptType.INLINE, "java", "doc['billSupplierId'].value+'-,-'+doc['transporterId'].value", new HashMap<String, Object>());
AggregationBuilder agg =AggregationBuilders.terms("sexprof").script(script);

//创建一个查询请求，这个里面传入的order是es中的索引,相当于表名
SearchRequest searchRequest = new SearchRequest("order");
//创建一个查询构造器
SearchSourceBuilder searchSourceBuilder = new SearchSourceBuilder();
//将上面设置的条件加入到查询构造器里
searchSourceBuilder.query(bool);
searchSourceBuilder.aggregation(agg);
//再把查询构造起器设置到searchRequest中
searchRequest.source(searchSourceBuilder);
//requestOptions,请求的验签，可以通过RequestOptions.DEFAULT.toBuilder().build()来生成，同时可以设置账号秘密等信息
SearchResponse search = client.search(searchRequest,requestOptions);
//hits里面就是我们的查询结果
SearchHits hits = search.getHits();

根据站点billSupplierId分组，然后看这个站点下有多少个不重复的transporterId
对应的sql

select billSupplierId,count(distinct(transporterId)) from order group by billSupplierId

ES查询语法

POST /order/_search
{
  "size": 0, 
  "aggs": {
	  "field1": {
	    "terms": {
	      "field": "billSupplierId",
	      "size": 1024
	    },
	    "aggs": {
	      "field2": {
	        "terms": {
	          "field": "transporterId",
	          "size": 1
	        }
	      }
	    }
	  }
	}
}

对应的java代码

//bill_supplier_id这个只是给取一个别名，类似上面的fields，用于后续获取结果用
AggregationBuilder agg = AggregationBuilders.terms("SUPPLIER_ID").field("billSupplierId").size(Integer.MAX_VALUE)
              .subAggregation(AggregationBuilders.cardinality("TRANSPORTER_ID").field("transporterId"));
//创建一个查询请求，这个里面传入的order是es中的索引,相当于表名
SearchRequest searchRequest = new SearchRequest("order");
//创建一个查询构造器
SearchSourceBuilder searchSourceBuilder = new SearchSourceBuilder();
searchSourceBuilder.aggregation(agg);
//再把查询构造起器设置到searchRequest中
searchRequest.source(searchSourceBuilder);
//requestOptions,请求的验签，可以通过RequestOptions.DEFAULT.toBuilder().build()来生成，同时可以设置账号秘密等信息
SearchResponse result = client.search(searchRequest,requestOptions);
Aggregations aggregations = result.getAggregations();
 Map<String, Aggregation> stringAggregationMap = aggregations.asMap();
        List<? extends Terms.Bucket> buckets = ((ParsedTerms) stringAggregationMap.get("SUPPLIER_ID")).getBuckets();

HashMap<Integer,Integer > map = new HashMap<>();
aggregation.getBuckets().forEach(bucket->{
map.put(Integer.valueOf(bucket.getKeyAsString()),
                    (int) ((ParsedCardinality) (bucket.getAggregations()).getAsMap().get("TRANSPORTER_ID")).getValue());
 });

啥也不是，记录一下