Join map and refer to its key/value in HQL

Question

Suppose I have a map:

        <map name="externalIds" table="album_external_ids">
            <key column="album_id" not-null="true"/>
            <map-key-many-to-many class="Major" column="major_id"/>
            <element column="external_id" type="string" not-null="true"/>
        </map> 

How do I make a HQL meaning "select entities where map key's id == :foo and map value == :bar"?

I can join it using select album from Album album join album.externalIds ids But how would I then refer to ids' key and value? ids.key.id = :foo and ids.value = :bar doesn't work, and hibernate doc is silent on this topic.

Naive approaches that didn't work:

select album 
from Album album 
join album.externalIds externalId
    where index(externalId).id = :foo and externalId = :bar

and

select album 
from Album album 
join album.externalIds externalId 
join index(externalId) major
    where major.id = :foo and externalId = :bar

Answer 1

I believe that your query should look like:

select album from Album album where album.externalIds['foo'] = 'bar'

Hope that helps,

Vincent Giguère