Range filter on a nested array in Elasticsearch

Question

I might have to do some inline groovy to do this, but it "feels" like I should be able to do this by combining a nested query and a range query, but the fact I'm using an array of properties might be making this impossible.

Let's suppose I have a mapping for some products that I want to provide different delivery estimates for depending on where in the World you are, and I have cut-off dates and times that I know of in advance and want to preload once a week or two.

PUT /example_delivery
{
      "mappings": {
         "product": {
            "properties": {

               "delivery_times": {
                 "type": "nested",
                  "properties": {

                     "local_area": {
                        "type": "nested",
                        "properties": {
                           "cut_off_datetime": {
                              "type": "date", "format": "dateOptionalTime"
                           },
                           "get_it_by_date": {
                              "type": "date", "format": "dateOptionalTime"
                           }
                        }
                     },
                     "out_of_state": {
                       "type": "nested",
                        "properties": {
                           "cut_off_datetime": {
                              "type": "date", "format": "dateOptionalTime"
                           },
                           "get_it_by_date": {
                              "type": "date", "format": "dateOptionalTime"
                           }
                        }
                     }
                  }
               },
               "name": {
                  "type": "string"
               }
            }
         }
      }
   }

I then decide to load up some products:

PUT /example_delivery/product/1
{
  "name": "Product 1",
"delivery_times":[
  {"local_area":[
    {"cut_off_datetime": "2016-10-28T14:00:00", "get_it_by_date": "2016-10-29T13:00:00"},
    {"cut_off_datetime": "2016-10-31T14:00:00", "get_it_by_date": "2016-11-01T23:59:59"},
    {"cut_off_datetime": "2016-11-01T14:00:00", "get_it_by_date": "2016-11-02T23:59:59"},
    {"cut_off_datetime": "2016-11-02T14:00:00", "get_it_by_date": "2016-11-03T23:59:59"}
  ]},
  {"out_of_state":[
    {"cut_off_datetime": "2016-10-28T14:00:00", "get_it_by_date": "2016-11-01T23:59:59"},
    {"cut_off_datetime": "2016-10-31T14:00:00", "get_it_by_date": "2016-11-02T23:59:59"},
    {"cut_off_datetime": "2016-11-01T14:00:00", "get_it_by_date": "2016-11-03T23:59:59"},
    {"cut_off_datetime": "2016-11-02T14:00:00", "get_it_by_date": "2016-11-04T23:59:59"}
  ]}
]
}

PUT /example_delivery/product/2
{
  "name": "Product 2",
"delivery_times":[
  {"local_area":[
    {"cut_off_datetime": "2016-10-28T14:00:00", "get_it_by_date": "2016-11-29T13:00:00"},
    {"cut_off_datetime": "2016-10-31T14:00:00", "get_it_by_date": "2016-12-01T23:59:59"},
    {"cut_off_datetime": "2016-11-01T14:00:00", "get_it_by_date": "2016-12-02T23:59:59"},
    {"cut_off_datetime": "2016-11-02T14:00:00", "get_it_by_date": "2016-12-03T23:59:59"}
  ]},
  {"out_of_state":[
    {"cut_off_datetime": "2016-10-28T14:00:00", "get_it_by_date": "2016-12-01T23:59:59"},
    {"cut_off_datetime": "2016-10-31T14:00:00", "get_it_by_date": "2016-12-02T23:59:59"},
    {"cut_off_datetime": "2016-11-01T14:00:00", "get_it_by_date": "2016-12-03T23:59:59"},
    {"cut_off_datetime": "2016-11-02T14:00:00", "get_it_by_date": "2016-12-04T23:59:59"}
  ]}
]
}

You can see where I'm going with this, I hope. I want to be able to do a search, but apply a filter on the nested path of delivery_times.local_area or delivery_times.out_of_state to only return products for which there is at least one delivery_times object where cut_off_datetime is gte to "now", but the get_it_by_date is lte to the target time the user has specified in their filter. Even just getting matching products that have a cut-off time in the future results in an empty set:

GET example_delivery/_search
{
        "query" : { "match_all" : {} },
        "filter" : {
            "nested" : {
                "path" : "delivery_times",
                "filter" : {
        "range" : 
        {
        "delivery_times.local_area.cut_off_datetime" :           {
            "gte": "now"
          }
        } 
      } 
    }
  }
}

I'm wondering whether I need to be cleverer about querying across the array, and maybe, as I say, do this with inline groovy, but I'm wondering if there is a simpler way to do this I'm just not seeing right now.

Answer 1

You would have to go one level down to the final nested level. This query will work. You can also use inner_hits to get back matched nested documents.

{
  "query": {
    "nested": {
      "path": "delivery_times",
      "query": {
        "bool": {
          "should": [
            {
              "nested": {
                "path": "delivery_times.out_of_state",
                "query": {
                  "range": {
                    "delivery_times.out_of_state.cut_off_datetime": {
                      "gte": "now"
                    }
                  }
                }
              }
            },
            {
              "nested": {
                "path": "delivery_times.local_area",
                "query": {
                  "range": {
                    "delivery_times.local_area.cut_off_datetime": {
                      "gte": "now"
                    }
                  }
                }
              }
            }
          ]
        }
      }
    }
  }
}