Group by returning lists or values in spring jpa

I’m trying to do something really simple, but I somehow can’t make it work without a native query.

Here is my entity:

public class A {
  private long id
  private String key
  private UUID value
}

In my repository, I have the following method:

@Query(value = """
        SELECT key as key,
            ARRAY_AGG(value) AS values
        FROM table_a
        WHERE key IN (:keys)
        GROUP BY key
""", nativeQuery = true)
List<MyProjection> getValuesByKeys(Collection<String> keys);

with

public interface MyProjection {
  String getKey();
  UUID[] getValues();
}

It works, and returns me a list of MyProjection objects, but I want to make it not a native query. I tried the following a various variations, but always have that error:

Caused by: org.hibernate.query.SemanticException: 'WITHIN GROUP' or 'OVER' clause is mandatory for ordered set aggregate function: array_agg

@Query(value = """
        SELECT new MyProjection(key, ARRAY_AGG(a.value))
        FROM A a
        WHERE a.key IN (:keys)
        GROUP BY a.key
""")
List<MyProjection> getValuesByKeys(Collection<String> keys);

I suppose array_agg is not the good method, but I can’t find any info on what the correct call is.

Thanks in advance.

You need to sign in to view this answers

Related Post