How to create hibernate specification with jsonb value in column?

Viewed 32

I have Java, Spring, Hibernate, PostgreSQL project Where i have view script for entity and I need to create specification that accept List region from filter, and I need to find if thats Longs from filter in jsonb field?

Here is view-entity:

@Entity
@Getter
@Immutable
@Table(name = "service_provider_profile_item_view")
@TypeDefs({
        @TypeDef(name = "jsonb", typeClass = JsonBinaryType.class)
})
public class ServiceProviderProfileItemView {

// a lot of other fileds

@Type(type = "jsonb")
@Column(columnDefinition = "jsonb", name = "region")
private List<Long> region;
}

SQL-script:

create view service_provider_profile_item_view as
       select (select jsonb_agg(distinct regions_id::bigint)
            from project_realizations pr,
            -- other fields

So as you see here, I store values in jsonb format in table. Some examples of values:

-"[47, 78]"
-"[2, 68]"

FilterRequest:

@Data
public class ServiceProviderProfileFilter {   
    private List<Long> region;
}

And I'm trying to create specification, right now I'm stuck on next code that doesn't work:

public Specification<ServiceProviderProfileItemView> from(ServiceProviderProfileFilter filter) {
    return Specification.where(basicSpecs.distinct(ServiceProviderProfileItemView.class)).
            .and((root, query, criteriaBuilder) ->
                        criteriaBuilder.function("json_build_array",
                        Long.class)
                                .in(filter.getRegion()
                ))
}

I get next error in this situation:

Operator does not exist: json = integer

How can I handle it and make filtering by List of longs in jsonb?

0 Answers
Related