ARRAY_JOIN
更新时间：2025-10-17
描述
根据分隔符 (sep) 和替换 NULL 的字符串 (null_replace), 将数组中的所有元素组合成一个新的字符串。
语法
SQL
1ARRAY_JOIN(<arr> , <sep> [, <null_replace>])参数
| Parameter | Description |
|---|---|
<arr> |
需要 join 的数组 |
<sep> |
分隔符 |
<null_replace> |
替换 NULL 的字符串 |
返回值
返回一个新的字符串，特殊情况：
- 若 sep 为 NULL，则返回值为 NULL。
- 若 null_replace 为 NULL，则返回值也为 NULL。
- 若 sep 为空字符串，则不应用任何分隔符。
- 若 null_replace 为空字符串或者不指定，则直接丢弃数组中的 NULL 元素。
举例
SQL
1CREATE TABLE array_test (
2 k1 INT,
3 k2 ARRAY<INT>
4)
5 duplicate key (k1)
6distributed by hash(k1) buckets 1
7properties(
8 'replication_num' = '1'
9);
10
11INSERT INTO array_test VALUES
12 (1, [1, 2, 3, 4, 5]),
13 (2, [6, 7, 8]),
14 (3, []),
15 (4, NULL),
16 (5, [1, 2, 3, 4, 5, 4, 3, 2, 1]),
17 (6, [1, 2, 3, NULL]),
18 (7, [4, 5, 6, NULL, NULL]);
19select k1, k2, array_join(k2, '_', 'null') from array_test order by k1;
Text
1+------+-----------------------------+------------------------------------+
2| k1 | k2 | array_join(`k2`, '_', 'null') |
3+------+-----------------------------+------------------------------------+
4| 1 | [1, 2, 3, 4, 5] | 1_2_3_4_5 |
5| 2 | [6, 7, 8] | 6_7_8 |
6| 3 | [] | |
7| 4 | NULL | NULL |
8| 5 | [1, 2, 3, 4, 5, 4, 3, 2, 1] | 1_2_3_4_5_4_3_2_1 |
9| 6 | [1, 2, 3, NULL] | 1_2_3_null |
10| 7 | [4, 5, 6, NULL, NULL] | 4_5_6_null_null |
11+------+-----------------------------+------------------------------------+
SQL
1select k1, k2, array_join(k2, '_') from array_test order by k1;
Text
1+------+-----------------------------+----------------------------+
2| k1 | k2 | array_join(`k2`, '_') |
3+------+-----------------------------+----------------------------+
4| 1 | [1, 2, 3, 4, 5] | 1_2_3_4_5 |
5| 2 | [6, 7, 8] | 6_7_8 |
6| 3 | [] | |
7| 4 | NULL | NULL |
8| 5 | [1, 2, 3, 4, 5, 4, 3, 2, 1] | 1_2_3_4_5_4_3_2_1 |
9| 6 | [1, 2, 3, NULL] | 1_2_3 |
10| 7 | [4, 5, 6, NULL, NULL] | 4_5_6 |
11+------+-----------------------------+----------------------------+
SQL
1CREATE TABLE array_test01 (
2 k1 INT,
3 k2 ARRAY<STRING>
4)
5duplicate key (k1)
6distributed by hash(k1) buckets 1
7properties(
8 'replication_num' = '1'
9);
10
11INSERT INTO array_test01 VALUES
12(1, ['a', 'b', 'c', 'd']),
13(2, ['e', 'f', 'g', 'h']),
14(3, [NULL, 'a', NULL, 'b', NULL, 'c']),
15(4, ['d', 'e', NULL, ' ']),
16(5, [' ', NULL, 'f', 'g']);
17select k1, k2, array_join(k2, '_', 'null') from array_test01 order by k1;
Text
1+------+-----------------------------------+------------------------------------+
2| k1 | k2 | array_join(`k2`, '_', 'null') |
3+------+-----------------------------------+------------------------------------+
4| 1 | ['a', 'b', 'c', 'd'] | a_b_c_d |
5| 2 | ['e', 'f', 'g', 'h'] | e_f_g_h |
6| 3 | [NULL, 'a', NULL, 'b', NULL, 'c'] | null_a_null_b_null_c |
7| 4 | ['d', 'e', NULL, ' '] | d_e_null_ |
8| 5 | [' ', NULL, 'f', 'g'] | _null_f_g |
9+------+-----------------------------------+------------------------------------+
SQL
1select k1, k2, array_join(k2, '_') from array_test01 order by k1;
Text
1+------+-----------------------------------+----------------------------+
2| k1 | k2 | array_join(`k2`, '_') |
3+------+-----------------------------------+----------------------------+
4| 1 | ['a', 'b', 'c', 'd'] | a_b_c_d |
5| 2 | ['e', 'f', 'g', 'h'] | e_f_g_h |
6| 3 | [NULL, 'a', NULL, 'b', NULL, 'c'] | a_b_c |
7| 4 | ['d', 'e', NULL, ' '] | d_e_ |
8| 5 | [' ', NULL, 'f', 'g'] | _f_g |
9+------+-----------------------------------+----------------------------+