array_remove

description

Syntax

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

Remove all elements that equal to element from array.

notice

Only supported in vectorized engine

example

1. mysql> set enable_vectorized_engine=true;

3. mysql> select array_remove(['test', NULL, 'value'], 'value');
4. +-----------------------------------------------------+
5. | array_remove(ARRAY('test', NULL, 'value'), 'value') |
6. +-----------------------------------------------------+
7. | [test, NULL]                                        |
8. +-----------------------------------------------------+

10. mysql> select k1, k2, array_remove(k2, 1) from array_type_table_1;
11. +------+--------------------+-----------------------+
12. | k1   | k2                 | array_remove(`k2`, 1) |
13. +------+--------------------+-----------------------+
14. |    1 | [1, 2, 3]          | [2, 3]                |
15. |    2 | [1, 3]             | [3]                   |
16. |    3 | NULL               | NULL                  |
17. |    4 | [1, 3]             | [3]                   |
18. |    5 | [NULL, 1, NULL, 2] | [NULL, NULL, 2]       |
19. +------+--------------------+-----------------------+

21. mysql> select k1, k2, array_remove(k2, k1) from array_type_table_1;
22. +------+--------------------+--------------------------+
23. | k1   | k2                 | array_remove(`k2`, `k1`) |
24. +------+--------------------+--------------------------+
25. |    1 | [1, 2, 3]          | [2, 3]                   |
26. |    2 | [1, 3]             | [1, 3]                   |
27. |    3 | NULL               | NULL                     |
28. |    4 | [1, 3]             | [1, 3]                   |
29. |    5 | [NULL, 1, NULL, 2] | [NULL, 1, NULL, 2]       |
30. +------+--------------------+--------------------------+

32. mysql> select k1, k2, array_remove(k2, date('2022-10-10')) from array_type_table_date;
33. +------+--------------------------+-------------------------------------------------+
34. | k1   | k2                       | array_remove(`k2`, date('2022-10-10 00:00:00')) |
35. +------+--------------------------+-------------------------------------------------+
36. |    1 | [2021-10-10, 2022-10-10] | [2021-10-10]                                    |
37. |    2 | [NULL, 2022-05-14]       | [NULL, 2022-05-14]                              |
38. +------+--------------------------+-------------------------------------------------+

40. mysql> select k1, k2, array_remove(k2, k1) from array_type_table_nullable;
41. +------+-----------+--------------------------+
42. | k1   | k2        | array_remove(`k2`, `k1`) |
43. +------+-----------+--------------------------+
44. | NULL | [1, 2, 3] | NULL                     |
45. |    1 | NULL      | NULL                     |
46. | NULL | [NULL, 1] | NULL                     |
47. |    1 | [NULL, 1] | [NULL]                   |
48. +------+-----------+--------------------------+

keywords

ARRAY,REMOVE,ARRAY_REMOVE