how to group into multiple arrays?

[KS Chong]

hello, 

I've a clickhouse table1 that's like this:

   ID .     data

    1 .       a

    1 .       b

    1 .       c

    2 .       c

    2 .       d

    3 .       e

    3 .       f

    3 .       g

I want this

   ID .     data

    1 .       [a, b, c]

    2 .       [c, d]

    3 .       [e, f, g]

I tried this but failed:

select ID, groupArray(data) from table1 group by ID

what can I do?

[Denis Zhuravlev]

>I tried this but failed:

 Failed how ?

select id, groupArray(data) from (select number%3 id, number data from numbers (10)) group by id

┌─id─┬─groupArray(data)─┐
│  0 │ [0,3,6,9]        │
│  1 │ [1,4,7]          │
│  2 │ [2,5,8]          │
└────┴──────────────────┘

[KS Chong]

thanks for answering, Denis,

I got this error:

org.jkiss.dbeaver.model.exec.DBCException: Can't extract array data from JDBC array

at org.jkiss.dbeaver.model.impl.jdbc.data.JDBCCollection.makeCollectionFromArray(JDBCCollection.java:240)

at org.jkiss.dbeaver.model.impl.jdbc.data.handlers.JDBCArrayValueHandler.getValueFromObject(JDBCArrayValueHandler.java:62)

at org.jkiss.dbeaver.model.impl.jdbc.data.handlers.JDBCArrayValueHandler.getValueFromObject(JDBCArrayValueHandler.java:1)

at org.jkiss.dbeaver.model.impl.jdbc.data.handlers.JDBCComplexValueHandler.fetchColumnValue(JDBCComplexValueHandler.java:50)

at org.jkiss.dbeaver.model.impl.jdbc.data.handlers.JDBCAbstractValueHandler.fetchValueObject(JDBCAbstractValueHandler.java:49)

at org.jkiss.dbeaver.ui.controls.resultset.ResultSetDataReceiver.fetchRow(ResultSetDataReceiver.java:122)

at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.fetchQueryData(SQLQueryJob.java:755)

at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeStatement(SQLQueryJob.java:484)

at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.lambda$0(SQLQueryJob.java:407)

at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:146)

at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeSingleQuery(SQLQueryJob.java:405)

at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.extractData(SQLQueryJob.java:849)

at org.jkiss.dbeaver.ui.editors.sql.SQLEditor$QueryResultsContainer.readData(SQLEditor.java:2727)

at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.lambda$0(ResultSetJobDataRead.java:98)

at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:146)

at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.run(ResultSetJobDataRead.java:96)

at org.jkiss.dbeaver.model.runtime.AbstractJob.run(AbstractJob.java:102)

at org.eclipse.core.internal.jobs.Worker.run(Worker.java:63)

Caused by: org.jkiss.dbeaver.model.exec.DBCException: SQL Error

at org.jkiss.dbeaver.model.impl.jdbc.data.JDBCCollection.makeCollectionFromArray(JDBCCollection.java:224)

... 17 more

Caused by: java.sql.SQLFeatureNotSupportedException

at ru.yandex.clickhouse.ClickHouseArray.getResultSet(ClickHouseArray.java:70)

at org.jkiss.dbeaver.model.impl.jdbc.data.JDBCCollection.makeCollectionFromResultSet(JDBCCollection.java:313)

at org.jkiss.dbeaver.model.impl.jdbc.data.JDBCCollection.makeCollectionFromArray(JDBCCollection.java:222)

... 17 more

[KS Chong]

I'm using Clickhouse driver on Dbeaver Ubuntu.

On Thursday, July 11, 2019 at 9:35:34 AM UTC+8, Denis Zhuravlev wrote:

[KS Chong]

I just tried your example. it failed with the same error.

On Thursday, July 11, 2019 at 9:35:34 AM UTC+8, Denis Zhuravlev wrote:

[Denis Zhuravlev]

 Not a CH issue. Java/JDBC just does not support Arrays as datatype.

try

select id, toString(groupArray(data)) from (select number%3 id, number data from numbers (10)) group by id

https://github.com/yandex/clickhouse-jdbc/issues/323

Basically it could not be solved because of JDBC will never support arrays especially nested.

[KS Chong]

Denis, I confirm that toString() solves my original problem as well. it's a pity. let me figure out how to resolve this issue then,

Thank you!

[Badri vamsi krishna]

I have a similar issue but with
Id. Array
1. ['abs', 'etw','wt']
2. ['ww','tt']
1. ['abs', 'ww']

I want output like
1. ['abs','etw','wt','ww']
2. ['ww','tt']
I tried using arrayconcat, groupuniqarray .but didn't work

[Denis Zhuravlev]

groupArrayArray

select k, groupArrayArray(v) from (
select 1 k, [7,8,1] v union all select 1 k, [] v union all select 2 k, [1,2,3] v union all select 1 k, [3,9,7]
) group by k

┌─k─┬─groupArrayArray(v)─┐
│ 1 │ [7,8,1,3,9,7] │
│ 2 │ [1,2,3] │
└───┴────────────────────┘

Flatten

select k, flatten(groupArray(v)) from (
select 1 k, [7,8,1] v union all select 1 k, [] v union all select 2 k, [1,2,3] v
) group by k
┌─k─┬─flatten(groupArray(v))─┐
│ 1 │ [7,8,1] │
│ 2 │ [1,2,3] │
└───┴────────────────────────┘