Array JOIN 子句允许在数据表的内部，与数组或者嵌套的字段进行JOIN操作，从而将一行数据变多行。适用于行转列操作。

Clickhouse> create table city(province String, city Array(String)) ENGINE=Log;CREATE TABLE city
(`province` String, `city` Array(String)
)
ENGINE = LogOk.0 rows in set. Elapsed: 0.012 sec. Clickhouse> insert into city values('hubei',['wuhan','xiangyang']),('guangdong',['guangzhou','shenzhen','zhuhai']);insert into city values('beijing',[]),('shanghai',[]);查看原始数据：Clickhouse> select * from city FORMAT PrettyCompactMonoBlock;SELECT *
FROM city
FORMAT PrettyCompactMonoBlock┌─province──┬─city──────────────────────────────┐
│ hubei     │ ['wuhan','xiangyang']             │
│ guangdong │ ['guangzhou','shenzhen','zhuhai'] │
│ beijing   │ []                                │
│ shanghai  │ []                                │
└───────────┴───────────────────────────────────┘4 rows in set. Elapsed: 0.003 sec. 1.INNER ARRAY JOIN:Clickhouse> select province,city from city array join city;SELECT province, city
FROM city
ARRAY JOIN city┌─province──┬─city──────┐
│ hubei     │ wuhan     │
│ hubei     │ xiangyang │
│ guangdong │ guangzhou │
│ guangdong │ shenzhen  │
│ guangdong │ zhuhai    │
└───────────┴───────────┘5 rows in set. Elapsed: 0.003 sec. Clickhouse> select province,city original_city,new_city from city array join city as new_city;SELECT province, city AS original_city, new_city
FROM city
ARRAY JOIN city AS new_city┌─province──┬─original_city─────────────────────┬─new_city──┐
│ hubei     │ ['wuhan','xiangyang']             │ wuhan     │
│ hubei     │ ['wuhan','xiangyang']             │ xiangyang │
│ guangdong │ ['guangzhou','shenzhen','zhuhai'] │ guangzhou │
│ guangdong │ ['guangzhou','shenzhen','zhuhai'] │ shenzhen  │
│ guangdong │ ['guangzhou','shenzhen','zhuhai'] │ zhuhai    │
└───────────┴───────────────────────────────────┴───────────┘5 rows in set. Elapsed: 0.006 sec. 2. LEFT Array JOIN :Clickhouse> select province,city original_city,new_city from city LEFT array join city as new_city FORMAT PrettyCompactMonoBlock;SELECT province, city AS original_city, new_city
FROM city
LEFT ARRAY JOIN city AS new_city
FORMAT PrettyCompactMonoBlock┌─province──┬─original_city─────────────────────┬─new_city──┐
│ hubei     │ ['wuhan','xiangyang']             │ wuhan     │
│ hubei     │ ['wuhan','xiangyang']             │ xiangyang │
│ guangdong │ ['guangzhou','shenzhen','zhuhai'] │ guangzhou │
│ guangdong │ ['guangzhou','shenzhen','zhuhai'] │ shenzhen  │
│ guangdong │ ['guangzhou','shenzhen','zhuhai'] │ zhuhai    │
│ beijing   │ []                                │           │
│ shanghai  │ []                                │           │
└───────────┴───────────────────────────────────┴───────────┘7 rows in set. Elapsed: 0.003 sec. 

参考：

https://clickhouse.tech/docs/en/sql-reference/statements/select/array-join/