Query to get available dates using start and end date

Question

I’m trying to create a query which returns available products with no reservation at that date (or date range) or no reservations at all. It’s driving me crazy.

Here is my current mapping with index settings:

{
  "development_product_instances" : {
    "aliases" : { },
    "mappings" : {
      "product_instance" : {
        "properties" : {
          "reservations" : {
            "type" : "nested",
            "properties" : {
              "end_date" : {
                "type" : "date",
                "format" : "yyyy-MM-dd"
              },
              "start_date" : {
                "type" : "date",
                "format" : "yyyy-MM-dd"
              }
            }
          }
        }
      }
    },
    "settings" : {
      "index" : {
        "creation_date" : "1503327829680",
        "number_of_shards" : "5",
        "number_of_replicas" : "1",
        "uuid" : "9b9BhF-ITta2dlCKRLrnfA",
        "version" : {
          "created" : "2040499"
        }
      }
    },
    "warmers" : { }
  }
}

And the query:

  {
    bool: {
      should: [
        {
          nested: {
            path: "reservations",
            filter: {
              bool: {
                must_not: [
                  {
                    range:
                      {
                        "reservations.start_date":
                         {
                           gte: start_date,
                           lte: end_date
                         }
                      }
                  },
                  {
                    range:
                      {
                        "reservations.end_date":
                         {
                           gte: start_date,
                           lt: end_date
                         }
                      }
                  }
                ]
              }
            }
          }
        },
        {
          not: {
            nested: {
              path: "reservations",
              filter: {
                match_all: {}
              }
            }
          }
        }
      ]
    }
  }

When there is more than one reservation it returns all.

I hope someone can see the bug in there. Maybe i'm missing something in the bigger picture.

Answer 1

Your problem is that the must_not is inside the nested query. That means that if it matches for any of the nested reservations, then the parent document matches. So when there are multiple reservations, unless the range you're querying overlaps all the existing reservations, you get a match. You can rewrite it like this (note that this query also matches when reservations is empty):

{
  "query": {
    "bool": {
      "must_not": {
        "nested": {
          "path": "reservations",
          "query": {
            "bool": {
              "should": [
                {
                  "range": {
                    "reservations.start_date": {
                      "gte": start_date,
                      "lt": end_date
                    }
                  }
                },
                {
                  "range": {
                    "reservations.end_date": {
                      "gte": start_date,
                      "lt": end_date
                    }
                  }
                }, 
                {
                  "bool": {
                    "must": [
                      {
                        "range": {
                           "reservations.start_date": {
                             "lt": start_date
                           }
                        }
                      },
                      {
                        "range": {
                           "reservations.end_date": {
                             "gt": end_date
                           }
                        }
                      }
                    ]
                  } 
                }
              ]
            }
          }
        }
      }
    }
  }
}