Elasticsearch inverse range overlap query

Question

I have the following document:

{
  "blocked_availabilities": [
    {
      "start_time": "2016-05-26T19:30:00Z",
      "end_time": "2016-05-26T20:30:00Z"
    },
    {
      "start_time": "2017-05-26T16:00:00Z",
      "end_time": "2017-05-26T17:00:00Z",
    }
  ]
}

blocked_availabilities is a nested type in the mapping.

What I'm trying to do is match documents that do not overlap with a specified start and end time. I have the following query to do this (which doesn't work, of course):

{
  "query":{
    "bool":{
      "filter":{
        "nested":{
          "path":"blocked_availabilities",
          "query":{
            "bool":{
              "must_not":{
                "bool":{
                  "must":[
                    {
                      "range":{
                        "blocked_availabilities.start_time":{
                          "from":null,
                          "include_lower":true,
                          "include_upper":true,
                          "to":"2016-05-26T20:00:00Z"
                        }
                      }
                    },
                    {
                      "range":{
                        "blocked_availabilities.end_time":{
                          "from":"2016-05-26T19:00:00Z",
                          "include_lower":true,
                          "include_upper":true,
                          "to":null
                        }
                      }
                    }
                  ]
                }
              }
            }
          }
        }
      }
    }
  }
}

The problem seems to be that one of the nested documents doesn't match so the whole document is returned.

Is there a good way to do what I want? I expect this document to not be returned by my query since it overlaps with the first nested document.

Answer 1

One way to achieve this is to check it there is any nested object withing the overlapping period and do a must-not of the nested query. This would end up matching on only documents which do not contain any blocked_availabilities overlapping in the desired time period.

Example:

Setup Index

put test

put test/test/_mapping
{

      "properties": {
         "blocked_availabilities": {
            "type": "nested",
            "properties": {
               "start_time": {
                  "type": "date"
               },
               "end_time": {
                  "type": "date"
               }
            }
         }
      }
   }
}


put test/test/1
{
   "blocked_availabilities": [
      {
         "start_time": "2016-05-26T19:30:00Z",
         "end_time": "2016-05-26T20:30:00Z"
      },
      {
         "start_time": "2017-05-26T16:00:00Z",
         "end_time": "2017-05-26T17:00:00Z"
      }
   ]
}

Query:

put test/test/_search
{
   "query": {
      "bool": {
         "must_not": [
            {
               "nested": {
                  "path": "blocked_availabilities",
                  "query": {
                     "bool": {
                        "should": [
                           {
                              "range": {
                                 "blocked_availabilities.end_time": {
                                    "lte": "2016-05-26T20:00:00Z",
                                    "gte": "2016-05-26T19:00:00Z"
                                 }
                              }
                           },
                           {
                              "range": {
                                 "blocked_availabilities.start_time": {
                                    "lte": "2016-05-26T20:00:00Z",
                                    "gte": "2016-05-26T19:00:00Z"
                                 }
                              }
                           }
                        ]
                     }
                  }
               }
            }
         ]
      }
   }
}

Answer 2

You're trying to make not ((start > s) and (end < e)). Why not simply make (start > e) or (end < s)? Looks it should work if data is consistent.