H2 Database: Get rows from subquery as array

Question

I have a query which works fine with PostgreSQL, and I need to use it on H2Database.

Important for the tables is basically only an id integer field.

The example-query on PostgreSQL with the result is here:

select id, 
array_to_string(
    array(select id from table1)
,',') 
from table2
order by id

Result:

id | array_to_string
2  | 1,3,4,5,2
3  | 1,3,4,5,2
4  | 1,3,4,5,2
6  | 1,3,4,5,2
7  | 1,3,4,5,2
8  | 1,3,4,5,2
9  | 1,3,4,5,2
10 | 1,3,4,5,2

For H2, I implemented the user-defined functions array_to_string and array as follows:

public class H2Functions {

    public static String arrayToString(final Object[] array, final String separator) {
        return StringUtils.join(array, separator);
    }

    public static Object array(final Object row) {
        return "???";
    }

}

The problem is that I can not implement array as I don't know what it get's passed.

The query fails with:

Scalar subquery contains more than one row;

How can I convince H2 to return something which array can work with ?

Unrelated, but: retrieving all ids from table1 for each row of table2 seems very strange to me. Are you sure you do not want a co-related sub-query? Or even a proper join and group by between those tables? — user330315
– user330315, Commented Jan 21, 2016 at 8:16
The query is just an example query, in my application there are clauses. — yglodt
– yglodt, Commented Jan 21, 2016 at 10:17

score 6 · Accepted Answer · 2016-01-21 08:25:42Z

6

You don't really want the rows as an array, you want them as a comma separated list. And array_to_string( array(select id from table1),',') is needlessly complicated in Postgres to begin with. It can be simplified to

select id, 
       (select string_agg(id::text, ',') from table1) as id_list
from table2
order by id

And this makes clear that you can simply use H2's group_concat() which is the equivalent to Postgres' string_agg():

select id, 
       (select group_concat(id separator ',') from table1) as id_list
from table2
order by id

edited Jan 21, 2016 at 8:25

answered Jan 21, 2016 at 8:15

user330315

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

Comments

Grigory Kislin · Accepted Answer · 2020-09-22 19:45:37Z

1

Since 1.4.197 H2 vervion also aggregate the value into an array function ARRAY_AGG is available.
See https://www.h2database.com/html/functions-aggregate.html#array_agg

answered Sep 22, 2020 at 19:45

Grigory Kislin

18.2k12 gold badges136 silver badges220 bronze badges

Collectives™ on Stack Overflow

H2 Database: Get rows from subquery as array

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related