Elasticsearch query issue on date-time ranges in a nested object

Question

Hi I am using spring data Elasticsearch to form a query and have the following data indexed in my elastic search

Indexed Data

[
        {
            "id": "Ef5E-HYB3sZzelDP-ie1",
            "name": "B",
            "availability": [
                {
                    "partial": false,
                    "dates": {
                        "gte": "2020-12-05T09:00:00",
                        "lte": "2020-12-10T13:00:00"
                    }
                }
            ]
        },
        {
            "id": "Ev5F-HYB3sZzelDPIicy",
            "name": "A",
            "availability": [
                {
                    "partial": false,
                    "dates": {
                        "gte": "2020-12-01T07:00:00",
                        "lte": "2020-12-02T12:00:00"
                    }
                }
            ]
        }
    ]

Entities

public class Worker {
    @Id
    private String id;

    @Field(type = FieldType.Text)
    private String name;

    @Field(type = FieldType.Nested)
    private List<Availability> availability;
}

public class Availability {
    @Field(type = FieldType.Boolean)
    private boolean partial;
    
    @Field(type = FieldType.Date_Range, format = DateFormat.date_hour_minute_second)
    private Map<String, LocalDateTime> dates;
}

Query Condition: I wanted to query and check if there are any worker available between the date-times:

{
    "startDate": "2020-12-05T09:00:00",
    "endDate": "2020-12-10T10:00:00"
}

For this I am creating a query using bool query and nested query like so:

private Query prepareSearchQuery(final WorkerQuery query, Integer pageNumber, Integer pageSize) {
        final BoolQueryBuilder queryBuilder = QueryBuilders.boolQuery();

        // To form the nested query
        NestedQueryBuilder nested = prepareAvailabilityQuery(query);

        queryBuilder.must(nested);

        Pageable pageable = PageRequest.of(pageNumber, pageSize);

        // @formatter:off
        return new NativeSearchQueryBuilder()
                .withPageable(pageable)
                .withQuery(queryBuilder)
                .build();
        // @formatter:on
    }

    /**
     * Query to prepare the nested object structure
     * 
     * @param query
     * @return
     */
    private NestedQueryBuilder prepareAvailabilityQuery(final WorkerQuery query) {
        final BoolQueryBuilder queryBuilder = QueryBuilders.boolQuery();

        // Add date query
        if (isValidDateTime(query.getStartDate(), query.getStartDate())) { // validate the datetime obj
            // @formatter:off
            RangeQueryBuilder dateQuery = QueryBuilders.rangeQuery("availability.dates")
                    .gte(query.getStartDate())
                    .lte(query.getEndDate())
                    // This is not working with date-time
                    .relation("WITHIN");
            // @formatter:on

            queryBuilder.must(dateQuery);
        }

        return QueryBuilders.nestedQuery("availability", queryBuilder, ScoreMode.None);
    }

For this particular case when I comment out the relation("WITHIN") I am able to fetch the result for my query condition, but when the relation is included in the query, it doesn't return any result. Is there something wrong with the query?

Note: I have also added converters to convert date-time to Long and Back while reading and writing the data.

Here is the repository for this for reference

UPDATE

I updated my search query to consider the timezone and format of the data that is indexed.

private NestedQueryBuilder prepareAvailabilityQuery(final WorkerQuery query) {
        final BoolQueryBuilder queryBuilder = QueryBuilders.boolQuery();
        
//      queryBuilder.must(QueryBuilders.termQuery("availability.partial", query.isPartial()));
        
        // Add date query
        if (isValidDateTime(query.getStartDate(), query.getStartDate())) {
            // @formatter:off
            RangeQueryBuilder dateQuery = QueryBuilders.rangeQuery("availability.dates")
                    .gte(query.getFormattedStartDate())
                    .lte(query.getFormattedEndDate())
                    .format("yyyy-MM-dd'T'HH:mm")
                    .timeZone("Asia/Kolkata");
                    // This is not working with date-time
//                  .relation("WITHIN");
            // @formatter:on

            queryBuilder.must(dateQuery);
        }

        return QueryBuilders.nestedQuery("availability", queryBuilder, ScoreMode.None);
    }

So the elastic query that is getting formed now is like this for the range-query alone:

{
    "range": {
        "availability.dates": {
            "from": "2020-12-01T07:00",
            "to": "2020-12-02T12:00",
            "include_lower": true,
            "include_upper": true,
            "time_zone": "Asia/Kolkata",
            "format": "yyyy-MM-dd'T'HH:mm",
            "boost": 1.0
        }
    }
}

Index mapping in elastic search for worker index

{
    "workers": {
        "aliases": {},
        "mappings": {
            "properties": {
                "_class": {
                    "type": "text",
                    "fields": {
                        "keyword": {
                            "type": "keyword",
                            "ignore_above": 256
                        }
                    }
                },
                "availability": {
                    "type": "nested",
                    "properties": {
                        "dates": {
                            "type": "date_range"
                        },
                        "partial": {
                            "type": "boolean"
                        }
                    }
                },
                "name": {
                    "type": "text"
                }
            }
        },
        "settings": {
            "index": {
                "routing": {
                    "allocation": {
                        "include": {
                            "_tier_preference": "data_content"
                        }
                    }
                },
                "refresh_interval": "1s",
                "number_of_shards": "1",
                "provided_name": "workers",
                "creation_date": "1610515965426",
                "store": {
                    "type": "fs"
                },
                "number_of_replicas": "1",
                "uuid": "gJ9zzWs1RXmevJbwwj9Z2g",
                "version": {
                    "created": "7100199"
                }
            }
        }
    }
}

Answer 1

等待大神答复