从 SQL 创建 ElasticSearch 6.5 嵌套布尔查询

Question

我有这个非常简单的 SQL 查询：

SELECT * FROM table

WHERE 

((sphereMin>=-17 AND sphereMax<=5) OR sphereMin<=-17)

AND

((sphereMax<=5 AND sphereMax>=-17) OR sphereMax>=5)

我必须使用高级 API 将该查询转换为 ES。这是相关代码：

 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);

它看起来对我来说是正确的，但该查询不会返回与 SQL 查询相同的数据。我究竟做错了什么？

Answer 1

在你的情况下booleanQuery1.must(subQuery1a);应该是booleanQuery1.should(subQuery1a);也booleanQuery2.must(subQuery2a);也应该是booleanQuery2.should(subQuery2a);例子：qb.should(a);qb.should(b);qb.should(c);默认情况下，必须至少满足以下条件之一：a OR b OR cwhileqb.must(a);qb.should(b);表示 a必须匹配，而 b应该。

Answer 2

((sphereMax<=5 AND sphereMax>=-17) OR sphereMax>=-17)ubQuery2a.must(rangeQuery("sphereMax").gte(-17));subQuery2a.must(rangeQuery("sphereMax").lte(5));subQuery2b = rangeQuery("sphereMax").gte(5);5 而不是 -17;)