Skip to content

Commit 212c0b8

Browse files
eliranmoyaleliranmoyal
committed
nestedObjects support on where conditions
1 parent a23a5c6 commit 212c0b8

File tree

10 files changed

+229
-22
lines changed

10 files changed

+229
-22
lines changed

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

Lines changed: 41 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -49,9 +49,11 @@ public OPEAR negative() throws SqlParseException {
4949

5050
private OPEAR opear;
5151

52-
private String aliasTableName;
52+
private boolean isNested;
5353

54-
public Condition(CONN conn, String name, OPEAR oper, Object value) throws SqlParseException {
54+
private String nestedPath;
55+
56+
public Condition(CONN conn, String name, OPEAR oper, Object value,boolean isNested , String nestedPath) throws SqlParseException {
5557
super(conn);
5658
this.opear = null;
5759

@@ -60,11 +62,19 @@ public Condition(CONN conn, String name, OPEAR oper, Object value) throws SqlPar
6062
this.value = value;
6163

6264
this.opear = oper ;
65+
66+
this.isNested = isNested;
67+
68+
this.nestedPath = nestedPath;
6369
}
6470

65-
public Condition(CONN conn, String name, String oper, Object value) throws SqlParseException {
71+
public Condition(CONN conn, String name, String oper, Object value,boolean isNested,String nestedPath) throws SqlParseException {
6672
super(conn);
6773

74+
this.isNested = isNested;
75+
76+
this.nestedPath = nestedPath;
77+
6878
this.opear = null;
6979

7080
this.name = name;
@@ -165,20 +175,43 @@ public void setOpear(OPEAR opear) {
165175
this.opear = opear;
166176
}
167177

168-
@Override
169-
public String toString() {
178+
public boolean isNested() {
179+
return isNested;
180+
}
181+
182+
public void setNested(boolean isNested) {
183+
this.isNested = isNested;
184+
}
185+
186+
public String getNestedPath() {
187+
return nestedPath;
188+
}
189+
190+
public void setNestedPath(String nestedPath) {
191+
this.nestedPath = nestedPath;
192+
}
170193

194+
@Override
195+
public String toString() {
196+
String result = "";
197+
if(this.isNested()){
198+
result = "nested condition ";
199+
if(this.getNestedPath()!=null){
200+
result+="on path:" + this.getNestedPath() + " ";
201+
}
202+
}
171203
if (value instanceof Object[]) {
172-
return this.conn + " " + this.name + " " + this.opear + " " + Arrays.toString((Object[]) value);
204+
result += this.conn + " " + this.name + " " + this.opear + " " + Arrays.toString((Object[]) value);
173205
} else {
174-
return this.conn + " " + this.name + " " + this.opear + " " + this.value;
206+
result += this.conn + " " + this.name + " " + this.opear + " " + this.value;
175207
}
208+
return result;
176209
}
177210

178211
@Override
179212
public Object clone() throws CloneNotSupportedException {
180213
try {
181-
Condition clonedCondition = new Condition(this.getConn(),this.getName(),this.getOpear(),this.getValue());
214+
Condition clonedCondition = new Condition(this.getConn(),this.getName(),this.getOpear(),this.getValue(),this.isNested(),this.getNestedPath());
182215
return clonedCondition;
183216
} catch (SqlParseException e) {
184217

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

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,9 @@
1+
package org.nlpcn.es4sql.parse;
2+
3+
/**
4+
* Created by Eliran on 12/11/2015.
5+
*/
6+
public class NestedType {
7+
public String field;
8+
public String path;
9+
}

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

Lines changed: 71 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -14,6 +14,7 @@
1414
import org.nlpcn.es4sql.domain.hints.Hint;
1515
import org.nlpcn.es4sql.domain.hints.HintFactory;
1616
import org.nlpcn.es4sql.exception.SqlParseException;
17+
import org.nlpcn.es4sql.query.join.NestedLoopsElasticRequestBuilder;
1718
import org.nlpcn.es4sql.spatial.SpatialParamsFactory;
1819

1920
/**
@@ -80,10 +81,18 @@ private Where findWhere(SQLExpr where) throws SqlParseException {
8081

8182

8283
private boolean isCond(SQLBinaryOpExpr expr) {
83-
return expr.getLeft() instanceof SQLIdentifierExpr || expr.getLeft() instanceof SQLPropertyExpr || expr.getLeft() instanceof SQLVariantRefExpr;
84+
SQLExpr leftSide = expr.getLeft();
85+
if(leftSide instanceof SQLMethodInvokeExpr){
86+
return isAllowedMethodOnConditionLeft((SQLMethodInvokeExpr) leftSide);
87+
}
88+
return leftSide instanceof SQLIdentifierExpr || leftSide instanceof SQLPropertyExpr || leftSide instanceof SQLVariantRefExpr;
8489
}
8590

86-
private void parseWhere(SQLExpr expr, Where where) throws SqlParseException {
91+
private boolean isAllowedMethodOnConditionLeft(SQLMethodInvokeExpr method) {
92+
return method.getMethodName().toLowerCase().equals("nested");
93+
}
94+
95+
private void parseWhere(SQLExpr expr, Where where) throws SqlParseException {
8796
if (expr instanceof SQLBinaryOpExpr && !isCond((SQLBinaryOpExpr) expr)) {
8897
SQLBinaryOpExpr bExpr = (SQLBinaryOpExpr) expr;
8998
routeCond(bExpr, bExpr.getLeft(), where);
@@ -120,29 +129,48 @@ private void explanCond(String opear, SQLExpr expr, Where where) throws SqlParse
120129
if (expr instanceof SQLBinaryOpExpr) {
121130
SQLBinaryOpExpr soExpr = (SQLBinaryOpExpr) expr;
122131
boolean methodAsOpear = false;
132+
boolean nestedFieldCondition = false;
133+
String nestedPath = null ;
134+
NestedType nestedType = new NestedType();
135+
if(tryFillNested(soExpr.getLeft(),nestedType)){
136+
soExpr.setLeft(new SQLIdentifierExpr(nestedType.field));
137+
nestedFieldCondition = true;
138+
nestedPath = nestedType.path ;
139+
}
140+
123141
if(soExpr.getRight() instanceof SQLMethodInvokeExpr){
124142
SQLMethodInvokeExpr method = (SQLMethodInvokeExpr) soExpr.getRight();
125143
String methodName = method.getMethodName().toLowerCase();
126144

127145
if(Condition.OPEAR.methodNameToOpear.containsKey(methodName)){
128146
Object[] methodParametersValue = getMethodValuesWithSubQueries(method);
129-
Condition condition = new Condition(CONN.valueOf(opear) ,soExpr.getLeft().toString(), Condition.OPEAR.methodNameToOpear.get(methodName),methodParametersValue);
147+
Condition condition = new Condition(CONN.valueOf(opear) ,soExpr.getLeft().toString(), Condition.OPEAR.methodNameToOpear.get(methodName),methodParametersValue,nestedFieldCondition,nestedPath);
130148
where.addWhere(condition);
131149
methodAsOpear = true;
132150
}
133151
}
134152
if(!methodAsOpear){
135-
Condition condition = new Condition(CONN.valueOf(opear), soExpr.getLeft().toString(), soExpr.getOperator().name, parseValue(soExpr.getRight()));
153+
Condition condition = new Condition(CONN.valueOf(opear), soExpr.getLeft().toString(), soExpr.getOperator().name, parseValue(soExpr.getRight()),nestedFieldCondition,nestedPath);
136154
where.addWhere(condition);
137155
}
138156
} else if (expr instanceof SQLInListExpr) {
139157
SQLInListExpr siExpr = (SQLInListExpr) expr;
140-
Condition condition = new Condition(CONN.valueOf(opear), siExpr.getExpr().toString(), siExpr.isNot() ? "NOT IN" : "IN", parseValue(siExpr.getTargetList()));
158+
NestedType nestedType = new NestedType();
159+
String leftSide = siExpr.getExpr().toString();
160+
if(tryFillNested(siExpr.getExpr(),nestedType)){
161+
leftSide = nestedType.field;
162+
}
163+
Condition condition = new Condition(CONN.valueOf(opear), leftSide, siExpr.isNot() ? "NOT IN" : "IN", parseValue(siExpr.getTargetList()),nestedType.field!=null,nestedType.path);
141164
where.addWhere(condition);
142165
} else if (expr instanceof SQLBetweenExpr) {
143166
SQLBetweenExpr between = ((SQLBetweenExpr) expr);
144-
Condition condition = new Condition(CONN.valueOf(opear), between.getTestExpr().toString(), between.isNot() ? "NOT BETWEEN" : "BETWEEN", new Object[]{parseValue(between.beginExpr),
145-
parseValue(between.endExpr)});
167+
String leftSide = between.getTestExpr().toString();
168+
NestedType nestedType = new NestedType();
169+
if(tryFillNested(between.getTestExpr(),nestedType)){
170+
leftSide = nestedType.field;
171+
}
172+
Condition condition = new Condition(CONN.valueOf(opear), leftSide, between.isNot() ? "NOT BETWEEN" : "BETWEEN", new Object[]{parseValue(between.beginExpr),
173+
parseValue(between.endExpr)},nestedType.field!=null,nestedType.path);
146174
where.addWhere(condition);
147175
}
148176
else if (expr instanceof SQLMethodInvokeExpr) {
@@ -151,23 +179,57 @@ else if (expr instanceof SQLMethodInvokeExpr) {
151179

152180
String methodName = methodExpr.getMethodName();
153181
String fieldName = methodParameters.get(0).toString();
182+
NestedType nestedType = new NestedType();
183+
if(tryFillNested(methodParameters.get(0),nestedType)){
184+
fieldName = nestedType.field;
185+
}
186+
154187
Object spatialParamsObject = SpatialParamsFactory.generateSpatialParamsObject(methodName, methodParameters);
155188

156-
Condition condition = new Condition(CONN.valueOf(opear), fieldName, methodName, spatialParamsObject);
189+
Condition condition = new Condition(CONN.valueOf(opear), fieldName, methodName, spatialParamsObject,nestedType.field!=null,nestedType.path);
157190
where.addWhere(condition);
158191
} else if (expr instanceof SQLInSubQueryExpr){
159192
SQLInSubQueryExpr sqlIn = (SQLInSubQueryExpr) expr;
160193
Select innerSelect = parseSelect((MySqlSelectQueryBlock) sqlIn.getSubQuery().getQuery());
161194
if(innerSelect.getFields() == null || innerSelect.getFields().size()!=1)
162195
throw new SqlParseException("should only have one return field in subQuery");
163196
SubQueryExpression subQueryExpression = new SubQueryExpression(innerSelect);
164-
Condition condition = new Condition(CONN.valueOf(opear), sqlIn.getExpr().toString(), sqlIn.isNot() ? "NOT IN" : "IN",subQueryExpression);
197+
String leftSide = sqlIn.getExpr().toString();
198+
NestedType nestedType = new NestedType();
199+
if(tryFillNested(sqlIn.getExpr(),nestedType)){
200+
leftSide = nestedType.field;
201+
}
202+
Condition condition = new Condition(CONN.valueOf(opear), leftSide, sqlIn.isNot() ? "NOT IN" : "IN",subQueryExpression,nestedType.field!=null,nestedType.path);
165203
where.addWhere(condition);
166204
} else {
167205
throw new SqlParseException("err find condition " + expr.getClass());
168206
}
169207
}
170208

209+
private boolean tryFillNested(SQLExpr expr,NestedType nestedType) throws SqlParseException {
210+
if (!(expr instanceof SQLMethodInvokeExpr)) return false;
211+
SQLMethodInvokeExpr method = (SQLMethodInvokeExpr) expr;
212+
if (!method.getMethodName().toLowerCase().equals("nested")) return false;
213+
214+
List<SQLExpr> parameters = method.getParameters();
215+
if (parameters.size() != 2 && parameters.size() != 1)
216+
throw new SqlParseException("on nested object only allowed 2 parameters (field,path) or 1 parameter (field) ");
217+
218+
String field = parameters.get(0).toString();
219+
nestedType.field = field;
220+
if (parameters.size() == 1) {
221+
//calc path myself..
222+
if (!field.contains("."))
223+
throw new SqlParseException("nested should contain . on their field name");
224+
int lastDot = field.lastIndexOf(".");
225+
nestedType.path = field.substring(0, lastDot);
226+
} else if (parameters.size() == 2) {
227+
nestedType.path = parameters.get(1).toString();
228+
}
229+
230+
return true;
231+
}
232+
171233
private Object[] getMethodValuesWithSubQueries(SQLMethodInvokeExpr method) throws SqlParseException {
172234
List<Object> values = new ArrayList<>();
173235
boolean foundSubQuery = false;

‎src/main/java/org/nlpcn/es4sql/query/join/ESHashJoinQueryAction.java

Lines changed: 0 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -88,7 +88,6 @@ private List<List<Map.Entry<Field, Field>>> getComparisonFields(String t1Alias,
8888
comparisonFields.add(getComparisonFieldsFromWhere(t1Alias,t2Alias,innerWhere));
8989
}
9090
}
91-
//todo: where is condition. is it possible?
9291

9392
return comparisonFields;
9493
}

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

Lines changed: 7 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -50,7 +50,13 @@ private void explanWhere(BoolFilterBuilder boolFilter, Where where) throws SqlPa
5050
* @param subFilter
5151
*/
5252
private void addSubFilter(BoolFilterBuilder boolFilter, Where where, BaseFilterBuilder subFilter) {
53-
if (where.getConn() == CONN.AND) {
53+
if(where instanceof Condition){
54+
Condition condition = (Condition) where;
55+
if(condition.isNested()){
56+
subFilter = FilterBuilders.nestedFilter(condition.getNestedPath(),subFilter);
57+
}
58+
}
59+
if (where.getConn() == CONN.AND) {
5460
boolFilter.must(subFilter);
5561
} else {
5662
boolFilter.should(subFilter);

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

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -85,7 +85,7 @@ private ToXContent make(Condition cond, String name, SQLMethodInvokeExpr value)
8585
case "scorequery":
8686
case "score_query":
8787
Float boost = Float.parseFloat(value.getParameters().get(1).toString());
88-
Condition subCond = new Condition(cond.getConn(), cond.getName(), cond.getOpear(), value.getParameters().get(0));
88+
Condition subCond = new Condition(cond.getConn(), cond.getName(), cond.getOpear(), value.getParameters().get(0),false,null);
8989
if (isQuery) {
9090
bqb = QueryBuilders.constantScoreQuery((QueryBuilder) make(subCond)).boost(boost);
9191
} else {

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

Lines changed: 27 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -64,6 +64,9 @@ public static void setUp() throws Exception {
6464
prepareSpatialIndex("location2");
6565
loadBulk("src/test/resources/locations2.json");
6666

67+
prepareNestedTypeIndex();
68+
loadBulk("src/test/resources/nested_objects.json");
69+
6770
searchDao = new SearchDao(client);
6871

6972
//refresh to make sure all the docs will return on queries
@@ -72,8 +75,31 @@ public static void setUp() throws Exception {
7275
System.out.println("Finished the setup process...");
7376
}
7477

78+
private static void prepareNestedTypeIndex() {
79+
80+
String dataMapping = "{ \"nestedType\": {\n" +
81+
" \"properties\": {\n" +
82+
" \"message\": {\n" +
83+
" \"type\": \"nested\",\n" +
84+
" \"properties\": {\n" +
85+
" \"info\": {\n" +
86+
" \"type\": \"string\",\n" +
87+
" \"index\": \"not_analyzed\"\n" +
88+
" }\n" +
89+
" }\n" +
90+
" },\n" +
91+
" \"myNum\": {\n" +
92+
" \"type\": \"long\"\n" +
93+
" }\n" +
94+
" }\n" +
95+
" }\n" +
96+
" }}";
97+
98+
client.admin().indices().preparePutMapping(TEST_INDEX).setType("nestedType").setSource(dataMapping).execute().actionGet();
99+
}
100+
75101

76-
@AfterClass
102+
@AfterClass
77103
public static void tearDown() {
78104
System.out.println("teardown process...");
79105
}

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

Lines changed: 22 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -756,7 +756,29 @@ public void idsQuerySubQueryIds() throws SqlParseException, SQLFeatureNotSupport
756756

757757
}
758758

759+
@Test
760+
public void nestedEqualsTestFieldNormalField() throws IOException, SqlParseException, SQLFeatureNotSupportedException{
761+
SearchHits response = query(String.format("SELECT * FROM %s/nestedType where nested(message.info)='b'", TEST_INDEX));
762+
Assert.assertEquals(1, response.getTotalHits());
763+
}
764+
765+
@Test
766+
public void nestedEqualsTestFieldInsideArrays() throws IOException, SqlParseException, SQLFeatureNotSupportedException{
767+
SearchHits response = query(String.format("SELECT * FROM %s/nestedType where nested(message.info) = 'a'", TEST_INDEX));
768+
Assert.assertEquals(2, response.getTotalHits());
769+
}
759770

771+
@Test
772+
public void nestedOnInQuery() throws IOException, SqlParseException, SQLFeatureNotSupportedException{
773+
SearchHits response = query(String.format("SELECT * FROM %s/nestedType where nested(message.info) in ('a','b')", TEST_INDEX));
774+
Assert.assertEquals(3, response.getTotalHits());
775+
}
776+
777+
@Test
778+
public void nestedOnInTermsQuery() throws IOException, SqlParseException, SQLFeatureNotSupportedException{
779+
SearchHits response = query(String.format("SELECT * FROM %s/nestedType where nested(message.info) = IN_TERMS(a,b)", TEST_INDEX));
780+
Assert.assertEquals(3, response.getTotalHits());
781+
}
760782

761783
private SearchHits query(String query) throws SqlParseException, SQLFeatureNotSupportedException, SQLFeatureNotSupportedException {
762784
SearchDao searchDao = MainTestSuite.getSearchDao();

0 commit comments

Comments
 (0)