user3714734
Reputation: 3
Elasticsearch iterate over range query result in nested object
I have a elasticsearch index which is having a nested object called availability which has a date and a boolean field , the mapping schema is as follows
{
"hotel_nested" : {
"mappings" : {
"doc" : {
"properties" : {
"amenities" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"availability" : {
"type" : "nested",
"properties" : {
"date" : {
"type" : "date",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
},
"format" : "MM/dd/yyyy HH:mm:ss||MM/dd/yyyy||epoch_millis"
},
"status" : {
"type" : "boolean"
}
}
},
"dailyRate" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"destination" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"hotelName" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"location" : {
"properties" : {
"lat" : {
"type" : "float"
},
"lon" : {
"type" : "float"
}
}
},
"maxOccupancy" : {
"type" : "long"
},
"propertyType" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"roomType" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"starRating" : {
"type" : "float"
}
}
}
}
}
}
Here is one of the object
{
"_index" : "hotel_nested",
"_type" : "doc",
"_id" : "14",
"_score" : 1.0,
"_source" : {
"roomType" : "Executive",
"destination" : "Albuquerque",
"maxOccupancy" : 4,
"starRating" : 6.8,
"hotelName" : "Lotte Hotel Seoul",
"amenities" : [
"Spa",
"Internet",
"Free parking",
"Air conditioning",
"Laundry Service",
"Business Services"
],
"location" : {
"lat" : 56.76755,
"lon" : -110.75792
},
"propertyType" : "Hotel",
"dailyRate" : "$178.96",
"availability" : [
{
"date" : "10/1/2017",
"status" : true
},
{
"date" : "10/2/2017",
"status" : true
},
{
"date" : "10/3/2017",
"status" : true
},
{
"date" : "10/4/2017",
"status" : false
},
{
"date" : "10/5/2017",
"status" : false
},
{
"date" : "10/6/2017",
"status" : false
},
{
"date" : "10/7/2017",
"status" : true
},
{
"date" : "10/8/2017",
"status" : true
},
{
"date" : "10/9/2017",
"status" : false
},
{
"date" : "10/10/2017",
"status" : false
},
{
"date" : "10/11/2017",
"status" : true
},
{
"date" : "10/12/2017",
"status" : false
},
{
"date" : "10/13/2017",
"status" : false
},
{
"date" : "10/14/2017",
"status" : true
},
{
"date" : "10/15/2017",
"status" : true
},
{
"date" : "10/16/2017",
"status" : true
},
{
"date" : "10/17/2017",
"status" : false
},
{
"date" : "10/18/2017",
"status" : true
},
{
"date" : "10/19/2017",
"status" : false
},
{
"date" : "10/20/2017",
"status" : true
},
{
"date" : "10/21/2017",
"status" : false
},
{
"date" : "10/22/2017",
"status" : true
},
{
"date" : "10/23/2017",
"status" : true
},
{
"date" : "10/24/2017",
"status" : true
},
{
"date" : "10/25/2017",
"status" : false
},
{
"date" : "10/26/2017",
"status" : false
},
{
"date" : "10/27/2017",
"status" : false
},
{
"date" : "10/28/2017",
"status" : true
},
{
"date" : "10/29/2017",
"status" : true
},
{
"date" : "10/30/2017",
"status" : true
},
{
"date" : "10/31/2017",
"status" : true
},
{
"date" : "11/1/2017",
"status" : true
},
{
"date" : "11/2/2017",
"status" : false
},
{
"date" : "11/3/2017",
"status" : false
},
{
"date" : "11/4/2017",
"status" : false
},
{
"date" : "11/5/2017",
"status" : false
},
{
"date" : "11/6/2017",
"status" : true
},
{
"date" : "11/7/2017",
"status" : false
},
{
"date" : "11/8/2017",
"status" : false
},
{
"date" : "11/9/2017",
"status" : false
},
{
"date" : "11/10/2017",
"status" : false
},
{
"date" : "11/11/2017",
"status" : false
},
{
"date" : "11/12/2017",
"status" : false
},
{
"date" : "11/13/2017",
"status" : false
},
{
"date" : "11/14/2017",
"status" : true
},
{
"date" : "11/15/2017",
"status" : true
},
{
"date" : "11/16/2017",
"status" : false
},
{
"date" : "11/17/2017",
"status" : true
},
{
"date" : "11/18/2017",
"status" : false
},
{
"date" : "11/19/2017",
"status" : true
},
{
"date" : "11/20/2017",
"status" : true
},
{
"date" : "11/21/2017",
"status" : true
},
{
"date" : "11/22/2017",
"status" : true
},
{
"date" : "11/23/2017",
"status" : false
},
{
"date" : "11/24/2017",
"status" : false
},
{
"date" : "11/25/2017",
"status" : false
},
{
"date" : "11/26/2017",
"status" : true
},
{
"date" : "11/27/2017",
"status" : true
},
{
"date" : "11/28/2017",
"status" : false
},
{
"date" : "11/29/2017",
"status" : false
},
{
"date" : "11/30/2017",
"status" : false
},
{
"date" : "12/1/2017",
"status" : true
},
{
"date" : "12/2/2017",
"status" : true
},
{
"date" : "12/3/2017",
"status" : true
},
{
"date" : "12/4/2017",
"status" : true
},
{
"date" : "12/5/2017",
"status" : true
},
{
"date" : "12/6/2017",
"status" : true
},
{
"date" : "12/7/2017",
"status" : false
},
{
"date" : "12/8/2017",
"status" : false
},
{
"date" : "12/9/2017",
"status" : true
},
{
"date" : "12/10/2017",
"status" : false
},
{
"date" : "12/11/2017",
"status" : true
},
{
"date" : "12/12/2017",
"status" : false
},
{
"date" : "12/13/2017",
"status" : true
},
{
"date" : "12/14/2017",
"status" : true
},
{
"date" : "12/15/2017",
"status" : true
},
{
"date" : "12/16/2017",
"status" : false
},
{
"date" : "12/17/2017",
"status" : true
},
{
"date" : "12/18/2017",
"status" : true
},
{
"date" : "12/19/2017",
"status" : false
},
{
"date" : "12/20/2017",
"status" : true
},
{
"date" : "12/21/2017",
"status" : true
},
{
"date" : "12/22/2017",
"status" : false
},
{
"date" : "12/23/2017",
"status" : false
},
{
"date" : "12/24/2017",
"status" : false
},
{
"date" : "12/25/2017",
"status" : false
},
{
"date" : "12/26/2017",
"status" : false
},
{
"date" : "12/27/2017",
"status" : false
},
{
"date" : "12/28/2017",
"status" : false
},
{
"date" : "12/29/2017",
"status" : false
},
{
"date" : "12/30/2017",
"status" : true
},
{
"date" : "12/31/2017",
"status" : true
}
]
}
}
My issue is that I want to search between two dates and find out if the hotel availability status is true for all dates between the two dates with also matching the city name at the crude level,i have other criteria to search also which i think i can handle,but finding true status for all dates between two dates is bugging me
eg search query parameters city Albuquerque,start date 10/22/2017 and end date 10/24/2017
I have created a query which is giving undesired results, my query is
{
"query": {
"bool": {
"must":[
{
"match": {
"destination": {
"query": "Albuquerque" ,
"operator": "and"
}
}
}
],
"filter": {
"bool": {
"must": [ {
"nested": {
"path": "availability",
"query": {
"bool": {
"must": [
{
"range": {
"availability.date":{ "gte": "10/22/2017",
"lte":"10/24/2017"}
}
},
{
"term": {
"availability.status": "true"
}
}
]
}
}
}
}
]
}
}
}
}
}
The first hit of this query is
{
"_index": "hotel_nested",
"_type": "doc",
"_id": "305",
"_score": 4.815987,
"_source": {
"roomType": "Executive",
"destination": "Albuquerque",
"maxOccupancy": 2,
"starRating": 4.2,
"hotelName": "Sheraton San Diego Hotel & Marina",
"amenities": [
"Kitchen",
"Air conditioning",
"Laundry Service",
"Business Services",
"Free parking",
"Spa"
],
"location": {
"lat": 54.92887,
"lon": -101.31256
},
"propertyType": "Hotel",
"dailyRate": "$462.59",
"availability": [
{
"date": "10/1/2017",
"status": false
},
{
"date": "10/2/2017",
"status": true
},
{
"date": "10/3/2017",
"status": false
},
{
"date": "10/4/2017",
"status": true
},
{
"date": "10/5/2017",
"status": true
},
{
"date": "10/6/2017",
"status": true
},
{
"date": "10/7/2017",
"status": true
},
{
"date": "10/8/2017",
"status": false
},
{
"date": "10/9/2017",
"status": false
},
{
"date": "10/10/2017",
"status": true
},
{
"date": "10/11/2017",
"status": true
},
{
"date": "10/12/2017",
"status": false
},
{
"date": "10/13/2017",
"status": true
},
{
"date": "10/14/2017",
"status": false
},
{
"date": "10/15/2017",
"status": true
},
{
"date": "10/16/2017",
"status": false
},
{
"date": "10/17/2017",
"status": true
},
{
"date": "10/18/2017",
"status": false
},
{
"date": "10/19/2017",
"status": false
},
{
"date": "10/20/2017",
"status": true
},
{
"date": "10/21/2017",
"status": true
},
{
"date": "10/22/2017",
"status": true
},
{
"date": "10/23/2017",
"status": false
},
{
"date": "10/24/2017",
"status": false
},
{
"date": "10/25/2017",
"status": false
},
{
"date": "10/26/2017",
"status": true
},
{
"date": "10/27/2017",
"status": true
},
{
"date": "10/28/2017",
"status": false
},
{
"date": "10/29/2017",
"status": true
},
{
"date": "10/30/2017",
"status": false
},
{
"date": "10/31/2017",
"status": false
},
{
"date": "11/1/2017",
"status": false
},
{
"date": "11/2/2017",
"status": false
},
{
"date": "11/3/2017",
"status": false
},
{
"date": "11/4/2017",
"status": false
},
{
"date": "11/5/2017",
"status": false
},
{
"date": "11/6/2017",
"status": true
},
{
"date": "11/7/2017",
"status": false
},
{
"date": "11/8/2017",
"status": true
},
{
"date": "11/9/2017",
"status": true
},
{
"date": "11/10/2017",
"status": true
},
{
"date": "11/11/2017",
"status": true
},
{
"date": "11/12/2017",
"status": false
},
{
"date": "11/13/2017",
"status": true
},
{
"date": "11/14/2017",
"status": false
},
{
"date": "11/15/2017",
"status": true
},
{
"date": "11/16/2017",
"status": true
},
{
"date": "11/17/2017",
"status": false
},
{
"date": "11/18/2017",
"status": true
},
{
"date": "11/19/2017",
"status": false
},
{
"date": "11/20/2017",
"status": true
},
{
"date": "11/21/2017",
"status": false
},
{
"date": "11/22/2017",
"status": false
},
{
"date": "11/23/2017",
"status": false
},
{
"date": "11/24/2017",
"status": false
},
{
"date": "11/25/2017",
"status": false
},
{
"date": "11/26/2017",
"status": false
},
{
"date": "11/27/2017",
"status": false
},
{
"date": "11/28/2017",
"status": false
},
{
"date": "11/29/2017",
"status": false
},
{
"date": "11/30/2017",
"status": true
},
{
"date": "12/1/2017",
"status": false
},
{
"date": "12/2/2017",
"status": false
},
{
"date": "12/3/2017",
"status": false
},
{
"date": "12/4/2017",
"status": true
},
{
"date": "12/5/2017",
"status": true
},
{
"date": "12/6/2017",
"status": false
},
{
"date": "12/7/2017",
"status": true
},
{
"date": "12/8/2017",
"status": false
},
{
"date": "12/9/2017",
"status": true
},
{
"date": "12/10/2017",
"status": true
},
{
"date": "12/11/2017",
"status": false
},
{
"date": "12/12/2017",
"status": true
},
{
"date": "12/13/2017",
"status": false
},
{
"date": "12/14/2017",
"status": true
},
{
"date": "12/15/2017",
"status": false
},
{
"date": "12/16/2017",
"status": true
},
{
"date": "12/17/2017",
"status": true
},
{
"date": "12/18/2017",
"status": false
},
{
"date": "12/19/2017",
"status": false
},
{
"date": "12/20/2017",
"status": false
},
{
"date": "12/21/2017",
"status": true
},
{
"date": "12/22/2017",
"status": true
},
{
"date": "12/23/2017",
"status": false
},
{
"date": "12/24/2017",
"status": false
},
{
"date": "12/25/2017",
"status": true
},
{
"date": "12/26/2017",
"status": false
},
{
"date": "12/27/2017",
"status": true
},
{
"date": "12/28/2017",
"status": false
},
{
"date": "12/29/2017",
"status": false
},
{
"date": "12/30/2017",
"status": true
},
{
"date": "12/31/2017",
"status": false
}
]
}
}
In this hit status for dates 10/23/2017 and 10/24/2017 is false Can someone guide me in the correct direction or maybe give a example search query to solve this problem
Upvotes: 0
Views: 405
Answers (1)
Eli
Reputation: 4936
You need to put your nested query under must clause. Add the inner_hits key at the bottom of the nested query AND enable specific _source fields at the head (because we don't care about the rest of the sub-documents who don't match the nested query) as follows:
{
"_source": [
"hotelName",
"dailyRate"
],
"query": {
"bool": {
"must": [
{
"match": {
"destination": {
"query": "Albuquerque",
"operator": "and"
}
}
},
{
"nested": {
"path": "availability",
"query": {
"bool": {
"must": [
{
"range": {
"availability.date": {
"gte": "10/22/2017",
"lte": "10/24/2017"
}
}
},
{
"term": {
"availability.status": "true"
}
}
]
}
},
"inner_hits": {
"size": 10
}
}
}
]
}
}
}
Pay attention that now you'll get only nested-documents that comply with the conditions, e.g for your example the result would be:
{
"_index": "hotel_nested",
"_type": "doc",
"_id": "305",
"_score": 4.815987,
"_source": {
"hotelName": "Sheraton San Diego Hotel & Marina",
"dailyRate": "$462.59"
},
"inner_hits": {
"availability": {
"hits": {
"total": 1,
"hits": [
{
"date": "10/22/2017",
"status": true
}
]
}
}
}
}
So now, you will have to check whether all the days in the range 22-24 in October are present.
One shortcut way is to mark "inner_hits":{"size":0} and just look at the result and check if the number of inner_hits equals to the number of the days in the range of 22-24 in October:
"inner_hits"."availability"."hits"."total" == 3
Upvotes: 0
Related Questions
- Nested Query with Date Range
- Elasticsearch query on array of composite objects along with date ranges
- Elasticsearch nested query with available values in range
- How to filter nested object in Elasticsearch 7.5?
- Elasticsearch Range Query on Nested Keyword field
- elasticsearch nested range query
- Elasticsearch date range check for nested array of date ranges
- using Nest DateRange
- Date Range Query Elasticsearch
- Query in elasticsearch with multiple ranges on multiple dates