I am trying to filter hotel rooms by price range in Elastic Search. The rooms have a default nightly price and also custom prices can be set for specific days.
I'm storing the nightlyPrice
and a nested object for custom prices together with the dates. The mapping is smt. like:
{
"adverts": {
"mappings": {
"advert": {
"properties": {
"nightlyPrice": {"type": "float"},
"customPrices": {
"type": "nested",
"properties": {
"date": {"type": "date"},
"price": {"type": "float"}
}
}
}
}
}
}
}
For example I want to get the rooms within the price range of 100 and 200$ between the dates 1st and 7th of July.
So I came up with this logic:
- Either
customPrices.date
must be between 2019-07-01 and 2019-07-07 andcustomPrices.price
between 100 and 200. - or the
nightlyPrice
must be between 100 and 200 and nocustomPrices.date
is set between 05 and 07 July.
However I couldn't be able to apply this logic to Elastic Search, nested objects / queries are kinda tricky I guess.
This is the final query I came up with:
{
"query": {
"bool": {
"filter": [
{
"term": {
"status": "active"
}
}
],
"must": [
{
"bool": {
"should": [
{
"nested": {
"path": "customPrices",
"query": {
"bool": {
"must": [
{
"range": {
"date": {
"from": "2019-07-01",
"to": "2019-07-07"
}
}
},
{
"range": {
"price": {
"from": 100,
"to": 200
}
}
}
]
}
}
}
},
{
"bool": {
"must": [
{
"range": {
"nightlyPrice": {
"from": 100,
"to": 200
}
}
}
],
"must_not": [
{
"nested": {
"path": "customPrices",
"query": {
"range": {
"customPrices.date": {
"from": "2019-07-01",
"to": "2019-07-07"
}
}
}
}
}
]
}
}
]
}
}
]
}
}
}
The problem with this query is if customPrices.date matches the date range it never matches the document no matter the price range is. I experimented with 1 - 100000$ price range and it still doesn't match.
Tried to use the explain API to understand why a specific document didn't match but I don't understand it, it says user requested match_none
query but there's this should
query so it should match the nested query (first one):
{
"_index": "adverts",
"_type": "advert",
"_id": "13867",
"matched": false,
"explanation": {
"value": 0.0,
"description": "Failure to meet condition(s) of required/prohibited clause(s)",
"details": [
{
"value": 0.0,
"description": "no match on required clause (+(ToParentBlockJoinQuery (MatchNoDocsQuery(\"User requested \"match_none\" query.\")) (+nightlyPrice:[100.0 TO 200.0] -ToParentBlockJoinQuery (customListingPrices.date:[1561939200000 TO 1562543999999]))) #status:active",
"details": [
{
"value": 0.0,
"description": "Failure to meet condition(s) of required/prohibited clause(s)",
"details": [
{
"value": 0.0,
"description": "no match on required clause (ToParentBlockJoinQuery (MatchNoDocsQuery(\"User requested \"match_none\" query.\")) (+nightlyPrice:[100.0 TO 200.0] -ToParentBlockJoinQuery (customListingPrices.date:[1561939200000 TO 1562543999999])))",
"details": [
{
"value": 0.0,
"description": "No matching clauses",
"details": []
}
]
},
{
"value": 0.0,
"description": "match on required clause, product of:",
"details": [
{
"value": 0.0,
"description": "# clause",
"details": []
},
{
"value": 1.0,
"description": "status:active",
"details": []
}
]
}
]
}
]
},
{
"value": 0.0,
"description": "match on required clause, product of:",
"details": [
{
"value": 0.0,
"description": "# clause",
"details": []
},
{
"value": 1.0,
"description": "DocValuesFieldExistsQuery [field=_primary_term]",
"details": []
}
]
}
]
}
}
Any help or idea is greatly appreciated...
If you closely look at the first
must
clause, it appears that you haven't mentioned the entire path of the field.Below is how the query should be and should work fine for your use case.
Query
Hope it helps!