Select entity whose elementCollection field contains some like string

so here is link of the question on stackoverflow.

anyway, the question is

say we have a entity named Profile

@Entity
@Serializable
@Table(name = "profile")
class Profile {

    @Id @GeneratedValue var id: Long = 0

    @ElementCollection
    @CollectionTable(name = "profile_attachment")
    @Column(length = 1024, nullable = false)
    @LazyCollection(LazyCollectionOption.FALSE)
    lateinit var attachments: Set<String> // could be something like ["/somepath/to/foo.png","/somepath/to/bar.png"]

    constructor()

}

I wanna select the entity whose attachments contains some string which include some character, for example, select entity whose attachments that has one item contains ‘foo’ . I’ve tried the following hql queries, but all failed:

profileRepository.list(" some elements(attachments) like '%foo%'")

profileRepository.list("  '%foo%' in elements(attachments) ")

profileRepository.list("  '%foo%' = some elements(attachments) ")

It’s pretty easy actually if you know the basics of SQL. This kind of query is usually formulated with an EXISTS subquery:

exists (select 1 from p.attachments a where a like '%foo%')