Elasticsearch: Match range in list of start and end dates

Question

What I want to achieve is to only return results if the dates provided in the query are in range of 1 single period. Right now I have a query that partially works, but it queries over all periods in the availability list. It matches because there exists a start date somewhere lower than or equal to 2020-12-25T00:00:00 and there exists an end date somewhere greater than or equal to 2020-12-22T00:00:00.

What I'd like is query that doesn't match the document provided below, since neither periods match the query I enter.

This is a part of my data model:

{
    "units": [
        {
            "availability": [
                {
                    "period": [
                        {
                            "start": "2020-09-12T00:00:00",
                            "end": "2020-10-31T00:00:00"
                        }
                    ]
                },
                {
                    "period": [
                        {
                            "start": "2021-04-03T00:00:00",
                            "end": "2021-04-24T00:00:00"
                        }
                    ]
                }
            ]
        }
    ]
}

This is my Elasticsearch query right now:

{ 
    "query": {
        "bool": {
            "must": [
                {
                    "range": {
                        "units.availability.period.start": {
                            "lte": "2020-12-25T00:00:00"
                        }
                    }
                },
                {
                    "range": {
                        "units.availability.period.end": {
                            "gte": "2020-12-22T00:00:00"
                        }
                    }
                }
            ]
        }
    }
}

Any suggestions would be of great help!

Answer 1

I suspect that availability needs to be nested -- otherwise your array objects' values get flattened and the connection between your starts & ends is lost.

---

In short, drop the index, change the mapping, reindex and then use something along the lines of

{
  "query": {
    "bool": {
      "must": [
        {
          "nested": {
            "path": "units.availability",
            "query": {
              "bool": {
                "must": [
                  {
                    "range": {
                      "units.availability.period.start": {
                        "lte": "2020-12-25T00:00:00"
                      }
                    }
                  },
                  {
                    "range": {
                      "units.availability.period.end": {
                        "gte": "2020-12-22T00:00:00"
                      }
                    }
                  }
                ]
              }
            }
          }
        }
      ]
    }
  }
}

BTW you may also want to think about making units themselves nested -- they too look like standalone entities!