title: JPA Query: Select elements that have at least one entryset pair of given map
body: <p>I have an entity:</p>
  
  <pre><code>@Entity
  class A {
     @Id
     @Column
     private Long id;
  
     @ElementCollection
     @MapKeyColumn(name="key")
     @Column(name="value")
     @CollectionTable(name="key_value_pairs", joinColumns=@JoinColumn(name="a_id")) 
     private Map&lt;String, String&gt; metadata;
  }
  </code></pre>
  
  <p>... and I have a JPARepository:</p>
  
  <pre><code>public interface ARepository extends JpaRepository&lt;A, Long&gt; { 
     @Query("...")   
     Page&lt;A&gt; getAllAsThatHaveAtleastOneEntrysetPairOfGivenMapInMetadataMap(Map&lt;String, String&gt; mapToCompareAgainst, Pageable pageable);
  }
  </code></pre>
  
  <p>The problem is that I don't know how to formulate that query or is it even doable in JPQL?
  So basically what I want the query to do, is take all key-value pairs of mapToCompareAgainst and compare them one by one against every element A's metadata all key-value pairs and select elements where at least one complete match is found (e.g key and the value are both the same in persisted element and given map entry).</p>
  
  <p>(The code examples are loosely typed and might have some minor flaws in them in which case I'm sorry. They are only meant to illustrate the problem at hand. Real-life entities are a bit more complex.)</p>
  
  <p><strong>Edit</strong></p>
  
  <p>I found a way to check against one pair (instead of all pairs in map) from here <a href="https://stackoverflow.com/a/23961780/3020903">https://stackoverflow.com/a/23961780/3020903</a> :</p>
  
  <pre><code>@Query("select a from A a where (KEY(a.metadata) = :key and :value in (VALUE(a.metadata)))")   
  Page&lt;A&gt; getAllAsThatHaveAtleastOneGivenPairInMetadataMap(@Param("key") String key, @Param("value") String value, Pageable pageable);
  </code></pre>
  
  <p>So i could do those queries in a loop over the maps entryset and concatenate the results, but then I would have no control over pagination and duplicates, so I'm still at square one.</p>
  
  <p><strong>Edit 2</strong></p>
  
  <p>Actually even this "check against one pair" query does not work. What it actually does is returns all elements that have the specified key, but only if the specified value exists in the map (but it does not necessarily have to be the pair of the specified key). So still at square one.</p>
  
  <p><strong>Edit 3</strong></p>
  
  <p>I got the one key-value pair match query working and as the requirements changed a bit, that's sufficient for now. Still would be nice to know how to solve the initial problem. One key-value pair solution:</p>
  
  <pre><code>@Query("select a from A a JOIN a.metadata md where KEY(md) = :key and :value = md")   
  Page&lt;A&gt; getAllAsThatHaveAtleastOneGivenPairInMetadataMap(@Param("key") String key, @Param("value") String value, Pageable pageable);
  </code></pre>