Search code examples
spring-bootelasticsearchelastic-stackelasticsearch-6

Elasticsearch - must(QueryBuilders.rangeQuery) and mustNot(QueryBuilders.existsQuery) not working at a time

I am using High level java rest client(6.5) and I need to filter datas according to some date range- let, I have these 4 documents (added a postman image) in a same index and in 2 documents I don't have publish_date and publish_end_date field so when I will put a range I need the documents which will come in between those range and the documents which don't have those range fields also.

Json Values

I got some help from this question- ElasticSearch (2.2) Java filter between startDate and endDate if exists as the QueryBuilders.missingQuery is deprecated so I am using mustNot(QueryBuilders.existsQuery) by taking the reference from here but it is not working and I am not getting the results.

when I am using only the range query like below its working fine-

BoolQueryBuilder startDateQuery = new BoolQueryBuilder()
           .must(QueryBuilders.rangeQuery("publish_date").lte("now"));
BoolQueryBuilder endDateQuery = new BoolQueryBuilder()
          .must(QueryBuilders.rangeQuery("publish_end_date").gte("now"));

result 1

When I am using only the mustNot query like below I am getting the desired results-

BoolQueryBuilder startDateQuery = new BoolQueryBuilder()
           .mustNot(QueryBuilders.existsQuery("publish_date"));
BoolQueryBuilder endDateQuery = new BoolQueryBuilder()
          .mustNot(QueryBuilders.existsQuery("publish_end_date"));

result 2

But the problem is when I combine both the queries like below I am not getting any result(getting an empty array)-

BoolQueryBuilder startDateQuery = new BoolQueryBuilder()
           .must(QueryBuilders.rangeQuery("publish_date").lte("now"))
           .mustNot(QueryBuilders.existsQuery("publish_date"));
BoolQueryBuilder endDateQuery = new BoolQueryBuilder()
          .must(QueryBuilders.rangeQuery("publish_end_date").gte("now"))
          .mustNot(QueryBuilders.existsQuery("publish_end_date"));

result 3

I want all the result 1 and result 2 values in result 3(thats my requirement).

Solution

  • Here is what you should do:

    BoolQueryBuilder boolQuery = QueryBuilders.boolQuery()
  .should(QueryBuilders.rangeQuery("publish_date").lte("now"))
  .should(QueryBuilders.boolQuery().mustNot(QueryBuilders.existsQuery("publish_date")));