filtering from json column in postgresql using CriteriaBuilder

Question

I have a postgresql table where I have department details as column and details of the employees working in the department as list of json in a column in the same table. I want to filter the department details if there are any employees joined after or before or between any particualar dates. I am able to do that using query. I am trying to do that using Java application. But I am not sure how exactly to do this.

Table structure:

CREATE TABLE IF NOT EXISTS department_details
(
    id bigint NOT NULL DEFAULT nextval('department_details_id_seq'::regclass),
    location_id bigint NOT NULL,
    department_code character varying(255) COLLATE pg_catalog."default" NOT NULL,
    department_name character varying(255) COLLATE pg_catalog."default" NOT NULL,
    employee_details json
}

And the structure of the employee_details will be as below.

[{"employeeId":1,
"firstName":"firstName1",
"lastName":"lastName1",
"designation":"Manager",
"dateOfBirth":"1990-01-31",
"joiningDate":"2015-01-31"},
{"employeeId":2,
"firstName":"firstName2",
"lastName":"lastName2",
"designation":"Supervisor",
"dateOfBirth":"1990-12-31",
"joiningDate":"2015-12-31"}]

Now I want to filter based on the joining date. I am able to achieve this in SQL query as below.

select * from department_Details
where json_extract_path_text(employee_details->0,'joiningDate') > '2023-08-20' or
json_extract_path_text(employee_details->1,'joiningDate') > '2023-08-20';

Consider that no of employees in the json will be varying, it is not fixed to one or two. Above query will work only if two employees details in the json.

I want to achieve the same in my java application using Criteria builder. Below is the code I have written to filter. It is working only if only one employee details that too not as an array.

LocalDate fromDate = LocalDate.parse(requestFilter.getFromDateValue().trim(), 
DateTimeFormatter.ofPattern(YYYY_MM_DD));
LocalDateTime startOfDay = fromDate.atStartOfDay();
FilterOperation operationType = requestFilter.getOperator();

switch (operationType) {

case BETWEEN:
  if (requestFilter.getToDateValue() == null) {
    throw new BusinessException("For 'Between' operation, two dates are required.");
  }

  LocalDate toDate = LocalDate.parse(requestFilter.getToDateValue().trim(), 
  DateTimeFormatter.ofPattern(YYYY_MM_DD));
  LocalDateTime endOfDay = toDate.plusDays(1).atStartOfDay().minusNanos(1);

  return criteriaBuilder.between(criteriaBuilder.function("json_extract_path_text", 
    String.class, root.get("employeeDetails"),
                            criteriaBuilder.literal("joiningDate").as(String.class)), startOfDay.toString(), endOfDay.toString());

case GREATER_THAN:
  return criteriaBuilder.greaterThan(criteriaBuilder.function("json_extract_path_text", String.class, root.get("employeeDetails").type(),
                            criteriaBuilder.literal("joiningDate").as(String.class)), startOfDay.toString());

case LESS_THAN:
  return criteriaBuilder.lessThan(criteriaBuilder.function("json_extract_path_text", String.class, root.get("employeeDetails"),
                            criteriaBuilder.literal("joiningDate).as(String.class)), startOfDay.toString());

default:
throw new BusinessException("Invalid operator for date : " + operationType.getOperatorKey());
}

My Entity is as below:

@Table(name = "department_details")
@Getter
@Setter
@NoArgsConstructor
public class DepartmentDetails{

   @Id
   @GeneratedValue(strategy = GenerationType.IDENTITY)
   private Long id;

   @NotNull
   private Long locationId;

   @NotNull
   private String departmentCode;

   private String deparmentName;

   @Column(name = "employee_details")
   @Convert(converter = EmployeeDetailsConverter.class)
   private List<EmployeeDetails> employeeDetailsList;
}

And my EmployeeDetails entity will be as below:

@Setter
@NoArgsConstructor
public class EmployeeDetails{

    private Long employeeId;

    private String firstName;

    private String lastName;

    private String designation;

    private String dateOfBirth;

    private String joiningDate;
}

Please let me know how to achieve in java code criteria builder that ->0, ->1 which I achieved in SQL query?