CODEWITHSUNDEEP
javaelasticsearchelasticsearch-2.0
Fabian Lurz
Fabian Lurz

Reputation: 2039

ElasticSearch (2.2) Java filter between startDate and endDate if exists

I want to have following filter with elasticsearch (java client):

If startDate exists and lower then now

If endDate exists and greater then now

I already have following but it doesn't show the once which have no startDate or endDate:

       //Start date end date

        BoolQueryBuilder queryBuilder = new BoolQueryBuilder();
        queryBuilder.filter(QueryBuilders.rangeQuery("startDate").lte("now"));
        queryBuilder.should(QueryBuilders.existsQuery("startDate"));
        queryBuilder.filter(QueryBuilders.rangeQuery("endDate").gte("now"));
        queryBuilder.should(QueryBuilders.existsQuery("endDate"));

        boolQueryBuilder.filter(queryBuilder);

The query in mysql looked like this:

SELECT * FROM customer_job WHERE (start_date <= CURDATE() OR start_date IS NULL) AND (end_date>=CURDATE() OR end_date IS NULL);

So how to do that?

I tried following from the answers but it still does not show the ones who have no startDate or endDate:

BoolQueryBuilder startDateQuery = new BoolQueryBuilder()
                .should(QueryBuilders.existsQuery("startDate"))
                .should(QueryBuilders.rangeQuery("startDate").lte("now"))
                .minimumNumberShouldMatch(1);
        BoolQueryBuilder endDateQuery = new BoolQueryBuilder()
                .should(QueryBuilders.existsQuery("endDate"))
                .should(QueryBuilders.rangeQuery("endDate").gte("now"))
                .minimumNumberShouldMatch(1);

        boolQueryBuilder.filter(startDateQuery).filter(endDateQuery);

Upvotes: 1

Views: 800

Answers (1)

Val
Val

Reputation: 217434

You need a query like this:

BoolQueryBuilder startDateQuery = new BoolQueryBuilder()
   .should(QueryBuilders.missingQuery("startDate"))
   .should(QueryBuilders.rangeQuery("startDate").lte("now"))
   .minimumShouldMatch(1);
BoolQueryBuilder endDateQuery = new BoolQueryBuilder()
   .should(QueryBuilders.missingQuery("endDate"))
   .should(QueryBuilders.rangeQuery("endDate").gte("now"))
   .minimumShouldMatch(1);
BoolQueryBuilder queryBuilder = new BoolQueryBuilder()
   .filter(startDateQuery)
   .filter(endDateQuery);

Upvotes: 1

Related Questions