We made a pretty big leap recently in elasticsearch versions and one issue we've run into is that when using query_string's on Date fields we get items which are outside the range of the deadline date.
I have the following mapping:
POST /license
{
"settings": {
"index": {
"routing": {
"allocation": {
"include": {
"_tier_preference": "data_content"
}
}
},
"number_of_shards": "1",
"provided_name": "license",
"creation_date": "1660225511618",
"analysis": {
"filter": {
"custom_asciifolding": {
"type": "asciifolding",
"preserve_original": "true"
},
"autocomplete_filter": {
"type": "edgeNGram",
"min_gram": "1",
"max_gram": "20"
}
},
"analyzer": {
"autocomplete": {
"filter": [
"lowercase",
"custom_asciifolding",
"autocomplete_filter"
],
"tokenizer": "whitespace"
},
"autocomplete_search": {
"filter": [
"lowercase"
],
"tokenizer": "whitespace"
},
"case_insensitive_sort": {
"filter": [
"lowercase"
],
"tokenizer": "keyword"
}
}
},
"number_of_replicas": "1",
"uuid": "aOmo-WQUQO62mmamS6BVyw",
"version": {
"created": "7160399"
}
}
},
"mappings": {
"properties": {
"companyName": {
"type": "text",
"fields": {
"raw": {
"type": "text",
"analyzer": "case_insensitive_sort",
"fielddata": true
}
},
"analyzer": "autocomplete",
"search_analyzer": "autocomplete_search"
},
"contactEmail": {
"type": "text",
"fields": {
"raw": {
"type": "text",
"analyzer": "case_insensitive_sort",
"fielddata": true
}
},
"analyzer": "autocomplete",
"search_analyzer": "autocomplete_search"
},
"contactName": {
"type": "text",
"fields": {
"raw": {
"type": "text",
"analyzer": "case_insensitive_sort",
"fielddata": true
}
},
"analyzer": "autocomplete",
"search_analyzer": "autocomplete_search"
},
"contactPhone": {
"type": "text",
"fields": {
"raw": {
"type": "text",
"analyzer": "case_insensitive_sort",
"fielddata": true
}
},
"analyzer": "autocomplete",
"search_analyzer": "autocomplete_search"
},
"contactRole": {
"type": "text",
"fields": {
"raw": {
"type": "text",
"analyzer": "case_insensitive_sort",
"fielddata": true
}
},
"analyzer": "autocomplete",
"search_analyzer": "autocomplete_search"
},
"created": {
"type": "date",
"format": "YYYY-mm-dd"
},
"createdAt": {
"type": "date"
},
"currencyAndBudget": {
"type": "text",
"fields": {
"raw": {
"type": "text",
"analyzer": "case_insensitive_sort",
"fielddata": true
}
},
"analyzer": "autocomplete",
"search_analyzer": "standard"
},
"deadline": {
"type": "date",
"format": "YYYY-mm-dd"
},
"deadlineDate": {
"type": "date"
},
"isOriginalCover": {
"type": "boolean"
},
"licenseRequestStatus": {
"type": "text"
},
"projectDetails": {
"type": "text",
"analyzer": "autocomplete",
"search_analyzer": "autocomplete_search"
},
"publicCatalogId": {
"type": "integer"
},
"songMediaDisplayArtist": {
"type": "text",
"analyzer": "autocomplete",
"search_analyzer": "autocomplete_search"
},
"songMediaTitle": {
"type": "text",
"fields": {
"raw": {
"type": "text",
"analyzer": "case_insensitive_sort",
"fielddata": true
}
},
"analyzer": "autocomplete",
"search_analyzer": "standard"
},
"songTitle": {
"type": "text",
"fields": {
"raw": {
"type": "text",
"analyzer": "case_insensitive_sort",
"fielddata": true
}
},
"analyzer": "autocomplete",
"search_analyzer": "standard"
},
"songWritersList": {
"type": "text",
"analyzer": "autocomplete",
"search_analyzer": "standard"
},
"subscriptionId": {
"type": "integer"
},
"syncMediaTypes": {
"type": "text"
},
"syncProjectTitle": {
"type": "text",
"fields": {
"raw": {
"type": "text",
"analyzer": "case_insensitive_sort",
"fielddata": true
}
},
"analyzer": "autocomplete",
"search_analyzer": "standard"
},
"termDetail": {
"type": "text",
"analyzer": "autocomplete",
"search_analyzer": "autocomplete_search"
},
"termType": {
"type": "text"
},
"territoryNames": {
"type": "text",
"analyzer": "autocomplete",
"search_analyzer": "autocomplete_search"
},
"updatedAt": {
"type": "date"
},
"userId": {
"type": "integer"
}
}
}
}
}
POST /license/_doc
{
"userId": 319,
"subscriptionId": 105,
"publicCatalogId": 22,
"syncProjectTitle": "test1",
"songTitle": "test1",
"songWritersList": "test1",
"projectDetails": "test1",
"isOriginalCover": false,
"songMediaTitle": "test1",
"songMediaDisplayArtist": "test1",
"currencyAndBudget": "USD100",
"deadline": "2001-01-01",
"deadlineDate": "2001-01-01T06:00:00-06:00",
"contactName": "test1",
"contactRole": "test1",
"contactEmail": "test1@test.com",
"contactPhone": "1234567890",
"companyName": "test1",
"termType": "7",
"termDetail": "test1",
"territoryNames": null,
"syncMediaTypes": [
"Video Game"
],
"licenseRequestStatus": "pending",
"created": "2022-08-29",
"createdAt": "2022-08-29T09:20:45-05:00",
"updatedAt": "2022-08-29T09:20:45-05:00"
}
POST /license/_doc
{
"userId": 319,
"subscriptionId": 105,
"publicCatalogId": 22,
"syncProjectTitle": "test2",
"songTitle": "test2",
"songWritersList": "",
"projectDetails": "test2",
"isOriginalCover": true,
"songMediaTitle": "test2",
"songMediaDisplayArtist": "test2",
"currencyAndBudget": "USD200",
"deadline": "2001-01-02",
"deadlineDate": "2001-01-02T09:00:00-06:00",
"contactName": "test2",
"contactRole": "test2",
"contactEmail": "test2@test.com",
"contactPhone": "1234567891",
"companyName": "test2",
"termType": "14",
"termDetail": "test2",
"territoryNames": null,
"syncMediaTypes": [
"Internet "
],
"licenseRequestStatus": "pending",
"created": "2022-08-29",
"createdAt": "2022-08-29T09:20:46-05:00",
"updatedAt": "2022-08-29T09:20:46-05:00"
}
One of these license docs has a deadline of 2001-01-01 while another has a deadline of 2001-01-02. I'm trying to do the following request to retrieve all docs with a 2001-01-01 deadline and then filter down to a specific user and subscription:
GET /license/_search
{
"query": {
"bool": {
"must": [
{
"query_string": {
"query": "deadline:2001-01-01",
"default_field": "term",
"allow_leading_wildcard": false,
"default_operator": "AND"
}
}
],
"filter": [
{
"bool": {
"should": [
{
"term": {
"userId": 319
}
},
{
"term": {
"subscriptionId": 105
}
}
]
}
}
]
}
}
}
This request when run returns both the documents above and I'm not sure why. I came across some information that pointed me to the fact that elasticsearch uses UTC in the background to convert the date times and then search on the UTC values. Using _explain I found that it's matching on deadline:[978307200000 TO 978390119999], both of which correspond to times within 2001-01-01 so I'm not sure why the search is matching this deadline outside it's range.
Thanks in advance for any help, if I made an errors in my formatting or need to post any additional information please let me know.
