It is possible to create flat array from query results in clickhouse?

Question

Query

select *
from (
    select [1] a
    union all
    select [1,2] a
    union all
    select [2] a
) q;

Result (3 rows)

[1]
[1,2]
[2]

Expected result (1 row)

[1,1,2,2]

It is possible ?

Answer 1

Alternate way using Array-combinator:

SELECT groupArrayArray(*)
FROM 
(
    SELECT [1] AS a
    UNION ALL
    SELECT [1, 2] AS a
    UNION ALL
    SELECT [2] AS a
) AS q
/* result
┌─groupArrayArray(a)─┐
│ [1,1,2,2]          │
└────────────────────┘
*/

Answer 2

Yes, sure. You just need a couple of array functions.

select arrayFlatten(groupArray(*))
from (
    select [1] a
    union all
    select [1,2] a
    union all
    select [2] a
) q;

groupyArray gives you [[1],[1,2],[2]], essentially grouping all the results in one array. arrayFlatten flattens the above array, resulting in [1,1,2,2].