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

Question

我有这个非常简单的 SQL 查询：SELECT * FROM tableWHERE ((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 cwhile

qb.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

;)