7

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}
dao hodac
  • 361
  • 2
  • 5
  • 14
  • 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`](http://stackoverflow.com/questions/21940642/hibernate-postgres-array-type) to use arrays in native queries... – pozs Aug 01 '15 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](https://github.com/hibernate/hibernate-orm/tree/master/hibernate-core/src/main/java/org/hibernate/dialect) must register it. Even the `PostgreSQL94Dialect` does not register the `array_agg()` function. – pozs Aug 01 '15 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 Aug 01 '15 at 14:32
  • 1
    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 Aug 01 '15 at 17:55
  • OK, then how can I get this structure? I have to get them without grouping and doing the job in JAVA? – dao hodac Aug 02 '15 at 07:29
  • "array_agg" is not a valid JPQL keyword, consequently your query is invalid. – Neil Stockton Aug 03 '15 at 07:04
  • @NeilStockton : that's clear. What workaround would you suggest? – dao hodac Aug 04 '15 at 08:14
  • 1
    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 Aug 04 '15 at 08:15
  • make a database view with your query and then in jpa select from that view containing a field: (example for eclipselink 2.6): @Array(databaseType="TEXT[]") private ListmyArray; – Steve Mar 06 '19 at 20:42

0 Answers0