ElasticSearch: Querying if today is between and series of start and end dates in list

Question

If I have some data with a field with multiple sets of start/end dates.. for example:

{
     id: 1,
     title: "My Title",
     availability[{start: 01-01-2020, end: 01-05-2020},{start: 02-01-2020, end: 02-22-2020}]
}

Is it possible in elasticsearch to build a query to check if today (or any given date) falls within any of the start/end date combinations in the list?

Or would I need to structure my data differently to make this work?

Previously, I was dealing with just one start and one end date and could store them as their own fields and do a gte, lte combination to check.

Update:

if I add them as nested fields. e.g.:

"avails" : {
    "type" : "nested",
    "properties" : {
        "availStart" : { "type" : "date" },
        "availEnd" : { "type" : "date" }
    }
}

If I do my search like this:

{
    "query": {
        "nested" : {
            "path" : "avails",
            "query" : {
                "term" : {
                    { "range" : {"avails.start" : {"lte": "now"}}},
                    { "range" : {"avails.end" : {"gt" : "now"}}}
                }
            }
        }
    }
}

will it evaluate this for each nested record and return any parent record with a child record that matches?

question from:https://stackoverflow.com/questions/65888730/elasticsearch-querying-if-today-is-between-and-series-of-start-and-end-dates-in

Answer 1

It's good that you've chosen nested fields. Now you just need to make sure the mappings, field names, and the query are all consistent.

1. The date mapping including the format:

PUT myindex
{
  "mappings": {
    "properties": {
      "avails": {
        "type": "nested",
        "properties": {
          "start": { "type": "date", "format": "MM-dd-yyyy" },
          "end": { "type": "date", "format": "MM-dd-yyyy" }
        }
      }
    }
  }
}

2. Syncing your doc

POST myindex/_doc
{
  "id": 1,
  "title": "My Title",
  "avails": [
    {
      "start":"01-01-2020",
      "end": "01-05-2020"
    },
    {
      "start": "02-01-2020",
      "end": "02-22-2020"
    }
  ]
}

3. And finally the query. Yours was malformed -- if you want a logical AND, you'll need to wrap the range queries in a bool + must:

POST myindex/_search
{
  "query": {
    "nested": {
      "path": "avails",
      "query": {
        "bool": {
          "must": [
            { "range" : {"avails.start" : {"lte": "now"}}},
            { "range" : {"avails.end" : {"gt" : "02-01-2020"}}}
          ]
        }
      }
    }
  }
}