public void run() {
Client client = null;
TransportClient transportClient = null;
try {
transportClient = new TransportClient();
client =
transportClient.addTransportAddress(new InetSocketTransportAddress("192.168.1.40", 9300));
SearchResponse response =
client
.prepareSearch("tms-allflat")
.setTypes("personal")
.setQuery(QueryBuilders.matchAllQuery())
.addAggregation(
AggregationBuilders.terms("aggs1")
.field("skill_1")
.size(20)
.order(Terms.Order.count(false)))
.execute()
.actionGet();
Terms terms = response.getAggregations().get("aggs1");
terms
.getBuckets()
.stream()
.forEach(s -> System.out.println(s.getKeyAsText() + "(" + s.getDocCount() + ")"));
} finally {
transportClient.close();
client.close();
}
}
@Test
public void singleValuedField_OrderedBySingleValueSubAggregationAsc() throws Exception {
boolean asc = true;
SearchResponse response =
client()
.prepareSearch("idx")
.setTypes("type")
.addAggregation(
terms("terms")
.executionHint(randomExecutionHint())
.field(SINGLE_VALUED_FIELD_NAME)
.order(Terms.Order.aggregation("avg_i", asc))
.subAggregation(avg("avg_i").field("i")))
.execute()
.actionGet();
assertSearchResponse(response);
Terms terms = response.getAggregations().get("terms");
assertThat(terms, notNullValue());
assertThat(terms.getName(), equalTo("terms"));
assertThat(terms.getBuckets().size(), equalTo(5));
int i = 0;
for (Terms.Bucket bucket : terms.getBuckets()) {
assertThat(bucket, notNullValue());
assertThat(key(bucket), equalTo("val" + i));
assertThat(bucket.getDocCount(), equalTo(1l));
Avg avg = bucket.getAggregations().get("avg_i");
assertThat(avg, notNullValue());
assertThat(avg.getValue(), equalTo((double) i));
i++;
}
}
@Test
public void singleValueField_WithMaxSize() throws Exception {
SearchResponse response =
client()
.prepareSearch("idx")
.setTypes("high_card_type")
.addAggregation(
terms("terms")
.executionHint(randomExecutionHint())
.field(SINGLE_VALUED_FIELD_NAME)
.size(20)
.order(
Terms.Order.term(
true))) // we need to sort by terms cause we're checking the first 20
// values
.execute()
.actionGet();
assertSearchResponse(response);
Terms terms = response.getAggregations().get("terms");
assertThat(terms, notNullValue());
assertThat(terms.getName(), equalTo("terms"));
assertThat(terms.getBuckets().size(), equalTo(20));
for (int i = 0; i < 20; i++) {
Terms.Bucket bucket = terms.getBucketByKey("val" + Strings.padStart(i + "", 3, '0'));
assertThat(bucket, notNullValue());
assertThat(key(bucket), equalTo("val" + Strings.padStart(i + "", 3, '0')));
assertThat(bucket.getDocCount(), equalTo(1l));
}
}
@Test
public void singleValueField_OrderedByTermDesc() throws Exception {
SearchResponse response =
client()
.prepareSearch("idx")
.setTypes("type")
.addAggregation(
terms("terms")
.executionHint(randomExecutionHint())
.field(SINGLE_VALUED_FIELD_NAME)
.order(Terms.Order.term(false)))
.execute()
.actionGet();
assertSearchResponse(response);
Terms terms = response.getAggregations().get("terms");
assertThat(terms, notNullValue());
assertThat(terms.getName(), equalTo("terms"));
assertThat(terms.getBuckets().size(), equalTo(5));
int i = 4;
for (Terms.Bucket bucket : terms.getBuckets()) {
assertThat(bucket, notNullValue());
assertThat(key(bucket), equalTo("val" + i));
assertThat(bucket.getDocCount(), equalTo(1l));
i--;
}
}
@Test
public void singleValuedField_OrderedByNonMetricsOrMultiBucketSubAggregation() throws Exception {
MockBigArrays.discardNextCheck();
try {
client()
.prepareSearch("idx")
.setTypes("type")
.addAggregation(
terms("terms")
.executionHint(randomExecutionHint())
.field(SINGLE_VALUED_FIELD_NAME)
.order(Terms.Order.aggregation("values", true))
.subAggregation(terms("values").field("i")))
.execute()
.actionGet();
fail(
"Expected search to fail when trying to sort terms aggregation by sug-aggregation which is not of a metrics or single-bucket type");
} catch (ElasticsearchException e) {
// expected
}
}
@Test
public void singleValuedField_OrderedByMultiValuedSubAggregation_WithoutMetric()
throws Exception {
MockBigArrays.discardNextCheck();
try {
client()
.prepareSearch("idx")
.setTypes("type")
.addAggregation(
terms("terms")
.executionHint(randomExecutionHint())
.field(SINGLE_VALUED_FIELD_NAME)
.order(Terms.Order.aggregation("stats", true))
.subAggregation(stats("stats").field("i")))
.execute()
.actionGet();
fail(
"Expected search to fail when trying to sort terms aggregation by multi-valued sug-aggregation "
+ "where the metric name is not specified");
} catch (ElasticsearchException e) {
// expected
}
}
public Map<String, Long> countByField(IndexField indexField, FilterBuilder filter) {
Map<String, Long> counts = new HashMap<>();
SearchRequestBuilder request =
client
.prepareSearch(this.getIndexName())
.setTypes(this.getIndexType())
.setQuery(QueryBuilders.filteredQuery(QueryBuilders.matchAllQuery(), filter))
.setSize(0)
.addAggregation(
AggregationBuilders.terms(indexField.field())
.field(indexField.field())
.order(Terms.Order.count(false))
.size(Integer.MAX_VALUE)
.minDocCount(0));
SearchResponse response = request.get();
Terms values = response.getAggregations().get(indexField.field());
for (Terms.Bucket value : values.getBuckets()) {
counts.put(value.getKey(), value.getDocCount());
}
return counts;
}
@Override
public void testOrderByEmptyAggregation() throws Exception {
SearchResponse searchResponse =
client()
.prepareSearch("idx")
.setQuery(matchAllQuery())
.addAggregation(
terms("terms")
.field("value")
.order(
Terms.Order.compound(
Terms.Order.aggregation("filter>percentiles.99", true)))
.subAggregation(
filter("filter", termQuery("value", 100))
.subAggregation(
percentiles("percentiles")
.method(PercentilesMethod.TDIGEST)
.field("value"))))
.get();
assertHitCount(searchResponse, 10);
Terms terms = searchResponse.getAggregations().get("terms");
assertThat(terms, notNullValue());
List<Terms.Bucket> buckets = terms.getBuckets();
assertThat(buckets, notNullValue());
assertThat(buckets.size(), equalTo(10));
for (int i = 0; i < 10; i++) {
Terms.Bucket bucket = buckets.get(i);
assertThat(bucket, notNullValue());
assertThat(bucket.getKeyAsNumber(), equalTo((long) i + 1));
assertThat(bucket.getDocCount(), equalTo(1L));
Filter filter = bucket.getAggregations().get("filter");
assertThat(filter, notNullValue());
assertThat(filter.getDocCount(), equalTo(0L));
Percentiles percentiles = filter.getAggregations().get("percentiles");
assertThat(percentiles, notNullValue());
assertThat(percentiles.percentile(99), equalTo(Double.NaN));
}
}
@Test
public void singleValuedField_OrderedBySingleBucketSubAggregationAsc() throws Exception {
boolean asc = randomBoolean();
SearchResponse response =
client()
.prepareSearch("idx")
.setTypes("type")
.addAggregation(
terms("tags")
.executionHint(randomExecutionHint())
.field("tag")
.order(Terms.Order.aggregation("filter", asc))
.subAggregation(filter("filter").filter(FilterBuilders.matchAllFilter())))
.execute()
.actionGet();
assertSearchResponse(response);
Terms tags = response.getAggregations().get("tags");
assertThat(tags, notNullValue());
assertThat(tags.getName(), equalTo("tags"));
assertThat(tags.getBuckets().size(), equalTo(2));
Iterator<Terms.Bucket> iters = tags.getBuckets().iterator();
Terms.Bucket tag = iters.next();
assertThat(tag, notNullValue());
assertThat(key(tag), equalTo(asc ? "less" : "more"));
assertThat(tag.getDocCount(), equalTo(asc ? 2l : 3l));
Filter filter = tag.getAggregations().get("filter");
assertThat(filter, notNullValue());
assertThat(filter.getDocCount(), equalTo(asc ? 2l : 3l));
tag = iters.next();
assertThat(tag, notNullValue());
assertThat(key(tag), equalTo(asc ? "more" : "less"));
assertThat(tag.getDocCount(), equalTo(asc ? 3l : 2l));
filter = tag.getAggregations().get("filter");
assertThat(filter, notNullValue());
assertThat(filter.getDocCount(), equalTo(asc ? 3l : 2l));
}
@Test
public void
givenNotAnalyzedQuery_whenMakeAggregationOnTermCount_thenEachTermCountsIndividually() {
TermsBuilder aggregation =
AggregationBuilders.terms("top_tags")
.field("tags")
.order(Terms.Order.aggregation("_count", false));
SearchResponse response =
client
.prepareSearch("blog")
.setTypes("article")
.addAggregation(aggregation)
.execute()
.actionGet();
Map<String, Aggregation> results = response.getAggregations().asMap();
StringTerms topTags = (StringTerms) results.get("top_tags");
List<String> keys = topTags.getBuckets().stream().map(b -> b.getKey()).collect(toList());
assertEquals(asList("elasticsearch", "spring data", "search engines", "tutorial"), keys);
}
@Override
public void execute(Client esClient, String index, boolean verbose) {
FilterBuilder fb =
new OrFilterBuilder(
new TermFilterBuilder("eventName", "TopBrowser"),
new TermFilterBuilder("data.playerEvent", "str-start"));
AbstractAggregationBuilder aggregations =
AggregationBuilders.terms("by_browser")
.field("data.browser")
.size(50)
.subAggregation(AggregationBuilders.sum("sum").field("counter"))
.order(Terms.Order.aggregation("sum", false));
if (index.equals("")) {
this.searchResponse =
esClient
.prepareSearch()
.setQuery(
org.elasticsearch.index.query.QueryBuilders.filteredQuery(
new MatchAllQueryBuilder(), fb))
.setSize(50)
.setPostFilter(fb)
.addAggregation(aggregations)
.get();
} else {
this.searchResponse =
esClient
.prepareSearch(index)
.setQuery(
org.elasticsearch.index.query.QueryBuilders.filteredQuery(
new MatchAllQueryBuilder(), fb))
.setSize(50)
.setPostFilter(fb)
.addAggregation(aggregations)
.get();
}
fileLog.info(String.valueOf(this.searchResponse.getTookInMillis()));
if (verbose) printResult();
}
@Test
public void singleValuedField_OrderedByMissingSubAggregation() throws Exception {
MockBigArrays.discardNextCheck();
try {
client()
.prepareSearch("idx")
.setTypes("type")
.addAggregation(
terms("terms")
.executionHint(randomExecutionHint())
.field(SINGLE_VALUED_FIELD_NAME)
.order(Terms.Order.aggregation("avg_i", true)))
.execute()
.actionGet();
fail(
"Expected search to fail when trying to sort terms aggregation by sug-aggregation that doesn't exist");
} catch (ElasticsearchException e) {
// expected
}
}
@Test
public void singleValuedField_OrderedBySubAggregationAsc_MultiHierarchyLevels() throws Exception {
boolean asc = randomBoolean();
SearchResponse response =
client()
.prepareSearch("idx")
.setTypes("type")
.addAggregation(
terms("tags")
.executionHint(randomExecutionHint())
.field("tag")
.order(Terms.Order.aggregation("filter1>filter2>stats.max", asc))
.subAggregation(
filter("filter1")
.filter(FilterBuilders.matchAllFilter())
.subAggregation(
filter("filter2")
.filter(FilterBuilders.matchAllFilter())
.subAggregation(stats("stats").field("i")))))
.execute()
.actionGet();
assertSearchResponse(response);
Terms tags = response.getAggregations().get("tags");
assertThat(tags, notNullValue());
assertThat(tags.getName(), equalTo("tags"));
assertThat(tags.getBuckets().size(), equalTo(2));
Iterator<Terms.Bucket> iters = tags.getBuckets().iterator();
// the max for "more" is 2
// the max for "less" is 4
Terms.Bucket tag = iters.next();
assertThat(tag, notNullValue());
assertThat(key(tag), equalTo(asc ? "more" : "less"));
assertThat(tag.getDocCount(), equalTo(asc ? 3l : 2l));
Filter filter1 = tag.getAggregations().get("filter1");
assertThat(filter1, notNullValue());
assertThat(filter1.getDocCount(), equalTo(asc ? 3l : 2l));
Filter filter2 = filter1.getAggregations().get("filter2");
assertThat(filter2, notNullValue());
assertThat(filter2.getDocCount(), equalTo(asc ? 3l : 2l));
Stats stats = filter2.getAggregations().get("stats");
assertThat(stats, notNullValue());
assertThat(stats.getMax(), equalTo(asc ? 2.0 : 4.0));
tag = iters.next();
assertThat(tag, notNullValue());
assertThat(key(tag), equalTo(asc ? "less" : "more"));
assertThat(tag.getDocCount(), equalTo(asc ? 2l : 3l));
filter1 = tag.getAggregations().get("filter1");
assertThat(filter1, notNullValue());
assertThat(filter1.getDocCount(), equalTo(asc ? 2l : 3l));
filter2 = filter1.getAggregations().get("filter2");
assertThat(filter2, notNullValue());
assertThat(filter2.getDocCount(), equalTo(asc ? 2l : 3l));
stats = filter2.getAggregations().get("stats");
assertThat(stats, notNullValue());
assertThat(stats.getMax(), equalTo(asc ? 4.0 : 2.0));
}
@Override
public SqlElasticSearchRequestBuilder explain() throws SqlParseException {
this.request = client.prepareSearch();
request.setListenerThreaded(false);
setIndicesAndTypes();
setWhere(select.getWhere());
AggregationBuilder<?> lastAgg = null;
for (List<Field> groupBy : select.getGroupBys()) {
if (!groupBy.isEmpty()) {
Field field = groupBy.get(0);
lastAgg = aggMaker.makeGroupAgg(field);
if (lastAgg != null && lastAgg instanceof TermsBuilder && !(field instanceof MethodField)) {
((TermsBuilder) lastAgg).size(select.getRowCount());
}
if (field.isNested()) {
AggregationBuilder nestedBuilder = createNestedAggregation(field);
if (insertFilterIfExistsAfter(lastAgg, groupBy, nestedBuilder, 1)) {
groupBy.remove(1);
} else {
nestedBuilder.subAggregation(lastAgg);
}
request.addAggregation(wrapNestedIfNeeded(nestedBuilder, field.isReverseNested()));
} else {
request.addAggregation(lastAgg);
}
for (int i = 1; i < groupBy.size(); i++) {
field = groupBy.get(i);
AggregationBuilder<?> subAgg = aggMaker.makeGroupAgg(field);
if (subAgg instanceof TermsBuilder && !(field instanceof MethodField)) {
((TermsBuilder) subAgg).size(0);
}
if (field.isNested()) {
AggregationBuilder nestedBuilder = createNestedAggregation(field);
if (insertFilterIfExistsAfter(subAgg, groupBy, nestedBuilder, i + 1)) {
groupBy.remove(i + 1);
i++;
} else {
nestedBuilder.subAggregation(subAgg);
}
lastAgg.subAggregation(wrapNestedIfNeeded(nestedBuilder, field.isReverseNested()));
} else {
lastAgg.subAggregation(subAgg);
}
lastAgg = subAgg;
}
}
}
Map<String, KVValue> groupMap = aggMaker.getGroupMap();
// add field
if (select.getFields().size() > 0) {
setFields(select.getFields());
explanFields(request, select.getFields(), lastAgg);
}
// add order
if (lastAgg != null && select.getOrderBys().size() > 0) {
for (Order order : select.getOrderBys()) {
KVValue temp = groupMap.get(order.getName());
if (temp != null) {
TermsBuilder termsBuilder = (TermsBuilder) temp.value;
switch (temp.key) {
case "COUNT":
termsBuilder.order(Terms.Order.count(isASC(order)));
break;
case "KEY":
termsBuilder.order(Terms.Order.term(isASC(order)));
// add the sort to the request also so the results get sorted as well
request.addSort(order.getName(), SortOrder.valueOf(order.getType()));
break;
case "FIELD":
termsBuilder.order(Terms.Order.aggregation(order.getName(), isASC(order)));
break;
default:
throw new SqlParseException(order.getName() + " can not to order");
}
} else {
request.addSort(order.getName(), SortOrder.valueOf(order.getType()));
}
}
}
setLimitFromHint(this.select.getHints());
request.setSearchType(SearchType.DEFAULT);
updateRequestWithIndexAndRoutingOptions(select, request);
SqlElasticSearchRequestBuilder sqlElasticRequestBuilder =
new SqlElasticSearchRequestBuilder(request);
return sqlElasticRequestBuilder;
}