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