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?


Ответы (0 шт):