CODEWITHSUNDEEP
clickhouse
ali
ali

Reputation: 101

how to use array join in Clickhouse

I'm trying to split 2 arrays using arrayJoin()

my table:

create table test_array(
    col1 Array(INT),
    col2 Array(INT),
    col3 String
)
engine = TinyLog;

then i insert these values:

insert into test_array values ([1,2],[11,22],'Text');
insert into test_array values ([5,6],[55,66],'Text');

when i split the first array in col1 the result will be like this :

enter image description here

but what i need is to split col1 and col2 and add them in the select.

I tried this query but it didn't work.

select arrayJoin(col1)  ,arrayJoin(col2) ,col1 , col2 ,col3 from test_array;

enter image description here

how can i edit the query to remove the highlighted rows in the picture?

Thanks.

Upvotes: 1

Views: 8510

Answers (2)

Denny Crane
Denny Crane

Reputation: 13300

one more way -- tuple()

SELECT
    untuple(arrayJoin(arrayZip(col1, col2))),
    col3
FROM test_array

┌─_ut_1─┬─_ut_2─┬─col3─┐
│     1 │    11 │ Text │
│     2 │    22 │ Text │
│     5 │    55 │ Text │
│     6 │    66 │ Text │
└───────┴───────┴──────┘

Upvotes: 3

vladimir
vladimir

Reputation: 15218

The serial calls of arrayJoin produces the cartesian product, to avoid it use ARRAY JOIN:

SELECT
    c1,
    c2,
    col1,
    col2,
    col3
FROM test_array
ARRAY JOIN
    col1 AS c1,
    col2 AS c2

/*
┌─c1─┬─c2─┬─col1──┬─col2────┬─col3─┐
│  1 │ 11 │ [1,2] │ [11,22] │ Text │
│  2 │ 22 │ [1,2] │ [11,22] │ Text │
│  5 │ 55 │ [5,6] │ [55,66] │ Text │
│  6 │ 66 │ [5,6] │ [55,66] │ Text │
└────┴────┴───────┴─────────┴──────┘
*/

Upvotes: 5

Related Questions