array_filter

SinceVersion 2.0

array_filter(lambda,array)

SinceVersion 2.0.2

array array_filter(array arr, array_bool filter_column)

description

Syntax

  1. ARRAY<T> array_filter(lambda, ARRAY<T> arr)
  2. ARRAY<T> array_filter(ARRAY<T> arr, ARRAY<Bool> filter_column)

使用lambda表达式作为输入参数，计算筛选另外的输入参数ARRAY列的数据。 并过滤掉在结果中0和NULL的值。

  1. array_filter(x->x>0, array1);
  2. array_filter(x->(x+2)=10, array1);
  3. array_filter(x->(abs(x)-2)>0, array1);
  4. array_filter(c_array,[0,1,0]);

example

  1. mysql [test]>select c_array,array_filter(c_array,[0,1,0]) from array_test;
  2. +-----------------+----------------------------------------------------+
  3. | c_array         | array_filter(`c_array`, ARRAY(FALSE, TRUE, FALSE)) |
  4. +-----------------+----------------------------------------------------+
  5. | [1, 2, 3, 4, 5] | [2]                                                |
  6. | [6, 7, 8]       | [7]                                                |
  7. | []              | []                                                 |
  8. | NULL            | NULL                                               |
  9. +-----------------+----------------------------------------------------+
  11. mysql [test]>select array_filter(x->(x > 1),[1,2,3,0,null]);
  12. +----------------------------------------------------------------------------------------------+
  13. | array_filter(ARRAY(1, 2, 3, 0, NULL), array_map([x] -> (x(0) > 1), ARRAY(1, 2, 3, 0, NULL))) |
  14. +----------------------------------------------------------------------------------------------+
  15. | [2, 3]                                                                                       |
  16. +----------------------------------------------------------------------------------------------+
  18. mysql [test]>select *, array_filter(x->x>0,c_array2) from array_test2;
  19. +------+-----------------+-------------------------+------------------------------------------------------------------+
  20. | id   | c_array1        | c_array2                | array_filter(`c_array2`, array_map([x] -> x(0) > 0, `c_array2`)) |
  21. +------+-----------------+-------------------------+------------------------------------------------------------------+
  22. |    1 | [1, 2, 3, 4, 5] | [10, 20, -40, 80, -100] | [10, 20, 80]                                                     |
  23. |    2 | [6, 7, 8]       | [10, 12, 13]            | [10, 12, 13]                                                     |
  24. |    3 | [1]             | [-100]                  | []                                                               |
  25. |    4 | NULL            | NULL                    | NULL                                                             |
  26. +------+-----------------+-------------------------+------------------------------------------------------------------+
  27. 4 rows in set (0.01 sec)
  29. mysql [test]>select *, array_filter(x->x%2=0,c_array2) from array_test2;
  30. +------+-----------------+-------------------------+----------------------------------------------------------------------+
  31. | id   | c_array1        | c_array2                | array_filter(`c_array2`, array_map([x] -> x(0) % 2 = 0, `c_array2`)) |
  32. +------+-----------------+-------------------------+----------------------------------------------------------------------+
  33. |    1 | [1, 2, 3, 4, 5] | [10, 20, -40, 80, -100] | [10, 20, -40, 80, -100]                                              |
  34. |    2 | [6, 7, 8]       | [10, 12, 13]            | [10, 12]                                                             |
  35. |    3 | [1]             | [-100]                  | [-100]                                                               |
  36. |    4 | NULL            | NULL                    | NULL                                                                 |
  37. +------+-----------------+-------------------------+----------------------------------------------------------------------+
  39. mysql [test]>select *, array_filter(x->(x*(-10)>0),c_array2) from array_test2;
  40. +------+-----------------+-------------------------+----------------------------------------------------------------------------+
  41. | id   | c_array1        | c_array2                | array_filter(`c_array2`, array_map([x] -> (x(0) * (-10) > 0), `c_array2`)) |
  42. +------+-----------------+-------------------------+----------------------------------------------------------------------------+
  43. |    1 | [1, 2, 3, 4, 5] | [10, 20, -40, 80, -100] | [-40, -100]                                                                |
  44. |    2 | [6, 7, 8]       | [10, 12, 13]            | []                                                                         |
  45. |    3 | [1]             | [-100]                  | [-100]                                                                     |
  46. |    4 | NULL            | NULL                    | NULL                                                                       |
  47. +------+-----------------+-------------------------+----------------------------------------------------------------------------+
  49. mysql [test]>select *, array_filter(x->x>0, array_map((x,y)->(x>y), c_array1,c_array2)) as res from array_test2;
  50. +------+-----------------+-------------------------+--------+
  51. | id   | c_array1        | c_array2                | res    |
  52. +------+-----------------+-------------------------+--------+
  53. |    1 | [1, 2, 3, 4, 5] | [10, 20, -40, 80, -100] | [1, 1] |
  54. |    2 | [6, 7, 8]       | [10, 12, 13]            | []     |
  55. |    3 | [1]             | [-100]                  | [1]    |
  56. |    4 | NULL            | NULL                    | NULL   |
  57. +------+-----------------+-------------------------+--------+

keywords

ARRAY,FILTER,ARRAY_FILTER