Elasticsearch between query for 2 fields

Question

I would like to query ElasticSearch (with below data) with 3 bedrooms and expect the output to return ids: 20, 21, 22, 23, 24, 25.

|   id   | bedrooms_min | bedrooms_max |
________________________________________
|   20   |       1      |       4      |
|   21   |       1      |       3      |
|   22   |       3      |       4      |
|   23   |       2      |       3      |
|   24   |       3      |       3      |
|   25   |       2      |       5      |
|   26   |       4      |       5      |
|   27   |       1      |       1      |
|   28   |       1      |       2      |
|   29   |       2      |       2      |
________________________________________

because:

1. ID 20: 3 is between 1 and 4.
2. ID 21: bedrooms_max is equal to 3.
3. ID 22: bedrooms_min is equal to 3.
4. ID 23: bedrooms_max is equal to 3.
5. ID 24: bedrooms_min = bedrooms_max = 3
6. ID 25: 3 is between 2 and 5.
7. ID 26: 3 is out of range

Some sort of BETWEEN query I am looking for!

Is it possible? So far I tried with multi_match query but that's not giving me IDs: 20 and 25 and I doubt that this resultset can be achieved with multi_match query.

Answer 1

The following query would do the trick I think. What it does is basically find all documents having bedrooms_min <= 3 AND bedrooms_max >= 3 which is what you expect:

{
  "query": {
    "bool": {
      "filter": [
        {
          "range": {
            "bedrooms_min": {
              "lte": 3
            }
          }
        },
        {
          "range": {
            "bedrooms_max": {
              "gte": 3
            }
          }
        }
      ]
    }
  }
}

An even better solution would be to model your bedroom as a integer_range field so you can run a simple intersecting range query on it.