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?