Elasticsearch iterate over range query result in nested object

Question

I have a elasticsearch index which is having a nested object called availability which has a date and a boolean field , the mapping schema is as follows

    {
       "hotel_nested" : {
        "mappings" : {
          "doc" : {
            "properties" : {
               "amenities" : {
                   "type" : "text",
                   "fields" : {
                   "keyword" : {
                        "type" : "keyword",
                        "ignore_above" : 256
                         }     
                     }
               },
              "availability" : {
                    "type" : "nested",
                     "properties" : {
                            "date" : {
                               "type" : "date",
                               "fields" : {
                               "keyword" : {
                                 "type" : "keyword",
                                 "ignore_above" : 256
                               }
                           },
                            "format" : "MM/dd/yyyy HH:mm:ss||MM/dd/yyyy||epoch_millis"
          },
                          "status" : {
                              "type" : "boolean"
                            }
        }
      },
      "dailyRate" : {
        "type" : "text",
        "fields" : {
          "keyword" : {
            "type" : "keyword",
            "ignore_above" : 256
          }
        }
      },
      "destination" : {
        "type" : "text",
        "fields" : {
          "keyword" : {
            "type" : "keyword",
            "ignore_above" : 256
          }
        }
      },
      "hotelName" : {
        "type" : "text",
        "fields" : {
          "keyword" : {
            "type" : "keyword",
            "ignore_above" : 256
          }
        }
      },
      "location" : {
        "properties" : {
          "lat" : {
            "type" : "float"
          },
          "lon" : {
            "type" : "float"
          }
        }
      },
      "maxOccupancy" : {
        "type" : "long"
      },
      "propertyType" : {
        "type" : "text",
        "fields" : {
          "keyword" : {
            "type" : "keyword",
            "ignore_above" : 256
          }
        }
      },
      "roomType" : {
        "type" : "text",
        "fields" : {
          "keyword" : {
            "type" : "keyword",
            "ignore_above" : 256
          }
        }
      },
      "starRating" : {
        "type" : "float"
         }
       }
     }
   }
 }
}

