Nested query with date range and exists returns incorrect result

I'm using ElasticSearch 2.3.0

Hi I have a document with this mapping

{
       "profiles_development_20180430093459665":{
          "mappings":{
             "profile":{
                "_all":{
                   "analyzer":"searchkick_index"
                },
                "dynamic_templates":[
                   {
                      "string_template":{
                         "mapping":{
                            "include_in_all":true,
                            "ignore_above":30000,
                            "index":"not_analyzed",
                            "type":"string",
                            "fields":{
                               "analyzed":{
                                  "analyzer":"searchkick_index",
                                  "index":"analyzed",
                                  "type":"string"
                               }
                            }
                         },
                         "match":"*",
                         "match_mapping_type":"string"
                      }
                   }
                ],
                "properties":{
                   "busy_periods":{
                      "type":"nested",
                      "properties":{
                         "end_date":{
                            "type":"date",
                            "format":"strict_date_optional_time||epoch_millis"
                         },
                         "start_date":{
                            "type":"date",
                            "format":"strict_date_optional_time||epoch_millis"
                         }
                      }
                   }
                }
             }
          }
       }
    }

I omitted the rest of the mapping for brevity.

I want to execute a query that returns documents that do not havebusy_periods in range or does not have busy_periods at all.

Here is an example of one profile:

{
   "_index":"profiles_development_20180430093459665",
   "_type":"profile",
   "_id":"1",
   "_score":1,
   "_source":{
      "id":1,
      "account_id":1,
      "registration_complete?":true,
      "busy_periods":[
         {
            "end_date":"2016-02-01",
            "start_date":"2016-01-01"
         },
         {
            "end_date":"2018-02-28",
            "start_date":"2018-01-01"
         }
      ]
   }
}

And the query I'm executing is:

{
   "query":{
      "bool":{
         "filter":[
            {
               "term":{
                  "account_id":1
               }
            },
            {
               "term":{
                  "registration_complete?":true
               }
            }
         ],
         "must":{
            "query":{
               "bool":{
                  "should":[
                     {
                        "query":{
                           "bool":{
                              "must_not":{
                                 "nested":{
                                    "path":"busy_periods",
                                    "query":{
                                       "exists":{
                                          "field":"busy_periods"
                                       }
                                    }
                                 }
                              }
                           }
                        }
                     },
                     {
                        "query":{
                           "nested":{
                              "path":"busy_periods",
                              "query":{
                                 "bool":{
                                    "must_not":[
                                       {
                                          "range":{
                                             "busy_periods.start_date":{
                                                "gte":"2018-01-01",
                                                "lt":"2018-02-28"
                                             }
                                          }
                                       },
                                       {
                                          "range":{
                                             "busy_periods.end_date":{
                                                "lte":"2018-02-28",
                                                "gt":"2018-01-01"
                                             }
                                          }
                                       }
                                    ]
                                 }
                              }
                           }
                        }
                     }
                  ]
               }
            }
         }
      }
   }
}

And the result brings back the profile from above when it shouldn't because one of the busy_periods is in range. Could you give me any advice please?

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.