I've this quite simple SQL query:
SELECT * FROM table
WHERE
((sphereMin>=-17 AND sphereMax<=5) OR sphereMin<=-17)
AND
((sphereMax<=5 AND sphereMax>=-17) OR sphereMax>=-17)
I have to translate that query to ES using high level API. This is the relevant code:
SearchSourceBuilder searchBuilder = new SearchSourceBuilder();
BoolQueryBuilder boolQueryBuilder = boolQuery();
BoolQueryBuilder booleanQuery1 = boolQuery();
BoolQueryBuilder subQuery1a = boolQuery();
booleanQuery1.must(subQuery1a);
RangeQueryBuilder subQuery1b = null;
subQuery1a.must(rangeQuery("sphereMIn").gte(-17));
subQuery1a.must(rangeQuery("sphereMax").lte(5));
subQuery1b = rangeQuery("sphereMin").lte(-17);
booleanQuery1.should(subQuery1b);
BoolQueryBuilder booleanQuery2 = boolQuery();
BoolQueryBuilder subQuery2a = boolQuery();
booleanQuery2.must(subQuery2a);
RangeQueryBuilder subQuery2b = null;
subQuery2a.must(rangeQuery("sphereMax").gte(-17));
subQuery2a.must(rangeQuery("sphereMax").lte(5));
subQuery2b = rangeQuery("sphereMax").gte(5);
booleanQuery2.should(subQuery2b);
boolQueryBuilder.filter(booleanQuery1);
boolQueryBuilder.filter(booleanQuery2);
searchBuilder.query(boolQueryBuilder);
SearchRequest searchRequest = Requests.searchRequest(indexName).allowPartialSearchResults(true)
.source(searchBuilder).routing(routing);
The JSON created from that code is:
{
"bool" : {
"filter" : [
{
"bool" : {
"must" : [
{
"bool" : {
"must" : [
{
"range" : {
"sphereMin" : {
"from" : "-17",
"to" : null,
"include_lower" : true,
"include_upper" : true,
"boost" : 1.0
}
}
},
{
"range" : {
"sphereMax" : {
"from" : null,
"to" : "5",
"include_lower" : true,
"include_upper" : true,
"boost" : 1.0
}
}
}
],
"adjust_pure_negative" : true,
"boost" : 1.0
}
}
],
"should" : [
{
"range" : {
"sphereMin" : {
"from" : null,
"to" : "-17",
"include_lower" : true,
"include_upper" : true,
"boost" : 1.0
}
}
}
],
"adjust_pure_negative" : true,
"boost" : 1.0
}
},
{
"bool" : {
"must" : [
{
"bool" : {
"must" : [
{
"range" : {
"sphereMax" : {
"from" : "-17",
"to" : null,
"include_lower" : true,
"include_upper" : true,
"boost" : 1.0
}
}
},
{
"range" : {
"sphereMax" : {
"from" : null,
"to" : "5",
"include_lower" : true,
"include_upper" : true,
"boost" : 1.0
}
}
}
],
"adjust_pure_negative" : true,
"boost" : 1.0
}
}
],
"should" : [
{
"range" : {
"sphereMax" : {
"from" : "5",
"to" : null,
"include_lower" : true,
"include_upper" : true,
"boost" : 1.0
}
}
}
],
"adjust_pure_negative" : true,
"boost" : 1.0
}
}
],
"adjust_pure_negative" : true,
"boost" : 1.0
}
}
It looks right to me but that query doesn't return the same data of the SQL query. What am I doing wrong?