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

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 ; Example row: user_id : 1 actions : [ ' click' , ' scroll' , ' purchase' ] Now you want each action as a separate row. The Tool: arrayJoin SELECT user_id , arrayJoin ( actions ) AS action FROM events ; Output: