HQL Query to get fields from @ElementCollection

Question

I'm trying to construct an HQL query to pull properties from an @ElementCollection:

public class Resource {
  @Id
  private String name;

  @ElementCollection()
  @CollectionTable(
          name = "property",
          )
  @SortNatural
  private final SortedMap<String, String> properties =
        Maps.newTreeMap();
}

The property table is using the default column names (properties and properties_key) to store the data (with a foreign key back to my resource table).

I have the following HQL query, where I'm trying to return the keys and values.

final Query q = session.createQuery("select new list(r.name, elements(r.properties)) from Resource r where r.name like :nameFilter");

This is working, and when I call q.list(), I get a List of objects containing the name and values. The problem I'm having is that I can't figure out how to get the keys which are associated with the values. i.e. The data in the properties_key column.

Things I've tried:

1. elements(r.properties_key)
2. elements(r.propertiesKey)
3. elements(r.key)

None of which work.

Is it possible to get this data out? How can I figure out the name of the property to use in my HQL query?

Answer 1

Join the collection and then use index() to get the key.
index(p) is the key and p is the value.

List list = currentSession.createQuery(
"select new list(s.id, index(p), p) from Resource s join s.properties p").list();