跳到主要内容
跳到主要内容

array_remove

array_remove

SinceVersion 1.2.0

array_remove

description

Syntax

ARRAY<T> array_remove(ARRAY<T> arr, T val)

返回移除所有的指定元素后的数组,如果输入参数为NULL,则返回NULL

notice

仅支持向量化引擎中使用

example

mysql> set enable_vectorized_engine=true;

mysql> select array_remove(['test', NULL, 'value'], 'value');
+-----------------------------------------------------+
| array_remove(ARRAY('test', NULL, 'value'), 'value') |
+-----------------------------------------------------+
| [test, NULL] |
+-----------------------------------------------------+

mysql> select k1, k2, array_remove(k2, 1) from array_type_table_1;
+------+--------------------+-----------------------+
| k1 | k2 | array_remove(`k2`, 1) |
+------+--------------------+-----------------------+
| 1 | [1, 2, 3] | [2, 3] |
| 2 | [1, 3] | [3] |
| 3 | NULL | NULL |
| 4 | [1, 3] | [3] |
| 5 | [NULL, 1, NULL, 2] | [NULL, NULL, 2] |
+------+--------------------+-----------------------+

mysql> select k1, k2, array_remove(k2, k1) from array_type_table_1;
+------+--------------------+--------------------------+
| k1 | k2 | array_remove(`k2`, `k1`) |
+------+--------------------+--------------------------+
| 1 | [1, 2, 3] | [2, 3] |
| 2 | [1, 3] | [1, 3] |
| 3 | NULL | NULL |
| 4 | [1, 3] | [1, 3] |
| 5 | [NULL, 1, NULL, 2] | [NULL, 1, NULL, 2] |
+------+--------------------+--------------------------+

mysql> select k1, k2, array_remove(k2, date('2022-10-10')) from array_type_table_date;
+------+--------------------------+-------------------------------------------------+
| k1 | k2 | array_remove(`k2`, date('2022-10-10 00:00:00')) |
+------+--------------------------+-------------------------------------------------+
| 1 | [2021-10-10, 2022-10-10] | [2021-10-10] |
| 2 | [NULL, 2022-05-14] | [NULL, 2022-05-14] |
+------+--------------------------+-------------------------------------------------+

mysql> select k1, k2, array_remove(k2, k1) from array_type_table_nullable;
+------+-----------+--------------------------+
| k1 | k2 | array_remove(`k2`, `k1`) |
+------+-----------+--------------------------+
| NULL | [1, 2, 3] | NULL |
| 1 | NULL | NULL |
| NULL | [NULL, 1] | NULL |
| 1 | [NULL, 1] | [NULL] |
+------+-----------+--------------------------+

keywords

ARRAY,REMOVE,ARRAY_REMOVE