Elasticsearch query on array of objects with date

Question

Hi i am new to Elasticsearch and trying to implement a solution using spring-data-elasticsearch. My indexed data looks like this:

[
  {
    "worker": "A",
    "availability": [
      {
        "startDate": "2020-01-12",
        "endDate": "2020-02-12"
      },
      {
        "startDate": "2020-04-12",
        "endDate": "2020-05-12"
      }
    ]
  },
  {
    "worker": "B",
    "availability": [
      {
        "startDate": "2020-04-12",
        "endDate": "2020-11-12"
      }
    ]
  }
]

By referring the elastic docs i planned on using range query to fetch the records for a specified rage of dates, for example i wanted to fetch the available worker between "2020-05-12 to 2020-06-12". This is the query that i formed:

{
    "query": {
        "bool": {
            "must": [
                {
                    "nested": {
                        "query": {
                            "range": {
                                "availability.start_date": {
                                    "from": "2020-05-12T00:00:00.000Z",
                                    "to": "2020-06-12T00:00:00.000Z",
                                    "include_lower": true,
                                    "include_upper": true,
                                    "boost": 1.0
                                }
                            }
                        },
                        "path": "availability",
                        "ignore_unmapped": false,
                        "score_mode": "none",
                        "boost": 1.0
                    }
                }
            ],
            "adjust_pure_negative": true,
            "boost": 1.0
        }
    }
}

The above query shows empty hits when its executed, but when i use the dates that is indexed then i am able to get the records (ex if i specify the dates as "2020-04-12 to 2020-11-12" the worker B results are displayed). As per the range query it should have worked for my earlier case as-well if i am not wrong. Is there anything wrong in the approach that i followed. Please advice.

Answer 1

You have no worker whose start_date (!!) is between 2020-05-12 and 2020-06-12. I think you need to proceed differently for what you're trying to achieve.

Since you're trying to match on ranges, it would probably be easier to leverage the date_range field type. Your mapping should look like this:

PUT your-index
{
  "mappings": {
    "properties": {
      "availability": {
        "type": "date_range", 
        "format": "yyyy-MM-dd"
      }
    }
  }
}

Then you can index all your worker's availabilities like this:

{
  "worker": "A",
  "availability": [
    {
      "gte": "2020-01-12",
      "lte": "2020-02-12"
    },
    {
      "gte": "2020-04-12",
      "lte": "2020-05-12"
    }
  ]
}
{
  "worker": "B",
  "availability": [
    {
      "gte": "2020-04-12",
      "lte": "2020-11-12"
    }
  ]
}

And then you can perform the search you want like this:

{
  "query": {
    "range": {
      "availability": {
        "gte": "2020-05-12",
        "lte": "2020-06-12",
        "relation": "contains"
      }
    }
  }
}

And you'd find only worker B satisfies the condition.

UPDATE

Your query in Java needs to be like this:

    final BoolQueryBuilder queryBuilder = QueryBuilders.boolQuery();

    queryBuilder.must(QueryBuilders.matchQuery("name", "A"));

    RangeQueryBuilder availability = QueryBuilders.rangeQuery("availability")
           .gte(query.getStartDate())
           .lte(query.getEndDate());

    queryBuilder.must(availability);

    Pageable pageable = PageRequest.of(pageNumber, pageSize);

    // @formatter:off
    return new NativeSearchQueryBuilder()
            .withPageable(pageable)
            .withQuery(queryBuilder)
            .build();