ARRAY_FILTER
更新时间：2025-10-17
描述
使用 lambda 表达式作为输入参数，计算筛选另外的输入参数 ARRAY 列的数据。 并过滤掉在结果中 0 和 NULL 的值。
语法
SQL
1ARRAY_FILTER(<lambda>, <arr>)
2ARRAY_FILTER(<arr>, <filter_column>)参数
| 参数 | 说明 |
|---|---|
<lambda> |
lambda 表达式，表达式中输入的参数为 1 个或多个，必须和后面的输入 array 列数量一致。在 lambda 中可以执行合法的标量函数，不支持聚合函数等。 |
<arr> |
ARRAY 数组 |
返回值
对其输入 ARRAY 参数的内部数据做对应表达式计算。过滤掉在结果中 0 和 NULL 的值
举例
SQL
1select c_array,array_filter(c_array,[0,1,0]) from array_test;
Text
1+-----------------+----------------------------------------------------+
2| c_array | array_filter(`c_array`, ARRAY(FALSE, TRUE, FALSE)) |
3+-----------------+----------------------------------------------------+
4| [1, 2, 3, 4, 5] | [2] |
5| [6, 7, 8] | [7] |
6| [] | [] |
7| NULL | NULL |
8+-----------------+----------------------------------------------------+
SQL
1select array_filter(x->(x > 1),[1,2,3,0,null]);
Text
1+----------------------------------------------------------------------------------------------+
2| array_filter(ARRAY(1, 2, 3, 0, NULL), array_map([x] -> (x(0) > 1), ARRAY(1, 2, 3, 0, NULL))) |
3+----------------------------------------------------------------------------------------------+
4| [2, 3] |
5+----------------------------------------------------------------------------------------------+
SQL
1select *, array_filter(x->x>0,c_array2) from array_test2;
Text
1+------+-----------------+-------------------------+------------------------------------------------------------------+
2| id | c_array1 | c_array2 | array_filter(`c_array2`, array_map([x] -> x(0) > 0, `c_array2`)) |
3+------+-----------------+-------------------------+------------------------------------------------------------------+
4| 1 | [1, 2, 3, 4, 5] | [10, 20, -40, 80, -100] | [10, 20, 80] |
5| 2 | [6, 7, 8] | [10, 12, 13] | [10, 12, 13] |
6| 3 | [1] | [-100] | [] |
7| 4 | NULL | NULL | NULL |
8+------+-----------------+-------------------------+------------------------------------------------------------------+
SQL
1select *, array_filter(x->x%2=0,c_array2) from array_test2;
Text
1+------+-----------------+-------------------------+----------------------------------------------------------------------+
2| id | c_array1 | c_array2 | array_filter(`c_array2`, array_map([x] -> x(0) % 2 = 0, `c_array2`)) |
3+------+-----------------+-------------------------+----------------------------------------------------------------------+
4| 1 | [1, 2, 3, 4, 5] | [10, 20, -40, 80, -100] | [10, 20, -40, 80, -100] |
5| 2 | [6, 7, 8] | [10, 12, 13] | [10, 12] |
6| 3 | [1] | [-100] | [-100] |
7| 4 | NULL | NULL | NULL |
8+------+-----------------+-------------------------+----------------------------------------------------------------------+
SQL
1select *, array_filter(x->(x*(-10)>0),c_array2) from array_test2;
Text
1+------+-----------------+-------------------------+----------------------------------------------------------------------------+
2| id | c_array1 | c_array2 | array_filter(`c_array2`, array_map([x] -> (x(0) * (-10) > 0), `c_array2`)) |
3+------+-----------------+-------------------------+----------------------------------------------------------------------------+
4| 1 | [1, 2, 3, 4, 5] | [10, 20, -40, 80, -100] | [-40, -100] |
5| 2 | [6, 7, 8] | [10, 12, 13] | [] |
6| 3 | [1] | [-100] | [-100] |
7| 4 | NULL | NULL | NULL |
8+------+-----------------+-------------------------+----------------------------------------------------------------------------+
SQL
1select *, array_filter(x->x>0, array_map((x,y)->(x>y), c_array1,c_array2)) as res from array_test2;
Text
1+------+-----------------+-------------------------+--------+
2| id | c_array1 | c_array2 | res |
3+------+-----------------+-------------------------+--------+
4| 1 | [1, 2, 3, 4, 5] | [10, 20, -40, 80, -100] | [1, 1] |
5| 2 | [6, 7, 8] | [10, 12, 13] | [] |
6| 3 | [1] | [-100] | [1] |
7| 4 | NULL | NULL | NULL |
8+------+-----------------+-------------------------+--------+