JPA: check if one array overlaps another/do arrays have common items
My tech stack is Kotlin/Spring Data JPA/Hibernate/Postgres. I implement PagingAndSortingRepository interface to initialize all logic I need to manage entities.
Let's consider I have an entity class:
@Entity
@Table(name = "team")
@TypeDefs(
TypeDef(name = "jsonb", typeClass = JsonBinaryType::class),
TypeDef(name = "list-array", typeClass = ListArrayType::class)
)
data class TeamEntity(
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
var id: Long? = null,
@Enumerated(EnumType.STRING)
val type: EventType,
@Type(type = "list-array")
val usernames: List<String>?
) {
@CreatedBy
lateinit var createdBy: String
}
and a repository:
@Repository
interface TeamRepository : PagingAndSortingRepository<TeamEntity, Long> {
@Query(
value = """
select *
from event e
where ((:subject) is null or cast(:subject as text[]) && e.usernames)
and ( cast(:type as text) is null or e.type = cast(:type as text))
""",
countQuery = """
select count(1)
from event e
where ((:subject) is null or cast(:subject as text[]) && e.usernames)
and ( cast(:type as text) is null or e.type = cast(:type as text))
""",
nativeQuery = true
)
fun filter(
subject: List<String> = emptyList(),
type: String? = null,
pageable: Pageable
): Page<TeamEntity>
}
My subject is the column 'usernames' which is of type text[]. So, I use && operator to check if the arrays have common items. However, I receive an error:
ERROR: array value must start with "{" or dimension information
I tried to use different array init methods, applied them to the parameter :subject. Used braces to wrap it. Tried to not cast it to text[] (in this case it just says that I can't compare record with text[]).
I also tried to use JPQL instead of native query. Same result.
Could you please help me to figure out how to solve this problem?