ARRAY_MAP
descriptionβ
Use a lambda expression as the input parameter to calculate the corresponding expression for the internal data of other input ARRAY parameters. The number of parameters entered the lambda expression is 1 or more, which must be consistent with the number of input array columns. The scalar functions can be executed in lambda, and aggregate functions are not supported.
Syntaxβ
ARRAY_MAP(lambda, <arr> [ , <arr> ... ] )
Parametersβ
| Parameter | Description |
|---|---|
lambda | A lambda expression with one or more input parameters, which must match the number of input arrays. The lambda can execute valid scalar functions but does not support aggregate functions. |
<arr> | ARRAY array |
Return Valueβ
An ARRAY processed through the lambda expression.
exampleβ
CREATE TABLE array_test2 (
id INT,
c_array1 ARRAY<INT>,
c_array2 ARRAY<INT>
)
duplicate key (id)
distributed by hash(id) buckets 1
properties(
'replication_num' = '1'
);
INSERT INTO array_test2 (id, c_array1, c_array2) VALUES
(1, [1, 2, 3, 4, 5], [10, 20, -40, 80, -100]),
(2, [6, 7, 8], [10, 12, 13]),
(3, [1], [-100]),
(4, NULL, NULL);
select *, array_map(x->x,[1,2,3]) from array_test2 order by id;
+------+-----------------+-------------------------+----------------------------------------+
| id | c_array1 | c_array2 | array_map([x] -> x(0), ARRAY(1, 2, 3)) |
+------+-----------------+-------------------------+----------------------------------------+
| 1 | [1, 2, 3, 4, 5] | [10, 20, -40, 80, -100] | [1, 2, 3] |
| 2 | [6, 7, 8] | [10, 12, 13] | [1, 2, 3] |
| 3 | [1] | [-100] | [1, 2, 3] |
| 4 | NULL | NULL | [1, 2, 3] |
+------+-----------------+-------------------------+----------------------------------------+
select *, array_map(x->x+2,[1,2,3]) from array_test2 order by id;
+------+-----------------+-------------------------+--------------------------------------------+
| id | c_array1 | c_array2 | array_map([x] -> x(0) + 2, ARRAY(1, 2, 3)) |
+------+-----------------+-------------------------+--------------------------------------------+
| 1 | [1, 2, 3, 4, 5] | [10, 20, -40, 80, -100] | [3, 4, 5] |
| 2 | [6, 7, 8] | [10, 12, 13] | [3, 4, 5] |
| 3 | [1] | [-100] | [3, 4, 5] |
| 4 | NULL | NULL | [3, 4, 5] |
+------+-----------------+-------------------------+--------------------------------------------+
select c_array1, c_array2, array_map(x->x,[1,2,3]) from array_test2 order by id;
+-----------------+-------------------------+----------------------------------------+
| c_array1 | c_array2 | array_map([x] -> x(0), ARRAY(1, 2, 3)) |
+-----------------+-------------------------+----------------------------------------+
| [1, 2, 3, 4, 5] | [10, 20, -40, 80, -100] | [1, 2, 3] |
| [6, 7, 8] | [10, 12, 13] | [1, 2, 3] |
| [1] | [-100] | [1, 2, 3] |
| NULL | NULL | [1, 2, 3] |
+-----------------+-------------------------+----------------------------------------+
select c_array1, c_array2, array_map(x->power(x,2),[1,2,3]) from array_test2 order by id;
+-----------------+-------------------------+----------------------------------------------------+
| c_array1 | c_array2 | array_map([x] -> power(x(0), 2.0), ARRAY(1, 2, 3)) |
+-----------------+-------------------------+----------------------------------------------------+
| [1, 2, 3, 4, 5] | [10, 20, -40, 80, -100] | [1, 4, 9] |
| [6, 7, 8] | [10, 12, 13] | [1, 4, 9] |
| [1] | [-100] | [1, 4, 9] |
| NULL | NULL | [1, 4, 9] |
+-----------------+-------------------------+----------------------------------------------------+
select c_array1, c_array2, array_map((x,y)->x+y,c_array1,c_array2) from array_test2 order by id;
+-----------------+-------------------------+----------------------------------------------------------+
| c_array1 | c_array2 | array_map([x, y] -> x(0) + y(1), `c_array1`, `c_array2`) |
+-----------------+-------------------------+----------------------------------------------------------+
| [1, 2, 3, 4, 5] | [10, 20, -40, 80, -100] | [11, 22, -37, 84, -95] |
| [6, 7, 8] | [10, 12, 13] | [16, 19, 21] |
| [1] | [-100] | [-99] |
| NULL | NULL | NULL |
+-----------------+-------------------------+----------------------------------------------------------+
select c_array1, c_array2, array_map((x,y)->power(x,2)+y,c_array1, c_array2) from array_test2 order by id;
+-----------------+-------------------------+----------------------------------------------------------------------+
| c_array1 | c_array2 | array_map([x, y] -> power(x(0), 2.0) + y(1), `c_array1`, `c_array2`) |
+-----------------+-------------------------+----------------------------------------------------------------------+
| [1, 2, 3, 4, 5] | [10, 20, -40, 80, -100] | [11, 24, -31, 96, -75] |
| [6, 7, 8] | [10, 12, 13] | [46, 61, 77] |
| [1] | [-100] | [-99] |
| NULL | NULL | NULL |
+-----------------+-------------------------+----------------------------------------------------------------------+
select *,array_map(x->x=3,c_array1) from array_test2 order by id;
+------+-----------------+-------------------------+----------------------------------------+
| id | c_array1 | c_array2 | array_map([x] -> x(0) = 3, `c_array1`) |
+------+-----------------+-------------------------+----------------------------------------+
| 1 | [1, 2, 3, 4, 5] | [10, 20, -40, 80, -100] | [0, 0, 1, 0, 0] |
| 2 | [6, 7, 8] | [10, 12, 13] | [0, 0, 0] |
| 3 | [1] | [-100] | [0] |
| 4 | NULL | NULL | NULL |
+------+-----------------+-------------------------+----------------------------------------+
select *,array_map(x->x>3,c_array1) from array_test2 order by id;
+------+-----------------+-------------------------+----------------------------------------+
| id | c_array1 | c_array2 | array_map([x] -> x(0) > 3, `c_array1`) |
+------+-----------------+-------------------------+----------------------------------------+
| 1 | [1, 2, 3, 4, 5] | [10, 20, -40, 80, -100] | [0, 0, 0, 1, 1] |
| 2 | [6, 7, 8] | [10, 12, 13] | [1, 1, 1] |
| 3 | [1] | [-100] | [0] |
| 4 | NULL | NULL | NULL |
+------+-----------------+-------------------------+----------------------------------------+
select *,array_map((x,y)->x>y,c_array1,c_array2) from array_test2 order by id;
+------+-----------------+-------------------------+----------------------------------------------------------+
| id | c_array1 | c_array2 | array_map([x, y] -> x(0) > y(1), `c_array1`, `c_array2`) |
+------+-----------------+-------------------------+----------------------------------------------------------+
| 1 | [1, 2, 3, 4, 5] | [10, 20, -40, 80, -100] | [0, 0, 1, 0, 1] |
| 2 | [6, 7, 8] | [10, 12, 13] | [0, 0, 0] |
| 3 | [1] | [-100] | [1] |
| 4 | NULL | NULL | NULL |
+------+-----------------+-------------------------+----------------------------------------------------------+
select array_map(x->cast(x as string), c_array1) from test_array_map_function;
+-----------------+-------------------------------------------------------+
| c_array1 | array_map([x] -> CAST(x(0) AS CHARACTER), `c_array1`) |
+-----------------+-------------------------------------------------------+
| [1, 2, 3, 4, 5] | ['1', '2', '3', '4', '5'] |
| [6, 7, 8] | ['6', '7', '8'] |
| [] | [] |
| NULL | NULL |
+-----------------+-------------------------------------------------------+
