array_remove

SinceVersion 1.2.0

array_remove

description

Syntax

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

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

notice

仅支持向量化引擎中使用

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