[FIXED] QueryDSL Predicate for use with JPARepository where field is a JSON String converted using an AttributeConverter to a List<Object>

Issue

I have a JPA Entity (Terminal) which uses an AttributeConverter to convert a Database String into a list of Objects (ProgrmRegistration). The converter just uses a JSON ObjectMapper to turn the JSON String into POJO objects.

Entity Object

@Entity
@Data
public class Terminal {

    @Id
    private String terminalId;

    @NotEmpty
    @Convert(converter = ProgramRegistrationConverter.class)
    private List<ProgramRegistration> programRegistrations;

    @Data
    public static class ProgramRegistration {
        private String program;
        private boolean online;
    }
}

The Terminal uses the following JPA AttributeConverter to serialize the Objects from and to JSON

JPA AttributeConverter

public class ProgramRegistrationConverter implements AttributeConverter<List<Terminal.ProgramRegistration>, String> {

    private final ObjectMapper objectMapper;
    private final CollectionType programRegistrationCollectionType;

    public ProgramRegistrationConverter() {
        this.objectMapper = new ObjectMapper().setSerializationInclusion(JsonInclude.Include.NON_EMPTY);
        this.programRegistrationCollectionType = 
              objectMapper.getTypeFactory().constructCollectionType(List.class, Terminal.ProgramRegistration.class);
    }

    @Override
    public String convertToDatabaseColumn(List<Terminal.ProgramRegistration> attribute) {
        if (attribute == null) {
            return null;
        }
        String json = null;
        try {
            json = objectMapper.writeValueAsString(attribute);
        } catch (final JsonProcessingException e) {
            LOG.error("JSON writing error", e);
        }
        return json;
    }

    @Override
    public List<Terminal.ProgramRegistration> convertToEntityAttribute(String dbData) {
        if (dbData == null) {
            return Collections.emptyList();
        }
        List<Terminal.ProgramRegistration> list = null;
        try {
            list = objectMapper.readValue(dbData, programRegistrationCollectionType);
        } catch (final IOException e) {
            LOG.error("JSON reading error", e);
        }
        return list;
    }

}

I am using Spring Boot and a JPARepository to fetch a Page of Terminal results from the Database. To filter the results I am using a BooleanExpression as the Predicate. For all the filter values on the Entity it works well, but the List of objects converted from the JSON string does not allow me to easily write an Expression that will filter the Objects in the list.

REST API that is trying to filter the Entity Objects using QueryDSL

@GetMapping(path = "/filtered/page", produces = MediaType.APPLICATION_JSON_VALUE)
public Page<Terminal> findFilteredWithPage(
    @RequestParam(required = false) String terminalId,
    @RequestParam(required = false) String programName,
    @PageableDefault(size = 20) @SortDefault.SortDefaults({ @SortDefault(sort = "terminalId") }) Pageable p) {

    BooleanBuilder builder = new BooleanBuilder();
    if (StringUtils.isNotBlank(terminalId))
       builder.and(QTerminal.terminal.terminalId.upper()
                  .contains(StringUtils.upperCase(terminalId)));

    // TODO: Figure out how to use QueryDsl to get the converted List as a predicate
    // The code below to find the programRegistrations does not allow a call to any(), 
    // expects a CollectionExpression or a SubqueryExpression for calls to eqAny() or in()
    
    if (StringUtils.isNotBlank(program)) 
       builder.and(QTerminal.terminal.programRegistrations.any().name()
                  .contains(StringUtils.upperCase(programName)));

    return terminalRepository.findAll(builder.getValue(), p);
}

I am wanting to get any Terminals that have a ProgramRegistration object with the program name equal to the parameter passed into the REST service.

I have been trying to get CollectionExpression or SubQueryExpression working without success since they all seem to be wanting to perform a join between two Entity objects. I do not know how to create the path and query so that it can iterate over the programRegistrations checking the "program" field for a match. I do not have a QProgamRegistration object to join with, since it is just a list of POJOs.

How can I get the predicate to match only the Terminals that have programs with the name I am searching for?

This is the line that is not working:

builder.and(QTerminal.terminal.programRegistrations.any().name() .contains(StringUtils.upperCase(programName)));

Solution

Although the following desired QueryDsl looks like it should work

QTerminal.terminal.programRegistrations.any().name().contains(programName);

In reality JPA would never be able to convert it into something that would make sense in terms of SQL. The only SQL that JPA could convert it into could be as follows:

SELECT t.terminal_id FROM terminal t where t.terminal_id LIKE '%00%' and t.program_registrations like '%"program":"MY_PROGRAM_NAME"%'; 

This would work in this use case, but be semantically wrong, and therefore it is correct that it should not work. Trying to select unstructured data using a structured query language makes no sense

The only solution is to treat the data as characters for the DB search criteria, and to treat it as a list of Objects after the query completes and then perform filtering of the rows in Java. Although This makes the paging feature rather useless.

One possible solution is to have a secondary read only String version of the column that is used for the DB search criteria, that is not converted to JSON by the AttributeConverter.

@JsonIgnore
@Column(name = "programRegistrations", insertable = false, updatable = false)
private String programRegistrationsStr;

The real solution is do not use unstructured data when you want structured queries on that data Therefore convert the data to either a database that supports the JSON natively for queries or model the data correctly in DDL.

Answered By - AndreJ