Querying an entity by values in an ElementCollections using Spring Data JpaRepositories

Question

Is it possible to query entities in a JpaRepository based on values stored in an attached @ElementCollection using query methods?

Setup

My Spring Boot 2 / Spring 5 application has an entity (Artwork) to which arbitrary metadata can be attached. The metadata is implemented with a simple key value map using @ElementCollection and @CollectionTable, since the metadata is just plaintext key/value pairs and does not exists outside the scope of it's entity.

The entity looks like this:

@Entity
@Table(name = "artwork")
public class Artwork implements Serializable {
    @Id
    @GeneratedValue(strategy = IDENTITY)
    private Long id;

    // more propperties 

    @ElementCollection
    @MapKeyColumn(name = "name")
    @Column(name = "value")
    @CollectionTable(name = "artwork_metadata", joinColumns = @JoinColumn(name = "artwork_id"))
    private Map<String, String> metadata = new HashMap<>();

    // more code
}

Problem

I have two use cases, where I need to query the entity based on information stored in the metadata map.

• One use case is finding all Artworks with licenses (having metadata.name = license, no matter what is in the value column)
• The other is finding all entities of one specific artist (having an exact key/value pair of metadata.name = artist and metadata.value = someName)

I have tried using the underscore notation for manual property path description; but my datatype is a map, not really an object with fields name / value.

public interface ArtworkRepository extends JpaRepository<Artwork, Long> {
    List<Artwork> findAllByReleaseDateAfter(Instant after);

    // Not working
    List<Artwork> findAllByMetadata_NameAndMetadata_value 

Querying in general seems to be possible. But most of the answers I found on StackOverflow are about searching in Lists, not in maps.

So my question is

How do I query entities in a JpaRepository based on values stored in an attached @ElementCollection using query methods? Or do I need to convert this rlationship at the object level using JPA’s one-to-many mapping.

Update

• Since we have several tens of thousands of recordsin the artist table I want to filter at the database level and not within the application.
• Querying by metadata will become common, so introducing helper flags like hasLicense or moving the artist to the primary entity is not an option.

Thanks.

Answer 1

JPA 2.0 allows to use KEY() and VALUE() to refer to the key and value in map-based @ElementCollection in JPQL.

Normally , you can use them for solving your problem. However, as mentioned in this , Hibernate seems to be a some strange behaviour for VALUES()...... Lucky , it has workaround.

Anyway , your problem can be solved by the following @Query (Assume you are using Hibernate. If you use other providers , you can try VALUES()). :

public interface ArtworkRepository extends JpaRepository<Artwork, Long> {

    @Query(value = "select a from Artwork a join a.metadata meta where (KEY(meta) = :name)")
    public List<Artwork> findArtworkByMetadata(@Param("name") String name);

    @Query(value = "select a from Artwork a join a.metadata meta where (KEY(meta) = :name and meta = :value)")
    public List<Artwork> findArtworkByMetadata(@Param("name") String name, @Param("value") String value);

}

To find all Artworks with licenses :

artworkRepository.findArtworkByMetadata("license");

To find all Artworks of a specific artist:

artworkRepository.findArtworkByMetadata("artist" , "someName");