Union by element of two array of array in clickhouse

user_6292205 0
Viewed 320

I have the following two columns (floats:

+-------------+-------------+
|col1         |col2         |
+-------------+-------------+
|[0.5,0.6]    |[900,1000]    |
|[0.7,0.8,0.9]|[1100,1200,1300]|
+-------------+-------------+

I would like the following output:

+-------------+-------------   +----------------------------+
|col1         |col2            |col3                        |
+-------------+-------------   +----------------------------+
|[0.5,0.6]    |[900,1000]      |[0.5 900 , 0.6 1000]        | 
|[0.7,0.8,0.9]|[1100,1200,1300]|[0.7 1100,0.8 1200,0.9 1300]|
+-------------+----------- ----+----------------------------+

I have tried ArrayZip but I'm getting an exception "can t extract array data from JDBC array

Thank you for your help.

clickhouse sql
1 Answers

JDBC driver does not support complex data types like Array(Tuple(String), you can convert them in CH toString.

SELECT
    [0.5, 0.6] AS col1,
    [900, 1000] AS col2,
    arrayMap((i, j) -> concat(toString(i), ' ', toString(j)), col1, col2) AS r

┌─col1──────┬─col2───────┬─r──────────────────────┐
│ [0.5,0.6] │ [900,1000] │ ['0.5 900','0.6 1000'] │
└───────────┴────────────┴────────────────────────┘


SELECT
    [0.5, 0.6] AS col1,
    [900, 1000] AS col2,
    toString(arrayMap((i, j) -> concat(toString(i), ' ', toString(j)), col1, col2)) AS r

┌─col1──────┬─col2───────┬─r──────────────────────┐
│ [0.5,0.6] │ [900,1000] │ ['0.5 900','0.6 1000'] │
└───────────┴────────────┴────────────────────────┘


SELECT
    [0.5, 0.6] AS col1,
    [900, 1000] AS col2,
    concat('[', arrayStringConcat(arrayMap((i, j) -> concat(toString(i), ' ', toString(j)), col1, col2), ', '), ']') AS r

┌─col1──────┬─col2───────┬─r───────────────────┐
│ [0.5,0.6] │ [900,1000] │ [0.5 900, 0.6 1000] │
└───────────┴────────────┴─────────────────────┘


SELECT
    [0.5, 0.6] AS col1,
    [900, 1000] AS col2,
    arrayZip(col1, col2)

Query id: 8a7aa26b-71f7-4544-b5c7-79e47f1c6453

┌─col1──────┬─col2───────┬─arrayZip([0.5, 0.6], [900, 1000])─┐
│ [0.5,0.6] │ [900,1000] │ [(0.5,900),(0.6,1000)]            │
└───────────┴────────────┴───────────────────────────────────┘


SELECT
    [0.5, 0.6] AS col1,
    [900, 1000] AS col2,
    toString(arrayZip(col1, col2))

┌─col1──────┬─col2───────┬─toString(arrayZip([0.5, 0.6], [900, 1000]))─┐
│ [0.5,0.6] │ [900,1000] │ [(0.5,900),(0.6,1000)]                      │
└───────────┴────────────┴─────────────────────────────────────────────┘
user_116443080
Related