CriteriaQuery with array aggregation

Question

I want to perform a Query into a custom Data-Access-Class. Unfortunately I am stuck midway.

class User {
  private Long id
  private String name
  private Set<Group> groups
...
}

class Group {
  private Long id
  private String name
...
}

I want to construct the equivalent to the following sql-Statement

select user.id, user.name, ARRAY_AGG(user_groups.groups_id) from user left join user_groups ON user_groups.user_id = user.id group by user.id

I tried the following

CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery query = criteriaBuilder.createQuery(CustomUser.class)
Root<ENTITY> entryRoot = query.from(User.class);
query.groupBy(entryRoot.get("ident");

If I multiselect with a join, it fails, because an aggregate function is missing

query.multiselect(entryRoot.get("id"), entryRoot.get("name"), entryRoot.join("groups", JoinType.Left)

I tried to add an array aggregation into multiselect

arraySelection = CompoundSelection<Object[]> array = criteriaBuilder.array(entryRoot.join("groups", JoinType.LEFT));
query.multiselect(entryRoot.get("id"), entryRoot.get("name"), arraySelection)

This unfortunately fails because Compound-Selections are not allowed with other Selections in a multiselect.

How can I perform an array aggregation with a CriteriaQuery?

I am aware, that I could simply use

CriteriaQuery query = criteriaBuilder.createQuery(User.class)

instead of

CriteriaQuery query = criteriaBuilder.createQuery(CustomUser.class)

but I want to add further joins of only inversely related Entities. Actually I am stuck in reconstructing the things done automatically, do add more functionality later.

Answer 1

To use the Array-Aggregation available in Postgres an extended Postgres-Dialect with a defined array_agg function.

public class ExtendedPostgreSQL9Dialect extends PostgreSQL9Dialect {

    public ExtendedPostgreSQL9Dialect() {
        super();
        registerFunction("array_agg", new StandardSQLFunction("array_agg", StandardBasicTypes.STRING));
    }
}

The dialect has to be configured at the configuration yml or in code at

jpa.hibernate.dialect:ExtendedPostgreSQL9Dialect

With this a subquery for inverse joins can be constructed

        Subquery<String> subquery = query.subquery(String.class);
        Root<> subFrom = subquery.from(parentClass);
        Join<Object, Object> join = subFrom.join(fieldName);
        join.on(criteriaBuilder.equal(entryRoot.get("id"), join.get("id")));
        Expression<String> function = criteriaBuilder.function("array_agg", String.class, join.getParent()
                                                                                              .get("id"));
        subquery.select(function);

In the query itself the selection of the subquery has to be added

query.multiselect(..., subquery.getSelection());