I am using pretty old elasticsearch 2.5. I have the availability information of hotels in each doc. There is a field called "availabilities" whose mapping is as follows:
"availabilities":{
"type": "nested",
"dynamic": "strict",
"properties": {
"start": { "type": "date", "format": "yyyy-MM-dd" },
"end": { "type": "date", "format": "yyyy-MM-dd" }
}
}
One of the sample doc (stripped version) is as follows:
{
"name": "Seaside hotel",
"availabilities": [
{
"start": "2018-03-01",
"end": "2018-10-01"
},
{
"start": "2018-10-04",
"end": "2018-10-04"
},
{
"start": "2018-10-06",
"end": "2018-10-06"
},
{
"start": "2018-10-08",
"end": "2018-10-17"
},
{
"start": "2018-10-21",
"end": "2018-10-28"
},
{
"start": "2018-10-30",
"end": "2018-10-31"
},
{
"start": "2018-11-03",
"end": "2018-11-10"
},
{
"start": "2018-11-13",
"end": "2019-03-01"
},
{
"start": "2019-03-04",
"end": "2019-03-04"
},
{
"start": "2019-03-06",
"end": "2020-02-29"
}
]
}
I am trying to find all those hotels' doc, that has availability from "2018-10-01" (YYYY-MM-DD) to "2018-10-10"
. My search query is as follows:
where the start and end dates are compared in milliseconds 1539154800000 milliseconds = 2018-10-10 and 1538377200000 = 2018-10-01
GET hotels/_search
{
"query": {
"filtered": {
"filter": {
"query": {
"bool": {
"must": [{
"nested": {
"query": {
"bool": {
"must": [{
"script": {
"script": "return (doc['availabilities.end'].date.getMillis() <= 1539154800000 && doc['availabilities.start'].date.getMillis() >= 1538377200000)"
}
}]
}
},
"path": "availabilities"
}
}],
"must_not": null
}
}
}
}
}
}
When I run this query, I end up getting the above "Seaside hotel" in the result set, while it should not have been there because it doesn't have any availabilites from 2018-10-01 to 2018-10-10.
Now I changed my query to not use the script and here I am searching hotel which has availability from 2018-10-09 to 2018-10-16
GET hotels/_search
{
"query": {
"filtered": {
"filter": {
"query": {
"bool": {
"must": [{
"nested": {
"query": {
"bool": {
"must": [{
"range": {
"availabilities.end": {
"gte": "2018-10-16"
}
}
}, {
"range": {
"availabilities.start": {
"lte": "2018-10-09"
}
}
}]
}
},
"path": "availabilities"
}
}],
"must_not": null
}
}
}
}
}
}
and this query should have brought me "Seaside hotel" doc in the result as it has availability per search date, but the search did not give me this hotel.
My whole purpose is to have a query to search hotels in the specified availabilities date by the user. Any idea what am I doing wrong or how can I achieve my goal?