how to use postgresql array_agg with JPA+hibernate 3.6

Question

I make this request with no problem:

select ac.reg, param.alias, array_agg(numValue) 
from Sample   
group by ac.reg, param.alias  
order by ac.reg ASC

now, I want more than the mean value. how can I get all the values as an array? I use postgresql as DB, an I know there is a array_agg method that returns exaclty what I want. but such aggregation seems not to be implemented in hibernate.

select ac.reg, param.alias, array_agg(numValue) 
from Sample   
group by ac.reg, param.alias  
order by ac.reg ASC

raises the exception:

A java.lang.IllegalStateException has been caught, No data type for node: org.hibernate.hql.ast.tree.MethodNode 
\-[METHOD_CALL] MethodNode: '(' +-[METHOD_NAME] IdentNode: 'array_agg' {originalText=array_agg} \-[EXPR_LIST] SqlNode: 'exprList' \-[DOT] DotNode: 'sample0_.num_value' 
{propertyName=numValue,dereferenceType=ALL,propertyPath=numValue,path={synthetic-alias}.numValue,tableAlias=sample0_,className=models.Sample,classAlias=null} +-[IDENT] IdentNode: '{synthetic-alias}' {originalText={synthetic-alias}} \-[IDENT] IdentNode: 'numValue' {originalText=numValue}

Arrays are introduced in JDBC4, but hibernate (and I think most jpa implementations) does not supprot them. In hibernate, you can use a custom UserType to use arrays in native queries... — pozs
– pozs, Commented Aug 1, 2015 at 13:22
... But from your error message, it seems you try to run a jpql / hql. That wont work either: to use a function in hibernate, the underlying dialect must register it. Even the PostgreSQL94Dialect does not register the array_agg() function. — pozs
– pozs, Commented Aug 1, 2015 at 13:23
@pozs : you mean that there is no solution to get something like [{reg1, alias1, [1, 3, 29, 3]}, {reg1, alias2, [3, 9, 6, 0]},...] ? Do you suggest I use native queries? — dao hodac
– dao hodac, Commented Aug 1, 2015 at 14:32
there is no easy solution. you could try with a custom usertype & a custom dialect, but I don't think it's worth it. — pozs
– pozs, Commented Aug 1, 2015 at 17:55
If something is not valid with JPQL, then put it as a NATIVE query (i.e SQL). You then lose datastore independence, but that might not be a concern to you — Neil Stockton
– Neil Stockton, Commented Aug 4, 2015 at 8:15

Grim · Accepted Answer · 2025-10-19 12:07:29Z

0

Try


select
  ac.reg,
  param.alias,
  array_agg(numValue) within group (order by numValue)
from
  Sample   
group by
  ac.reg,
  param.alias  
order by
  ac.reg ASC

answered Oct 19 at 12:07

Grim

2,13312 gold badges63 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

how to use postgresql array_agg with JPA+hibernate 3.6

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related