Hi @mreiche , Thanks for the response.
Note - We store the dates in String (LocalDate Object) in couchbase and not epoch time (more details as below)
Regarding the spring version, at the moment we don’t have the feasibility to upgrade to 4.3.X .
Is there any way that we can make this Query work without upgrading the version?
Complete Query
@Query("SELECT META(prep).id AS __id, META(prep).cas AS __cas, prep.* "
+ "FROM #{#n1ql.bucket} prep "
+ "UNNEST prep.schedules AS sch "
+ "WHERE REGEXP_LIKE(META(prep).id, $docIdPrefix) "
+ "AND sch.periodOfOperation.startDate <= $endDate "
+ "AND sch.periodOfOperation.endDate >= $startDate "
+ "AND ANY day IN sch.daysOfOperation SATISFIES day IN $frequencies END "
+ “ORDER BY sch.periodOfOperation.startDate”)
List findAllSchedulesByIdLikeAndDatePeriod(@Param(“docIdPrefix”) String docIdPrefix,
@Param(“startDate”) LocalDate startDate,
@Param(“endDate”) LocalDate endDate,
@Param(“frequencies”) JsonArray frequencies);
Model JSON as stored in couchbase
{
“schedules”: [
{
“daysOfOperation”: [
1,2,3,4,5,6,7
],
“periodOfOperation”: {
“endDate”: “2025-03-28”,
“startDate”: “2025-03-01”
}
},
{
“daysOfOperation”: [
1,2,3,4,5,6,7
],
“periodOfOperation”: {
“endDate”: “2025-03-28”,
“startDate”: “2025-03-01”
}
}
]
}
Model Object
public class Instruction
{
@Id
private String id;
private List <Schedule> schedules;
}
public class Schedule implements Serializable
{
private PeriodOfOperation periodOfOperation;
private List<Integer> daysOfOperation;
}
public class PeriodOfOperation implements Serializable {
private LocalDate startDate;
private LocalDate endDate;
}
Convertors Used in Couchbase Config
@WritingConverter
private static class LocalDateStringConverter implements Converter<LocalDate, String> {
@Override
public String convert(@NotNull LocalDate localDate) {
return localDate.format(DATE_FORMATTER);
}
}
@ReadingConverter
private static class StringLocalDateConverter implements Converter<String, LocalDate> {
@Override
public LocalDate convert(@NotNull String string) {
return LocalDate.parse(string, DATE_FORMATTER);
}
}