@Test
public void testSimpleSubAggregations() throws Exception {
final String query =
String.format(
"SELECT /*! DOCS_WITH_AGGREGATION(10) */ * FROM %s/account GROUP BY (gender), (state) ",
TEST_INDEX);
SqlElasticSearchRequestBuilder select = getSearchRequestBuilder(query);
SearchResponse response = (SearchResponse) select.get();
Aggregations result = response.getAggregations();
Terms gender = result.get("gender");
for (Terms.Bucket genderBucket : gender.getBuckets()) {
String genderKey = genderBucket.getKey();
Assert.assertTrue("Gender should be m or f", genderKey.equals("m") || genderKey.equals("f"));
}
Assert.assertEquals(2, gender.getBuckets().size());
Terms state = result.get("state");
for (Terms.Bucket stateBucket : state.getBuckets()) {
if (stateBucket.getKey().equalsIgnoreCase("ak")) {
Assert.assertTrue("There are 22 entries for state ak", stateBucket.getDocCount() == 22);
}
}
Assert.assertEquals(response.getHits().totalHits(), 1000);
Assert.assertEquals(response.getHits().hits().length, 10);
}
public SearchResult(SearchResponse resp, Select select) throws SqlParseException {
Aggregations aggs = resp.getAggregations();
if (aggs.get("filter") != null) {
InternalFilter inf = aggs.get("filter");
aggs = inf.getAggregations();
}
if (aggs.get("group by") != null) {
InternalTerms terms = aggs.get("group by");
Collection<Bucket> buckets = terms.getBuckets();
this.total = buckets.size();
results = new ArrayList<>(buckets.size());
for (Bucket bucket : buckets) {
Map<String, Object> aggsMap = toAggsMap(bucket.getAggregations().getAsMap());
aggsMap.put("docCount", bucket.getDocCount());
results.add(aggsMap);
}
} else {
results = new ArrayList<>(1);
this.total = 1;
Map<String, Object> map = new HashMap<>();
for (Aggregation aggregation : aggs) {
map.put(aggregation.getName(), covenValue(aggregation));
}
results.add(map);
}
}
@Test
public void reverseAnotherNestedGroupByOnNestedFieldWithFilterTestWithReverseNestedNoPath()
throws Exception {
Aggregations result =
query(
String.format(
"SELECT COUNT(*) FROM %s/nestedType GROUP BY nested(message.info),filter('myFilter',message.info = 'a'),reverse_nested(comment.data,'~comment')",
TEST_INDEX));
InternalNested nested = result.get("message.info@NESTED");
InternalFilter filter = nested.getAggregations().get("myFilter@FILTER");
Terms infos = filter.getAggregations().get("message.info");
Assert.assertEquals(1, infos.getBuckets().size());
for (Terms.Bucket bucket : infos.getBuckets()) {
InternalReverseNested reverseNested =
bucket.getAggregations().get("comment.data@NESTED_REVERSED");
InternalNested innerNested = reverseNested.getAggregations().get("comment.data@NESTED");
Terms terms = innerNested.getAggregations().get("comment.data");
Terms.Bucket internalBucket = terms.getBuckets().get(0);
long count = ((ValueCount) internalBucket.getAggregations().get("COUNT(*)")).getValue();
String key = internalBucket.getKey();
if (key.equalsIgnoreCase("ab")) {
Assert.assertEquals(2, count);
} else {
throw new Exception(String.format("Unexpected key. expected: only a . found: %s", key));
}
}
}
@Test
public void percentileTestSpecific()
throws IOException, SqlParseException, SQLFeatureNotSupportedException {
Aggregations result =
query(String.format("SELECT PERCENTILES(age,25.0,75.0) x FROM %s/account", TEST_INDEX));
Percentiles percentiles = result.get("x");
Assert.assertTrue(Math.abs(percentiles.percentile(25.0) - 25.0) < 0.001);
Assert.assertTrue(Math.abs(percentiles.percentile(75.0) - 35.0) < 0.001);
}
@Test
public void minOnNestedField() throws Exception {
Aggregations result =
query(
String.format(
"SELECT min(nested(message.dayOfWeek)) as minDays FROM %s/nestedType", TEST_INDEX));
InternalNested nested = result.get("message.dayOfWeek@NESTED");
Min mins = nested.getAggregations().get("minDays");
Assert.assertEquals(1.0, mins.getValue(), 0.0001);
}
@Test
public void sumOnNestedField() throws Exception {
Aggregations result =
query(
String.format(
"SELECT sum(nested(message.dayOfWeek)) as sumDays FROM %s/nestedType", TEST_INDEX));
InternalNested nested = result.get("message.dayOfWeek@NESTED");
Sum sum = nested.getAggregations().get("sumDays");
Assert.assertEquals(13.0, sum.getValue(), 0.0001);
}
@Test
public void statsTest() throws IOException, SqlParseException, SQLFeatureNotSupportedException {
Aggregations result = query(String.format("SELECT STATS(age) FROM %s/account", TEST_INDEX));
Stats stats = result.get("STATS(age)");
Assert.assertEquals(1000, stats.getCount());
assertThat(stats.getSum(), equalTo(30171.0));
assertThat(stats.getMin(), equalTo(20.0));
assertThat(stats.getMax(), equalTo(40.0));
assertThat(stats.getAvg(), equalTo(30.171));
}
@Test
public void limitTest() throws IOException, SqlParseException, SQLFeatureNotSupportedException {
Aggregations result =
query(
String.format(
"SELECT COUNT(*) FROM %s/account GROUP BY age ORDER BY COUNT(*) LIMIT 5",
TEST_INDEX));
Terms age = result.get("age");
assertThat(age.getBuckets().size(), equalTo(5));
}
@Test
public void termsWithSize() throws Exception {
Map<String, Set<Integer>> buckets = new HashMap<>();
Aggregations result =
query(
String.format(
"SELECT COUNT(*) FROM %s/account GROUP BY terms('alias'='ageAgg','field'='age','size'=3)",
TEST_INDEX));
Terms gender = result.get("ageAgg");
Assert.assertEquals(3, gender.getBuckets().size());
}
@Test
public void testSubAggregations() throws Exception {
Set expectedAges =
new HashSet<>(ContiguousSet.create(Range.closed(20, 40), DiscreteDomain.integers()));
final String query =
String.format(
"SELECT /*! DOCS_WITH_AGGREGATION(10) */"
+ " * FROM %s/account GROUP BY (gender, age), (state) LIMIT 0,10",
TEST_INDEX);
Map<String, Set<Integer>> buckets = new HashMap<>();
SqlElasticSearchRequestBuilder select = getSearchRequestBuilder(query);
SearchResponse response = (SearchResponse) select.get();
Aggregations result = response.getAggregations();
Terms gender = result.get("gender");
for (Terms.Bucket genderBucket : gender.getBuckets()) {
String genderKey = genderBucket.getKey();
buckets.put(genderKey, new HashSet<Integer>());
Terms ageBuckets = (Terms) genderBucket.getAggregations().get("age");
for (Terms.Bucket ageBucket : ageBuckets.getBuckets()) {
buckets.get(genderKey).add(Integer.parseInt(ageBucket.getKey()));
}
}
Assert.assertEquals(2, buckets.keySet().size());
Assert.assertEquals(expectedAges, buckets.get("m"));
Assert.assertEquals(expectedAges, buckets.get("f"));
Terms state = result.get("state");
for (Terms.Bucket stateBucket : state.getBuckets()) {
if (stateBucket.getKey().equalsIgnoreCase("ak")) {
Assert.assertTrue("There are 22 entries for state ak", stateBucket.getDocCount() == 22);
}
}
Assert.assertEquals(response.getHits().totalHits(), 1000);
Assert.assertEquals(response.getHits().hits().length, 10);
}
@Test
public void geoBounds() throws SQLFeatureNotSupportedException, SqlParseException {
Aggregations result =
query(
String.format(
"SELECT * FROM %s/location GROUP BY geo_bounds(field='center',alias='bounds') ",
TEST_INDEX));
InternalGeoBounds bounds = result.get("bounds");
Assert.assertEquals(0.5, bounds.bottomRight().getLat(), 0.001);
Assert.assertEquals(105.0, bounds.bottomRight().getLon(), 0.001);
Assert.assertEquals(5.0, bounds.topLeft().getLat(), 0.001);
Assert.assertEquals(100.5, bounds.topLeft().getLon(), 0.001);
}
@Test
public void geoHashGrid() throws SQLFeatureNotSupportedException, SqlParseException {
Aggregations result =
query(
String.format(
"SELECT COUNT(*) FROM %s/location GROUP BY geohash_grid(field='center',precision=5) ",
TEST_INDEX));
InternalGeoHashGrid grid = result.get("geohash_grid(field=center,precision=5)");
Collection<GeoHashGrid.Bucket> buckets = grid.getBuckets();
for (GeoHashGrid.Bucket bucket : buckets) {
Assert.assertTrue(bucket.getKey().equals("w2fsm") || bucket.getKey().equals("w0p6y"));
Assert.assertEquals(1, bucket.getDocCount());
}
}
@Test
public void extendedStatsTest()
throws IOException, SqlParseException, SQLFeatureNotSupportedException {
Aggregations result =
query(String.format("SELECT EXTENDED_STATS(age) FROM %s/account", TEST_INDEX));
ExtendedStats stats = result.get("EXTENDED_STATS(age)");
Assert.assertEquals(1000, stats.getCount());
assertThat(stats.getMin(), equalTo(20.0));
assertThat(stats.getMax(), equalTo(40.0));
assertThat(stats.getAvg(), equalTo(30.171));
assertThat(stats.getSum(), equalTo(30171.0));
assertThat(stats.getSumOfSquares(), equalTo(946393.0));
Assert.assertTrue(Math.abs(stats.getStdDeviation() - 6.008640362012022) < 0.0001);
Assert.assertTrue(Math.abs(stats.getVariance() - 36.10375899999996) < 0.0001);
}
@Test
public void groupByTest() throws Exception {
Aggregations result =
query(String.format("SELECT COUNT(*) FROM %s/account GROUP BY gender", TEST_INDEX));
Terms gender = result.get("gender");
for (Terms.Bucket bucket : gender.getBuckets()) {
String key = bucket.getKey();
long count = ((ValueCount) bucket.getAggregations().get("COUNT(*)")).getValue();
if (key.equalsIgnoreCase("m")) {
Assert.assertEquals(507, count);
} else if (key.equalsIgnoreCase("f")) {
Assert.assertEquals(493, count);
} else {
throw new Exception(String.format("Unexpected key. expected: m OR f. found: %s", key));
}
}
}
@Test
public void reverseToRootGroupByOnNestedFieldWithFilterAndSumOnReverseNestedField()
throws Exception {
Aggregations result =
query(
String.format(
"SELECT sum(reverse_nested(myNum)) bla FROM %s/nestedType GROUP BY nested(message.info),filter('myFilter',message.info = 'a')",
TEST_INDEX));
InternalNested nested = result.get("message.info@NESTED");
InternalFilter filter = nested.getAggregations().get("myFilter@FILTER");
Terms infos = filter.getAggregations().get("message.info");
Assert.assertEquals(1, infos.getBuckets().size());
for (Terms.Bucket bucket : infos.getBuckets()) {
InternalReverseNested reverseNested = bucket.getAggregations().get("myNum@NESTED");
InternalSum sum = reverseNested.getAggregations().get("bla");
Assert.assertEquals(5.0, sum.getValue(), 0.000001);
}
}
@Test
public void reverseToRootGroupByOnNestedFieldWithFilterTestWithReverseNestedOnHistogram()
throws Exception {
Aggregations result =
query(
String.format(
"SELECT COUNT(*) FROM %s/nestedType GROUP BY nested(message.info),filter('myFilter',message.info = 'a'),histogram('field'='myNum','reverse_nested'='','interval'='2' , 'alias' = 'someAlias' )",
TEST_INDEX));
InternalNested nested = result.get("message.info@NESTED");
InternalFilter filter = nested.getAggregations().get("myFilter@FILTER");
Terms infos = filter.getAggregations().get("message.info");
Assert.assertEquals(1, infos.getBuckets().size());
for (Terms.Bucket bucket : infos.getBuckets()) {
InternalReverseNested reverseNested = bucket.getAggregations().get("someAlias@NESTED");
InternalHistogram histogram = reverseNested.getAggregations().get("someAlias");
Assert.assertEquals(2, histogram.getBuckets().size());
}
}
@Test
public void orderByDescTest()
throws IOException, SqlParseException, SQLFeatureNotSupportedException {
ArrayList<Long> agesCount = new ArrayList<>();
Aggregations result =
query(
String.format(
"SELECT COUNT(*) FROM %s/account GROUP BY age ORDER BY COUNT(*) DESC", TEST_INDEX));
Terms age = result.get("age");
for (Terms.Bucket bucket : age.getBuckets()) {
agesCount.add(((ValueCount) bucket.getAggregations().get("COUNT(*)")).getValue());
}
ArrayList<Long> sortedAgesCount = (ArrayList<Long>) agesCount.clone();
Collections.sort(sortedAgesCount, Collections.reverseOrder());
Assert.assertTrue("The list is not ordered descending", agesCount.equals(agesCount));
}
@Test
public void histogramOnNestedField() throws Exception {
Aggregations result =
query(
String.format(
"select count(*) from %s/nestedType group by histogram('field'='message.dayOfWeek','nested'='message','interval'='2' , 'alias' = 'someAlias' )",
TEST_INDEX));
InternalNested nested = result.get("message@NESTED");
Histogram histogram = nested.getAggregations().get("someAlias");
for (Histogram.Bucket bucket : histogram.getBuckets()) {
long count = ((ValueCount) bucket.getAggregations().get("COUNT(*)")).getValue();
if (bucket.getKey().equals("0") || bucket.getKey().equals("4")) {
Assert.assertEquals(2, count);
} else if (bucket.getKey().equals("2")) {
Assert.assertEquals(1, count);
} else {
Assert.assertTrue("only 0 2 4 keys are allowed got:" + bucket.getKey(), false);
}
}
}
@Test
public void groupByOnNestedFieldWithFilterTest() throws Exception {
Aggregations result =
query(
String.format(
"SELECT COUNT(*) FROM %s/nestedType GROUP BY nested(message.info),filter('myFilter',message.info = 'a')",
TEST_INDEX));
InternalNested nested = result.get("message.info@NESTED");
InternalFilter filter = nested.getAggregations().get("myFilter@FILTER");
Terms infos = filter.getAggregations().get("message.info");
Assert.assertEquals(1, infos.getBuckets().size());
for (Terms.Bucket bucket : infos.getBuckets()) {
String key = bucket.getKey();
long count = ((ValueCount) bucket.getAggregations().get("COUNT(*)")).getValue();
if (key.equalsIgnoreCase("a")) {
Assert.assertEquals(2, count);
} else {
throw new Exception(String.format("Unexpected key. expected: only a . found: %s", key));
}
}
}
@Test
public void countGroupByRange()
throws IOException, SqlParseException, SQLFeatureNotSupportedException {
Aggregations result =
query(
String.format(
"SELECT COUNT(age) FROM %s/account GROUP BY range(age, 20,25,30,35,40) ",
TEST_INDEX));
org.elasticsearch.search.aggregations.bucket.range.Range ageRanges =
result.get("range(age,20,25,30,35,40)");
assertThat(ageRanges.getBuckets().size(), equalTo(4));
long[] expectedResults = new long[] {225L, 226L, 259L, 245L};
int index = 0;
for (Bucket bucket : ageRanges.getBuckets()) {
assertThat(
((ValueCount) bucket.getAggregations().get("COUNT(age)")).getValue(),
equalTo(expectedResults[index]));
index++;
}
}
@Test
public void multipleGroupBysWithSize() throws Exception {
Set expectedAges =
new HashSet<Integer>(ContiguousSet.create(Range.closed(20, 40), DiscreteDomain.integers()));
Map<String, Set<Integer>> buckets = new HashMap<>();
Aggregations result =
query(
String.format(
"SELECT COUNT(*) FROM %s/account GROUP BY gender, terms('alias'='ageAgg','field'='age','size'=3)",
TEST_INDEX));
Terms gender = result.get("gender");
Assert.assertEquals(2, gender.getBuckets().size());
for (Terms.Bucket genderBucket : gender.getBuckets()) {
String genderKey = genderBucket.getKey();
buckets.put(genderKey, new HashSet<Integer>());
Terms ageBuckets = genderBucket.getAggregations().get("ageAgg");
Assert.assertEquals(3, ageBuckets.getBuckets().size());
}
}
@Test
public void multipleGroupByTest() throws Exception {
Set expectedAges =
new HashSet<Integer>(ContiguousSet.create(Range.closed(20, 40), DiscreteDomain.integers()));
Map<String, Set<Integer>> buckets = new HashMap<>();
Aggregations result =
query(String.format("SELECT COUNT(*) FROM %s/account GROUP BY gender, age", TEST_INDEX));
Terms gender = result.get("gender");
for (Terms.Bucket genderBucket : gender.getBuckets()) {
String genderKey = genderBucket.getKey();
buckets.put(genderKey, new HashSet<Integer>());
Terms ageBuckets = (Terms) genderBucket.getAggregations().get("age");
for (Terms.Bucket ageBucket : ageBuckets.getBuckets()) {
buckets.get(genderKey).add(Integer.parseInt(ageBucket.getKey()));
}
}
Assert.assertEquals(2, buckets.keySet().size());
Assert.assertEquals(expectedAges, buckets.get("m"));
Assert.assertEquals(expectedAges, buckets.get("f"));
}
@Test
public void maxTest() throws IOException, SqlParseException, SQLFeatureNotSupportedException {
Aggregations result = query(String.format("SELECT MAX(age) FROM %s/account", TEST_INDEX));
Max max = result.get("MAX(age)");
assertThat(max.getValue(), equalTo(40.0));
}
@Test
public void sumTest() throws IOException, SqlParseException, SQLFeatureNotSupportedException {
Aggregations result = query(String.format("SELECT SUM(balance) FROM %s/account", TEST_INDEX));
Sum sum = result.get("SUM(balance)");
assertThat(sum.getValue(), equalTo(25714837.0));
}
@Test
public void avgTest() throws IOException, SqlParseException, SQLFeatureNotSupportedException {
Aggregations result = query(String.format("SELECT AVG(age) FROM %s/account", TEST_INDEX));
Avg avg = result.get("AVG(age)");
assertThat(avg.getValue(), equalTo(30.171));
}
@Test
public void countTest() throws IOException, SqlParseException, SQLFeatureNotSupportedException {
Aggregations result = query(String.format("SELECT COUNT(*) FROM %s/account", TEST_INDEX));
ValueCount count = result.get("COUNT(*)");
Assert.assertEquals(1000, count.getValue());
}