Thanks for the messages. I have changed the structure to include parent and child documents on the same index. I am able to query everything individually successfully. In order to create a query for the parent units that are available for every day of a trip, I am now attempting a "has_child" but am having some unexpected results.
The mapping for /units/:
{
"units": {
"mappings": {
"_doc": {
"properties": {
"available": {
"type": "boolean"
},
"bathrooms": {
"type": "integer"
},
"bedrooms": {
"type": "integer"
},
"coordinates": {
"type": "geo_point"
},
"date": {
"type": "date",
"format": "yyyy-MM-dd"
},
"join_field": {
"type": "join",
"eager_global_ordinals": true,
"relations": {
"unit": "availability"
}
}
}
}
}
}
}
id join_field parent_id date available
1 unit
20190109-1 availability 1 2019-01-09 true
20190110-1 availability 1 2019-01-10 true
20190111-1 availability 1 2019-01-11 false
20190112-1 availability 1 2019-01-12 true
And the query (this query does not work when there is more than 1 date... 2 are shown below). In pseudo SQL, I am attempting the following:
{
"query":{
"bool":{
"must":[
{
"has_child":{
"type":"availability",
"query":{
"bool":{
"must":[
{
"bool":{
"must":[
{
"term":{
"date":{
"value":"2019-01-09"
}
}
},
{
"term":{
"available":{
"value":true
}
}
}
]
}
},
{
"bool":{
"must":[
{
"term":{
"date":{
"value":"2019-01-10"
}
}
},
{
"term":{
"available":{
"value":true
}
}
}
]
}
}
]
}
},
"ignore_unmapped":true
}
}
],
"filter":[
{
"geo_bounding_box":{
"coordinates":{
"bottom":49.853735,
"left":-123.215338,
"top":50.340893,
"right":-122.747045
}
}
}
]
}
}
}
The logic should be similar to:
SELECT parentUnit
WHERE (childAvailability.date = '2019-01-09' AND childAvailability.available = true) AND (childAvailability.date = '2019-01-10' AND childAvailability.available = true)
AND parentUnit.geo ...
I am looping through the days of the stay, and adding a term component for each day. The above is NOT working, however, when I only include a single day, such as below, it does work... I can confirm both days are in the index, and are both available=true as they can be queried individually.
{
"query":{
"bool":{
"must":[
{
"has_child":{
"type":"availability",
"query":{
"bool":{
"must":[
{
"bool":{
"must":[
{
"term":{
"date":{
"value":"2019-01-09"
}
}
},
{
"term":{
"available":{
"value":true
}
}
}
]
}
}
]
}
},
"ignore_unmapped":true
}
}
],
"filter":[
{
"geo_bounding_box":{
"coordinates":{
"bottom":49.853735,
"left":-123.215338,
"top":50.340893,
"right":-122.747045
}
}
}
]
}
}
}
Am I formulating this the correct way to loop through days? The reason I need to do it this way is there are many other variables besides "available" that I will be adding checks for - this is just a simple example.
Any help would be greatly appreciated, thanks!