Here is one of the object

    {
    "_index" : "hotel_nested",
    "_type" : "doc",
    "_id" : "14",
    "_score" : 1.0,
    "_source" : {
      "roomType" : "Executive",
      "destination" : "Albuquerque",
      "maxOccupancy" : 4,
      "starRating" : 6.8,
      "hotelName" : "Lotte Hotel Seoul",
      "amenities" : [
        "Spa",
        "Internet",
        "Free parking",
        "Air conditioning",
        "Laundry Service",
        "Business Services"
      ],
      "location" : {
        "lat" : 56.76755,
        "lon" : -110.75792
      },
      "propertyType" : "Hotel",
      "dailyRate" : "$178.96",
      "availability" : [
        {
          "date" : "10/1/2017",
          "status" : true
        },
        {
          "date" : "10/2/2017",
          "status" : true
        },
        {
          "date" : "10/3/2017",
          "status" : true
        },
        {
          "date" : "10/4/2017",
          "status" : false
        },
        {
          "date" : "10/5/2017",
          "status" : false
        },
        {
          "date" : "10/6/2017",
          "status" : false
        },
        {
          "date" : "10/7/2017",
          "status" : true
        },
        {
          "date" : "10/8/2017",
          "status" : true
        },
        {
          "date" : "10/9/2017",
          "status" : false
        },
        {
          "date" : "10/10/2017",
          "status" : false
        },
        {
          "date" : "10/11/2017",
          "status" : true
        },
        {
          "date" : "10/12/2017",
          "status" : false
        },
        {
          "date" : "10/13/2017",
          "status" : false
        },
        {
          "date" : "10/14/2017",
          "status" : true
        },
        {
          "date" : "10/15/2017",
          "status" : true
        },
        {
          "date" : "10/16/2017",
          "status" : true
        },
        {
          "date" : "10/17/2017",
          "status" : false
        },
        {
          "date" : "10/18/2017",
          "status" : true
        },
        {
          "date" : "10/19/2017",
          "status" : false
        },
        {
          "date" : "10/20/2017",
          "status" : true
        },
        {
          "date" : "10/21/2017",
          "status" : false
        },
        {
          "date" : "10/22/2017",
          "status" : true
        },
        {
          "date" : "10/23/2017",
          "status" : true
        },
        {
          "date" : "10/24/2017",
          "status" : true
        },
        {
          "date" : "10/25/2017",
          "status" : false
        },
        {
          "date" : "10/26/2017",
          "status" : false
        },
        {
          "date" : "10/27/2017",
          "status" : false
        },
        {
          "date" : "10/28/2017",
          "status" : true
        },
        {
          "date" : "10/29/2017",
          "status" : true
        },
        {
          "date" : "10/30/2017",
          "status" : true
        },
        {
          "date" : "10/31/2017",
          "status" : true
        },
        {
          "date" : "11/1/2017",
          "status" : true
        },
        {
          "date" : "11/2/2017",
          "status" : false
        },
        {
          "date" : "11/3/2017",
          "status" : false
        },
        {
          "date" : "11/4/2017",
          "status" : false
        },
        {
          "date" : "11/5/2017",
          "status" : false
        },
        {
          "date" : "11/6/2017",
          "status" : true
        },
        {
          "date" : "11/7/2017",
          "status" : false
        },
        {
          "date" : "11/8/2017",
          "status" : false
        },
        {
          "date" : "11/9/2017",
          "status" : false
        },
        {
          "date" : "11/10/2017",
          "status" : false
        },
        {
          "date" : "11/11/2017",
          "status" : false
        },
        {
          "date" : "11/12/2017",
          "status" : false
        },
        {
          "date" : "11/13/2017",
          "status" : false
        },
        {
          "date" : "11/14/2017",
          "status" : true
        },
        {
          "date" : "11/15/2017",
          "status" : true
        },
        {
          "date" : "11/16/2017",
          "status" : false
        },
        {
          "date" : "11/17/2017",
          "status" : true
        },
        {
          "date" : "11/18/2017",
          "status" : false
        },
        {
          "date" : "11/19/2017",
          "status" : true
        },
        {
          "date" : "11/20/2017",
          "status" : true
        },
        {
          "date" : "11/21/2017",
          "status" : true
        },
        {
          "date" : "11/22/2017",
          "status" : true
        },
        {
          "date" : "11/23/2017",
          "status" : false
        },
        {
          "date" : "11/24/2017",
          "status" : false
        },
        {
          "date" : "11/25/2017",
          "status" : false
        },
        {
          "date" : "11/26/2017",
          "status" : true
        },
        {
          "date" : "11/27/2017",
          "status" : true
        },
        {
          "date" : "11/28/2017",
          "status" : false
        },
        {
          "date" : "11/29/2017",
          "status" : false
        },
        {
          "date" : "11/30/2017",
          "status" : false
        },
        {
          "date" : "12/1/2017",
          "status" : true
        },
        {
          "date" : "12/2/2017",
          "status" : true
        },
        {
          "date" : "12/3/2017",
          "status" : true
        },
        {
          "date" : "12/4/2017",
          "status" : true
        },
        {
          "date" : "12/5/2017",
          "status" : true
        },
        {
          "date" : "12/6/2017",
          "status" : true
        },
        {
          "date" : "12/7/2017",
          "status" : false
        },
        {
          "date" : "12/8/2017",
          "status" : false
        },
        {
          "date" : "12/9/2017",
          "status" : true
        },
        {
          "date" : "12/10/2017",
          "status" : false
        },
        {
          "date" : "12/11/2017",
          "status" : true
        },
        {
          "date" : "12/12/2017",
          "status" : false
        },
        {
          "date" : "12/13/2017",
          "status" : true
        },
        {
          "date" : "12/14/2017",
          "status" : true
        },
        {
          "date" : "12/15/2017",
          "status" : true
        },
        {
          "date" : "12/16/2017",
          "status" : false
        },
        {
          "date" : "12/17/2017",
          "status" : true
        },
        {
          "date" : "12/18/2017",
          "status" : true
        },
        {
          "date" : "12/19/2017",
          "status" : false
        },
        {
          "date" : "12/20/2017",
          "status" : true
        },
        {
          "date" : "12/21/2017",
          "status" : true
        },
        {
          "date" : "12/22/2017",
          "status" : false
        },
        {
          "date" : "12/23/2017",
          "status" : false
        },
        {
          "date" : "12/24/2017",
          "status" : false
        },
        {
          "date" : "12/25/2017",
          "status" : false
        },
        {
          "date" : "12/26/2017",
          "status" : false
        },
        {
          "date" : "12/27/2017",
          "status" : false
        },
        {
          "date" : "12/28/2017",
          "status" : false
        },
        {
          "date" : "12/29/2017",
          "status" : false
        },
        {
          "date" : "12/30/2017",
          "status" : true
        },
        {
          "date" : "12/31/2017",
          "status" : true
        }
      ]
    }
  }

