Skip to content

Commit 502c012

Browse files
eliranmoyaleliranmoyal
committed
nested types - more aggregations NLPchina#118 
1 parent 0eda1e2 commit 502c012

File tree

7 files changed

+115
-36
lines changed

7 files changed

+115
-36
lines changed

‎src/main/java/org/nlpcn/es4sql/domain/MethodField.java

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -53,4 +53,14 @@ public void setOption(String option) {
5353
this.option = option;
5454
}
5555

56+
@Override
57+
public boolean isNested() {
58+
return this.getParamsAsMap().containsKey("nested");
59+
}
60+
61+
@Override
62+
public String getNestedPath() {
63+
if(!this.isNested()) return null;
64+
return this.getParamsAsMap().get("nested").toString();
65+
}
5666
}

‎src/main/java/org/nlpcn/es4sql/parse/FieldMaker.java

Lines changed: 10 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -146,11 +146,19 @@ private static MethodField makeMethodField(String name, List<SQLExpr> arguments,
146146
}
147147
} else if(object instanceof SQLMethodInvokeExpr) {
148148
SQLMethodInvokeExpr mExpr = (SQLMethodInvokeExpr) object;
149-
if(mExpr.getMethodName().toLowerCase().equals("script")){
149+
String methodName = mExpr.getMethodName().toLowerCase();
150+
if(methodName.equals("script")){
150151
KVValue script = new KVValue("script", makeMethodField(mExpr.getMethodName(), mExpr.getParameters(), null, alias));
151152
paramers.add(script);
152153
}
153-
else throw new SqlParseException("only support script as nested functions");
154+
else if(methodName.equals("nested")){
155+
NestedType nestedType = new NestedType();
156+
if(!nestedType.tryFillFromExpr(object)){
157+
throw new SqlParseException("failed parsing nested expr " + object);
158+
}
159+
paramers.add(new KVValue("nested",nestedType));
160+
}
161+
else throw new SqlParseException("only support script/nested as inner functions");
154162
}else {
155163
paramers.add(new KVValue(Util.expr2Object(object)));
156164
}

‎src/main/java/org/nlpcn/es4sql/query/AggregationQueryAction.java

Lines changed: 14 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -59,7 +59,7 @@ public SqlElasticSearchRequestBuilder explain() throws SqlParseException {
5959
}
6060

6161
if(field.isNested()){
62-
NestedBuilder nestedBuilder = AggregationBuilders.nested(field.getName() + "@NESTED")
62+
NestedBuilder nestedBuilder = AggregationBuilders.nested(getNestedAggName(field))
6363
.path(field.getNestedPath());
6464
if(insertFilterIfExistsAfter(lastAgg, groupBy, nestedBuilder,1)){
6565
groupBy.remove(1);
@@ -73,7 +73,6 @@ public SqlElasticSearchRequestBuilder explain() throws SqlParseException {
7373
request.addAggregation(lastAgg);
7474
}
7575

76-
7776
for (int i = 1; i < groupBy.size(); i++) {
7877
field = groupBy.get(i);
7978
AggregationBuilder<?> subAgg = aggMaker.makeGroupAgg(field);
@@ -83,7 +82,7 @@ public SqlElasticSearchRequestBuilder explain() throws SqlParseException {
8382

8483
if(field.isNested()){
8584

86-
NestedBuilder nestedBuilder = AggregationBuilders.nested(field.getName() + "@NESTED")
85+
NestedBuilder nestedBuilder = AggregationBuilders.nested(getNestedAggName(field))
8786
.path(field.getNestedPath());
8887
if(insertFilterIfExistsAfter(subAgg, groupBy, nestedBuilder,i+1)){
8988
groupBy.remove(i+1);
@@ -144,6 +143,18 @@ public SqlElasticSearchRequestBuilder explain() throws SqlParseException {
144143
return sqlElasticRequestBuilder;
145144
}
146145

146+
private String getNestedAggName(Field field) {
147+
String prefix;
148+
if(field instanceof MethodField){
149+
prefix = field.getNestedPath();
150+
}
151+
else {
152+
prefix = field.getName();
153+
}
154+
return prefix + "@NESTED";
155+
}
156+
157+
147158
private boolean insertFilterIfExistsAfter(AggregationBuilder<?> agg, List<Field> groupBy, NestedBuilder builder, int nextPosition) throws SqlParseException {
148159
if(groupBy.size() <= nextPosition) return false;
149160
Field filterFieldCandidate = groupBy.get(nextPosition);

‎src/main/java/org/nlpcn/es4sql/query/maker/AggMaker.java

Lines changed: 34 additions & 27 deletions
Original file line numberDiff line numberDiff line change
@@ -15,6 +15,7 @@
1515
import org.elasticsearch.search.aggregations.bucket.histogram.DateHistogramBuilder;
1616
import org.elasticsearch.search.aggregations.bucket.histogram.Histogram;
1717
import org.elasticsearch.search.aggregations.bucket.histogram.HistogramBuilder;
18+
import org.elasticsearch.search.aggregations.bucket.nested.NestedBuilder;
1819
import org.elasticsearch.search.aggregations.bucket.range.RangeBuilder;
1920
import org.elasticsearch.search.aggregations.bucket.range.date.DateRangeBuilder;
2021
import org.elasticsearch.search.aggregations.bucket.terms.TermsBuilder;
@@ -29,6 +30,8 @@
2930
import org.nlpcn.es4sql.domain.MethodField;
3031
import org.nlpcn.es4sql.domain.Where;
3132
import org.nlpcn.es4sql.exception.SqlParseException;
33+
import org.nlpcn.es4sql.parse.NestedType;
34+
import org.nlpcn.es4sql.query.join.NestedLoopsElasticRequestBuilder;
3235

3336
public class AggMaker {
3437

@@ -74,32 +77,25 @@ public AbstractAggregationBuilder makeFieldAgg(MethodField field, AbstractAggreg
7477
switch (field.getName().toUpperCase()) {
7578
case "SUM":
7679
builder = AggregationBuilders.sum(field.getAlias());
77-
addFieldOrScriptToAggregation(field, builder);
78-
return builder;
80+
return addFieldOrScriptToAggregation(field, builder);
7981
case "MAX":
8082
builder = AggregationBuilders.max(field.getAlias());
81-
addFieldOrScriptToAggregation(field, builder);
82-
return builder;
83+
return addFieldOrScriptToAggregation(field, builder);
8384
case "MIN":
8485
builder = AggregationBuilders.min(field.getAlias());
85-
addFieldOrScriptToAggregation(field, builder);
86-
return builder;
86+
return addFieldOrScriptToAggregation(field, builder);
8787
case "AVG":
8888
builder = AggregationBuilders.avg(field.getAlias());
89-
addFieldOrScriptToAggregation(field, builder);
90-
return builder;
89+
return addFieldOrScriptToAggregation(field, builder);
9190
case "STATS":
9291
builder = AggregationBuilders.stats(field.getAlias());
93-
addFieldOrScriptToAggregation(field, builder);
94-
return builder;
92+
return addFieldOrScriptToAggregation(field, builder);
9593
case "EXTENDED_STATS":
9694
builder = AggregationBuilders.extendedStats(field.getAlias());
97-
addFieldOrScriptToAggregation(field, builder);
98-
return builder;
95+
return addFieldOrScriptToAggregation(field, builder);
9996
case "PERCENTILES":
10097
builder = AggregationBuilders.percentiles(field.getAlias());
101-
addFieldOrScriptToAggregation(field, builder);
102-
return builder;
98+
return addFieldOrScriptToAggregation(field, builder);
10399
case "TOPHITS":
104100
return makeTopHitsAgg(field);
105101
case "SCRIPTED_METRIC":
@@ -117,34 +113,40 @@ private String fixAlias(String alias) {
117113
return alias.replaceAll("\\[","(").replaceAll("\\]",")");
118114
}
119115

120-
private void addFieldOrScriptToAggregation(MethodField field, ValuesSourceMetricsAggregationBuilder builder) {
116+
private AbstractAggregationBuilder addFieldOrScriptToAggregation(MethodField field, ValuesSourceMetricsAggregationBuilder builder) {
121117
KVValue kvValue = field.getParams().get(0);
122-
if(kvValue.key==null || !kvValue.key.equals("script") )
123-
builder.field(kvValue.toString());
124-
else
125-
{
118+
if(kvValue.key!=null && kvValue.key.equals("script") ){
126119
//todo: support different lang script
127-
builder.script(((MethodField)kvValue.value).getParams().get(1).toString());
120+
return builder.script(((MethodField)kvValue.value).getParams().get(1).toString());
128121
}
122+
else if (kvValue.key!=null && kvValue.key.equals("nested")){
123+
NestedType nestedType = (NestedType) kvValue.value;
124+
builder.field(nestedType.field);
125+
return AggregationBuilders.nested(nestedType.field+"@NESTED").path(nestedType.path).subAggregation(builder);
126+
}
127+
return builder.field(kvValue.toString());
129128
}
130129

131130
private AggregationBuilder<?> makeRangeGroup(MethodField field) throws SqlParseException {
131+
132+
AggregationBuilder<?> aggregationBuilder;
132133
switch (field.getName().toLowerCase()) {
133134
case "range":
134-
return rangeBuilder(field);
135+
aggregationBuilder = rangeBuilder(field);break;
135136
case "date_histogram":
136-
return dateHistogram(field);
137+
aggregationBuilder = dateHistogram(field);break;
137138
case "date_range":
138-
return dateRange(field);
139+
aggregationBuilder = dateRange(field);break;
139140
case "month":
140-
return dateRange(field);
141+
aggregationBuilder = dateRange(field);break;
141142
case "histogram":
142-
return histogram(field);
143+
aggregationBuilder = histogram(field);break;
143144
case "geohash_grid":
144-
return geohashGrid(field);
145+
aggregationBuilder = geohashGrid(field);break;
145146
default:
146147
throw new SqlParseException("can define this method " + field);
147148
}
149+
return aggregationBuilder;
148150

149151
}
150152

@@ -207,6 +209,7 @@ private AbstractAggregationBuilder scriptedMetric(MethodField field) throws SqlP
207209
scriptedMetricBuilder.reduceScriptFile(paramValue);
208210
break;
209211
case "alias":
212+
case "nested":
210213
break;
211214
default:
212215
throw new SqlParseException("scripted_metric err or not define field " + param.getKey());
@@ -242,6 +245,7 @@ private AggregationBuilder<?> geohashGrid(MethodField field) throws SqlParseExce
242245
geoHashGrid.shardSize(Integer.parseInt(value));
243246
break;
244247
case "alias":
248+
case "nested":
245249
break;
246250
default:
247251
throw new SqlParseException("geohash grid err or not define field " + kv.toString());
@@ -323,6 +327,7 @@ private DateHistogramBuilder dateHistogram(MethodField field) throws SqlParseExc
323327
dateHistogram.preOffset(value);
324328
break;
325329
case "alias":
330+
case "nested":
326331
break;
327332
default:
328333
throw new SqlParseException("date range err or not define field " + kv.toString());
@@ -362,6 +367,7 @@ private HistogramBuilder histogram(MethodField field) throws SqlParseException {
362367
histogram.extendedBounds(Long.valueOf(bounds[0]), Long.valueOf(bounds[1]));
363368
break;
364369
case "alias":
370+
case "nested":
365371
break;
366372
case "order":
367373
Histogram.Order order = null;
@@ -455,7 +461,8 @@ private AbstractAggregationBuilder makeTopHitsAgg(MethodField field) {
455461
topHits.setSize((int) kv.value);
456462
break;
457463
case "alias":
458-
break;
464+
case "nested":
465+
break;
459466
default:
460467
topHits.addSort(kv.key, SortOrder.valueOf(kv.value.toString().toUpperCase()));
461468
break;

‎src/test/java/org/nlpcn/es4sql/AggregationTest.java

Lines changed: 40 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -11,6 +11,7 @@
1111
import org.elasticsearch.search.aggregations.bucket.filter.InternalFilter;
1212
import org.elasticsearch.search.aggregations.bucket.geogrid.GeoHashGrid;
1313
import org.elasticsearch.search.aggregations.bucket.geogrid.InternalGeoHashGrid;
14+
import org.elasticsearch.search.aggregations.bucket.histogram.Histogram;
1415
import org.elasticsearch.search.aggregations.bucket.nested.InternalNested;
1516
import org.elasticsearch.search.aggregations.bucket.terms.Terms;
1617
import org.elasticsearch.search.aggregations.metrics.avg.Avg;
@@ -473,6 +474,45 @@ public void groupByOnNestedFieldWithFilterTest() throws Exception {
473474
}
474475
}
475476

477+
@Test
478+
public void minOnNestedField() throws Exception {
479+
Aggregations result = query(String.format("SELECT min(nested(message.dayOfWeek)) as minDays FROM %s/nestedType", TEST_INDEX));
480+
InternalNested nested = result.get("message.dayOfWeek@NESTED");
481+
Min mins = nested.getAggregations().get("minDays");
482+
Assert.assertEquals(1.0,mins.getValue(),0.0001);
483+
484+
}
485+
486+
@Test
487+
public void sumOnNestedField() throws Exception {
488+
Aggregations result = query(String.format("SELECT sum(nested(message.dayOfWeek)) as sumDays FROM %s/nestedType", TEST_INDEX));
489+
InternalNested nested = result.get("message.dayOfWeek@NESTED");
490+
Sum sum = nested.getAggregations().get("sumDays");
491+
Assert.assertEquals(13.0,sum.getValue(),0.0001);
492+
493+
}
494+
495+
@Test
496+
public void histogramOnNestedField() throws Exception {
497+
Aggregations result = query(String.format("select count(*) from %s/nestedType group by histogram('field'='message.dayOfWeek','nested'='message','interval'='2' , 'alias' = 'someAlias' )", TEST_INDEX));
498+
InternalNested nested = result.get("message@NESTED");
499+
Histogram histogram = nested.getAggregations().get("someAlias");
500+
for(Histogram.Bucket bucket : histogram.getBuckets()){
501+
long count = ((ValueCount) bucket.getAggregations().get("COUNT(*)")).getValue();
502+
if(bucket.getKey().equals("0") || bucket.getKey().equals("4")){
503+
Assert.assertEquals(2,count);
504+
}
505+
else if (bucket.getKey().equals("2")){
506+
Assert.assertEquals(1,count);
507+
}
508+
else{
509+
Assert.assertTrue("only 0 2 4 keys are allowed got:" + bucket.getKey(),false);
510+
}
511+
}
512+
513+
514+
}
515+
476516

477517

478518
}

‎src/test/java/org/nlpcn/es4sql/MainTestSuite.java

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -89,6 +89,9 @@ private static void prepareNestedTypeIndex() {
8989
" \"author\": {\n" +
9090
" \"type\": \"string\",\n" +
9191
" \"index\": \"not_analyzed\"\n" +
92+
" },\n" +
93+
" \"dayOfWeek\": {\n" +
94+
" \"type\": \"long\"\n" +
9295
" }\n" +
9396
" }\n" +
9497
" },\n" +

‎src/test/resources/nested_objects.json

Lines changed: 4 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1,8 +1,8 @@
11
{"index":{"_type": "nestedType", "_id":"1"}}
2-
{"message":{"info":"a","author":"e"},"myNum":1}
2+
{"message":{"info":"a","author":"e","dayOfWeek":1},"myNum":1}
33
{"index":{"_type": "nestedType", "_id":"2"}}
4-
{"message":{"info":"b","author":"f"},"myNum":2}
4+
{"message":{"info":"b","author":"f","dayOfWeek":2},"myNum":2}
55
{"index":{"_type": "nestedType", "_id":"3"}}
6-
{"message":{"info":"c","author":"g"},"myNum":3}
6+
{"message":{"info":"c","author":"g","dayOfWeek":1},"myNum":3}
77
{"index":{"_type": "nestedType", "_id":"4"}}
8-
{"message":[{"info":"c","author":"h"},{"info":"a","author":"i"}],"myNum":4}
8+
{"message":[{"info":"c","author":"h","dayOfWeek":4},{"info":"a","author":"i","dayOfWeek":5}],"myNum":4}

0 commit comments

Comments
 (0)