How to write a predicate query in JPA if the column contains jsonb data, match an attribute inside that jsonb data

53 Views Asked by At

I have a jsonb column to save some extra data of an entity. While querying in that table, I want to filter data based on one field inside the extra_data along with some other filters. I am using the JPA findAll method with a custom specification in my Spring Boot application.

Here is my ExtraDataDto:

@Data
@NoArgsConstructor
@AllArgsConstructor
public class ExtraDataDTO {

    private String attendantName;

    private Integer attendantId;
}

Entity class:

@Data
@NoArgsConstructor
@AllArgsConstructor
public class OrderItem extends DateAudit {

...other members

@Type(type = "jsonb")
    @Column(columnDefinition = "jsonb")
    @Basic(fetch = FetchType.LAZY)
    private ExtraDataDTO extraData;
}

I attempted to use the following specification:

Specification<OrderItem> serviceItemSpecification = (root, query, criteriaBuilder) -> {
...other conditions

            if (attendantId.isPresent()) {
                Expression<String> attendantIdExpression = root.get("extraData").get("attendantId");
                predicate = criteriaBuilder.and(predicate, criteriaBuilder.equal(attendantIdExpression, attendantId.get()));
            }
};

However, it results in the following error:

"error": "java.lang.IllegalStateException: Illegal attempt to dereference path source [null.extraData] of basic type"

I'm using DataTablesRepository and the output I receive is as follows, excluding the attendantId predicate filter:

{
....other fields

    "alternateUPC": null,
    "taxProfiles": null,
    "extraData": {
                 "attendantName": "Aby",
                 "attendantId": 1
                 },
    "orderCustomerId": null,
    "orderDrawerName": null
 }

Thank you

0

There are 0 best solutions below