My issue is that I want to search between two dates and find out if the hotel availability status is true for all dates between the two dates with also matching the city name at the crude level,i have other criteria to search also which i think i can handle,but finding true status for all dates between two dates is bugging me

eg search query parameters city Albuquerque,start date 10/22/2017 and end date 10/24/2017

I have created a query which is giving undesired results, my query is

        {
         "query": {
         "bool": {
            "must":[ 
        {
                 "match": {
                      "destination": { 
                            "query":    "Albuquerque" ,
                            "operator": "and"
                         }
                  }
            }

        ],
        "filter": {
              "bool": { 
                 "must": [  {
                "nested": {
                    "path": "availability", 
                        "query": {
                           "bool": {
                             "must": [ 
                                  {
                                 "range": {
                                     "availability.date":{ "gte": "10/22/2017",
                                        "lte":"10/24/2017"}
                }
              },
              {
                "term": {
                  "availability.status": "true"
                }
              }
            ]
          }
        }
      }
    }
            ]
      }
     }
   }
 }
 }

The first hit of this query is

        {
            "_index": "hotel_nested",
            "_type": "doc",
            "_id": "305",
            "_score": 4.815987,
            "_source": {
                "roomType": "Executive",
                "destination": "Albuquerque",
                "maxOccupancy": 2,
                "starRating": 4.2,
                "hotelName": "Sheraton San Diego Hotel & Marina",
                "amenities": [
                    "Kitchen",
                    "Air conditioning",
                    "Laundry Service",
                    "Business Services",
                    "Free parking",
                    "Spa"
                ],
                "location": {
                    "lat": 54.92887,
                    "lon": -101.31256
                },
                "propertyType": "Hotel",
                "dailyRate": "$462.59",
                "availability": [
                    {
                        "date": "10/1/2017",
                        "status": false
                    },
                    {
                        "date": "10/2/2017",
                        "status": true
                    },
                    {
                        "date": "10/3/2017",
                        "status": false
                    },
                    {
                        "date": "10/4/2017",
                        "status": true
                    },
                    {
                        "date": "10/5/2017",
                        "status": true
                    },
                    {
                        "date": "10/6/2017",
                        "status": true
                    },
                    {
                        "date": "10/7/2017",
                        "status": true
                    },
                    {
                        "date": "10/8/2017",
                        "status": false
                    },
                    {
                        "date": "10/9/2017",
                        "status": false
                    },
                    {
                        "date": "10/10/2017",
                        "status": true
                    },
                    {
                        "date": "10/11/2017",
                        "status": true
                    },
                    {
                        "date": "10/12/2017",
                        "status": false
                    },
                    {
                        "date": "10/13/2017",
                        "status": true
                    },
                    {
                        "date": "10/14/2017",
                        "status": false
                    },
                    {
                        "date": "10/15/2017",
                        "status": true
                    },
                    {
                        "date": "10/16/2017",
                        "status": false
                    },
                    {
                        "date": "10/17/2017",
                        "status": true
                    },
                    {
                        "date": "10/18/2017",
                        "status": false
                    },
                    {
                        "date": "10/19/2017",
                        "status": false
                    },
                    {
                        "date": "10/20/2017",
                        "status": true
                    },
                    {
                        "date": "10/21/2017",
                        "status": true
                    },
                    {
                        "date": "10/22/2017",
                        "status": true
                    },
                    {
                        "date": "10/23/2017",
                        "status": false
                    },
                    {
                        "date": "10/24/2017",
                        "status": false
                    },
                    {
                        "date": "10/25/2017",
                        "status": false
                    },
                    {
                        "date": "10/26/2017",
                        "status": true
                    },
                    {
                        "date": "10/27/2017",
                        "status": true
                    },
                    {
                        "date": "10/28/2017",
                        "status": false
                    },
                    {
                        "date": "10/29/2017",
                        "status": true
                    },
                    {
                        "date": "10/30/2017",
                        "status": false
                    },
                    {
                        "date": "10/31/2017",
                        "status": false
                    },
                    {
                        "date": "11/1/2017",
                        "status": false
                    },
                    {
                        "date": "11/2/2017",
                        "status": false
                    },
                    {
                        "date": "11/3/2017",
                        "status": false
                    },
                    {
                        "date": "11/4/2017",
                        "status": false
                    },
                    {
                        "date": "11/5/2017",
                        "status": false
                    },
                    {
                        "date": "11/6/2017",
                        "status": true
                    },
                    {
                        "date": "11/7/2017",
                        "status": false
                    },
                    {
                        "date": "11/8/2017",
                        "status": true
                    },
                    {
                        "date": "11/9/2017",
                        "status": true
                    },
                    {
                        "date": "11/10/2017",
                        "status": true
                    },
                    {
                        "date": "11/11/2017",
                        "status": true
                    },
                    {
                        "date": "11/12/2017",
                        "status": false
                    },
                    {
                        "date": "11/13/2017",
                        "status": true
                    },
                    {
                        "date": "11/14/2017",
                        "status": false
                    },
                    {
                        "date": "11/15/2017",
                        "status": true
                    },
                    {
                        "date": "11/16/2017",
                        "status": true
                    },
                    {
                        "date": "11/17/2017",
                        "status": false
                    },
                    {
                        "date": "11/18/2017",
                        "status": true
                    },
                    {
                        "date": "11/19/2017",
                        "status": false
                    },
                    {
                        "date": "11/20/2017",
                        "status": true
                    },
                    {
                        "date": "11/21/2017",
                        "status": false
                    },
                    {
                        "date": "11/22/2017",
                        "status": false
                    },
                    {
                        "date": "11/23/2017",
                        "status": false
                    },
                    {
                        "date": "11/24/2017",
                        "status": false
                    },
                    {
                        "date": "11/25/2017",
                        "status": false
                    },
                    {
                        "date": "11/26/2017",
                        "status": false
                    },
                    {
                        "date": "11/27/2017",
                        "status": false
                    },
                    {
                        "date": "11/28/2017",
                        "status": false
                    },
                    {
                        "date": "11/29/2017",
                        "status": false
                    },
                    {
                        "date": "11/30/2017",
                        "status": true
                    },
                    {
                        "date": "12/1/2017",
                        "status": false
                    },
                    {
                        "date": "12/2/2017",
                        "status": false
                    },
                    {
                        "date": "12/3/2017",
                        "status": false
                    },
                    {
                        "date": "12/4/2017",
                        "status": true
                    },
                    {
                        "date": "12/5/2017",
                        "status": true
                    },
                    {
                        "date": "12/6/2017",
                        "status": false
                    },
                    {
                        "date": "12/7/2017",
                        "status": true
                    },
                    {
                        "date": "12/8/2017",
                        "status": false
                    },
                    {
                        "date": "12/9/2017",
                        "status": true
                    },
                    {
                        "date": "12/10/2017",
                        "status": true
                    },
                    {
                        "date": "12/11/2017",
                        "status": false
                    },
                    {
                        "date": "12/12/2017",
                        "status": true
                    },
                    {
                        "date": "12/13/2017",
                        "status": false
                    },
                    {
                        "date": "12/14/2017",
                        "status": true
                    },
                    {
                        "date": "12/15/2017",
                        "status": false
                    },
                    {
                        "date": "12/16/2017",
                        "status": true
                    },
                    {
                        "date": "12/17/2017",
                        "status": true
                    },
                    {
                        "date": "12/18/2017",
                        "status": false
                    },
                    {
                        "date": "12/19/2017",
                        "status": false
                    },
                    {
                        "date": "12/20/2017",
                        "status": false
                    },
                    {
                        "date": "12/21/2017",
                        "status": true
                    },
                    {
                        "date": "12/22/2017",
                        "status": true
                    },
                    {
                        "date": "12/23/2017",
                        "status": false
                    },
                    {
                        "date": "12/24/2017",
                        "status": false
                    },
                    {
                        "date": "12/25/2017",
                        "status": true
                    },
                    {
                        "date": "12/26/2017",
                        "status": false
                    },
                    {
                        "date": "12/27/2017",
                        "status": true
                    },
                    {
                        "date": "12/28/2017",
                        "status": false
                    },
                    {
                        "date": "12/29/2017",
                        "status": false
                    },
                    {
                        "date": "12/30/2017",
                        "status": true
                    },
                    {
                        "date": "12/31/2017",
                        "status": false
                    }
                ]
            }
        }

