Date Range queries not working with must_not in nested context

[titani0us (Wes M)]

Hello,

Running a must_not bool query on a nested object with dates returns values within the range even though the must_not operator was used.

Here is the example query:

{
"query" : {
	"bool" : {
		"must" : {
			"match" : { "is_active" : true }
		},
		"must_not" : {
			"nested" : {
				"path" : "nonAvailability",
				"query" : {
					"range" : {
						"nonAvailability.date" : {
							"gte" : "2018-11-05",
							"lt" : "2018-11-08",
							"format" : "YYYY-MM-DD"
						}
					}	
				}
				
			}
		    }
	    }
    }			
}

Here is an example document being returned even though there are dates that fall within the range clause:
{
"is_active" : true,
"nonAvailability:
{
"date": "2018-11-03"
},
{
"date": "2018-11-04"
},
{
"date": "2018-11-05"
},
{
"date": "2018-11-06"
},
{
"date": "2018-10-24"
}
}

The mapping for the field is specified like so:

"nonAvailability": {
      "type": "nested",
       "properties": {
             "date": {
                    "type": "date"
                 }
              }
          }

Has anyone seen this type of behavior before? Any thoughts on how to fix this?

[abdon (Abdon Pijpelink)]

The date formatter in the query is incorrect. Instead of YYYY-MM-DD you should use yyyy-MM-dd.

Or you could omit the format altogether, as the date format you are using will already be recognized as a valid date.

[titani0us (Wes M)]

Hi @abdon,

Thanks for your response!

This is a good catch - I removed it all together but the query still does not work as expected on ES6.4.

@Peter_Steenbergen actually suggested I do this via a parent-child relationship, but my understanding of nested documents was that they essentially achieved the same thing in a single document, so I'm confused as to why a child type would solve this query.

Are you able to reproduce the same result?

[abdon (Abdon Pijpelink)]

I cannot reproduce your issue. If I remove the format from the query, it works as it should, unless I am misunderstanding the problem.

The following only returns the document that does not contain any date within the specified range:

DELETE test

PUT test
{
  "mappings": {
    "_doc": {
      "properties": {
        "nonAvailability": {
          "type": "nested",
          "properties": {
            "date": {
              "type": "date"
            }
          }
        }
      }
    }
  }
}

PUT test/_doc/1
{
  "is_active": true,
  "nonAvailability": [
    {
      "date": "2018-11-03"
    },
    {
      "date": "2018-11-04"
    },
    {
      "date": "2018-11-05"
    },
    {
      "date": "2018-11-06"
    },
    {
      "date": "2018-10-24"
    }
  ]
}

PUT test/_doc/2
{
  "is_active": true,
  "nonAvailability": [
    {
      "date": "2019-11-03"
    },
    {
      "date": "2019-11-04"
    },
    {
      "date": "2019-11-05"
    },
    {
      "date": "2019-11-06"
    },
    {
      "date": "2019-10-24"
    }
  ]
}

GET test/_search
{
  "query": {
    "bool": {
      "must": {
        "match": {
          "is_active": true
        }
      },
      "must_not": {
        "nested": {
          "path": "nonAvailability",
          "query": {
            "range": {
              "nonAvailability.date": {
                "gte": "2018-11-05",
                "lt": "2018-11-08"
              }
            }
          }
        }
      }
    }
  }
}

[titani0us (Wes M)]

@abdon - Thanks for your response on this.

Not sure why, but this morning reviewing this again, the query appears to be performing correctly. The availability object is updated frequently. Is it possible old data was stilled cached and was being served by the query? I'm not super familiar with the inner workings of ElasticSearch.

Thanks again for all of your help.