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…