Menu

arrayJoin in ClickHouse: Why Your Rows Are Duplicating (and How to Control It)
🖼️

arrayJoin in ClickHouse: Why Your Rows Are Duplicating (and How to Control It)

DEV Community·Mohamed Hussain S·7 months ago
#7wHLVViO
#why#clickhouse#sql#arrayjoin#rows
Reading 0:00
15s threshold

When working with arrays in ClickHouse, arrayJoin feels straightforward.

Until your query suddenly returns far more rows than expected.

The Use Case

Let’s say you have a table like this:

CREATE TABLE events ( user_id UInt32 , actions Array ( String ) ) ENGINE = MergeTree ORDER BY user_id ;

Enter fullscreen mode

Exit fullscreen mode

Example row:

user_id : 1 actions : [ ' click' , ' scroll' , ' purchase' ]

Enter fullscreen mode

Exit fullscreen mode

Now you want each action as a separate row.

The Tool: arrayJoin

SELECT user_id , arrayJoin ( actions ) AS action FROM events ;

Enter fullscreen mode

Exit fullscreen mode

Output:

1 click 1 scroll 1 purchase

Enter fullscreen mode

Exit fullscreen mode

So far, everything looks correct.

Where Things Go Wrong

Now let’s say you write:

SELECT user_id , arrayJoin ( actions ) AS action , arrayJoin ( actions ) AS action2 FROM events ;

Enter fullscreen mode

Exit fullscreen mode

You might expect:

3 rows

But you actually get:

9 rows

Why This…

Continue reading — create a free account

Join HashtagPLUS to read full articles, follow hashtags, vote, and join the conversation.