In this hit status for dates 10/23/2017 and 10/24/2017 is false Can someone guide me in the correct direction or maybe give a example search query to solve this problem

Answer 1

You need to put your nested query under must clause. Add the inner_hits key at the bottom of the nested query AND enable specific _source fields at the head (because we don't care about the rest of the sub-documents who don't match the nested query) as follows:

{
  "_source": [
    "hotelName",
    "dailyRate"
  ],
  "query": {
    "bool": {
      "must": [
        {
          "match": {
            "destination": {
              "query": "Albuquerque",
              "operator": "and"
            }
          }
        },
        {
          "nested": {
            "path": "availability",
            "query": {
              "bool": {
                "must": [
                  {
                    "range": {
                      "availability.date": {
                        "gte": "10/22/2017",
                        "lte": "10/24/2017"
                      }
                    }
                  },
                  {
                    "term": {
                      "availability.status": "true"
                    }
                  }
                ]
              }
            },
            "inner_hits": {
              "size": 10
            }
          }
        }
      ]
    }
  }
}

Pay attention that now you'll get only nested-documents that comply with the conditions, e.g for your example the result would be:

{
  "_index": "hotel_nested",
  "_type": "doc",
  "_id": "305",
  "_score": 4.815987,
  "_source": {
    "hotelName": "Sheraton San Diego Hotel & Marina",
    "dailyRate": "$462.59"
  },
  "inner_hits": {
    "availability": {
      "hits": {
        "total": 1,
        "hits": [
          {
            "date": "10/22/2017",
            "status": true
          }
        ]
      }
    }
  }
}

So now, you will have to check whether all the days in the range 22-24 in October are present.

One shortcut way is to mark "inner_hits":{"size":0} and just look at the result and check if the number of inner_hits equals to the number of the days in the range of 22-24 in October:

"inner_hits"."availability"."hits"."total" == 3