ElasticSearch - date range condition should match EXACTLY one item from date range array

Elastic Stack Elasticsearch
1

I am planning to store million of airbnb type apartments availabilty in elasticsearch .
Where availabilty is an array that contains nested objects (availability type is nested).
And each of those objects have date range, in which that apartment is available.

    apartments = [
  { 
    "_id": "kjty873yhekrg789e7r0n87e",
    "first_available_date": "2016-06-21",
    "availability": [
      {
        "start": "2016-06-21",
        "end": "2016-08-01"
      },
      {
        "start": "2016-08-20",
        "end": "2016-08-28"
      },
      {
        "start": "2016-10-03",
        "end": "2016-11-02"
      },
      { //This means it is available only for one day.
        "start": "2016-11-13",
        "end": "2016-11-13"
      },
      { 
        "start": "2016-11-28",
        "end": "2017-01-14"
      } 
    ],
    "apartment_metadata1": 56456,
    "apartment_metadata2": 8989,
    "status": "active"
  },
  { 
    "_id": "hgk87783iii86937jh",
    "first_available_date": "2016-06-09",
    "availability": [
      {
        "start": "2016-06-09",
        "end": "2016-07-02"
      },
      {
        "start": "2016-07-21",
        "end": "2016-12-19"
      },
      {
        "start": "2016-12-12",
        "end": "2017-07-02"
      }
    ],
    "apartment_metadata1": 23534,
    "apartment_metadata2": 24377,
    "status": "active"
  }
]

I would want to search apartments those are available for a specific date range (say 2016-08-20 to 2016-12-12). And that
range should fall inside one of the availability date ranges of various apartments.

So I want to write a query, something like:

{
  "query": {
    "bool": {
      "must": [
        {
          "range": { "first_available_date": {"lte": "2016-08-20"} },
          "match": { "status": "active" }
        }
      ]
      },
      "filter": [
        {
          "range": 
            {
              "apartments.availability.start": {"gte": "2016-08-20"}, 
              "apartments.availability.end": {"lte": "2016-12-12"} 
            }
        }
     ]
    }
  }
}

And above query will return me both apartments (with MULTIPLE availability objects matching the condition),
and that is incorrect, it should only return document with _id: hgk87783iii86937jh as there is EXACTLY one availability object matches the creiteria and that is {"start": "2016-07-21", "end": "2016-12-19"}. So in order to have correct result, the condition should be - there should be EXACTLY one availability object in apartment doc
that should match the condition. So how to enforce that there should be EXACTLY one match in the above query? Second question - is my query even correct?

2

See if this clears up the issue [1]

[1] https://www.elastic.co/guide/en/elasticsearch/guide/current/nested-objects.html

3

@Glen_Smith thanks for the link. But there I did not find anything that explain, how to find those documents where EXACTLY one nested object match my filter

4

Hey,

this looks rather like a duplicate of How to store disjoint date ranges and then search for a specific range

--Alex