array_slice

description

Syntax

  1. ARRAY<T> array_slice(ARRAY<T> arr, BIGINT off, BIGINT len)

Returns a slice of the array.

  1. A positive off indicates an indent on the left
  2. A negative off indicates an indent on the right.
  3. An empty array is returned when the off is not within the actual range of the array.
  4. A negative len will be treated as 0.

notice

Only supported in vectorized engine

example

  1. mysql> set enable_vectorized_engine=true;
  3. mysql> select k2, k2[2:2] from array_type_table_nullable;
  4. +-----------------+-------------------------+
  5. | k2              | array_slice(`k2`, 2, 2) |
  6. +-----------------+-------------------------+
  7. | [1, 2, 3]       | [2, 3]                  |
  8. | [1, NULL, 3]    | [NULL, 3]               |
  9. | [2, 3]          | [3]                     |
  10. | NULL            | NULL                    |
  11. +-----------------+-------------------------+
  13. mysql> select k2, array_slice(k2, 2, 2) from array_type_table_nullable;
  14. +-----------------+-------------------------+
  15. | k2              | array_slice(`k2`, 2, 2) |
  16. +-----------------+-------------------------+
  17. | [1, 2, 3]       | [2, 3]                  |
  18. | [1, NULL, 3]    | [NULL, 3]               |
  19. | [2, 3]          | [3]                     |
  20. | NULL            | NULL                    |
  21. +-----------------+-------------------------+
  23. mysql> select k2, k2[2:2] from array_type_table_nullable_varchar;
  24. +----------------------------+-------------------------+
  25. | k2                         | array_slice(`k2`, 2, 2) |
  26. +----------------------------+-------------------------+
  27. | ['hello', 'world', 'c++']  | ['world', 'c++']        |
  28. | ['a1', 'equals', 'b1']     | ['equals', 'b1']        |
  29. | ['hasnull', NULL, 'value'] | [NULL, 'value']         |
  30. | ['hasnull', NULL, 'value'] | [NULL, 'value']         |
  31. +----------------------------+-------------------------+
  33. mysql> select k2, array_slice(k2, 2, 2) from array_type_table_nullable_varchar;
  34. +----------------------------+-------------------------+
  35. | k2                         | array_slice(`k2`, 2, 2) |
  36. +----------------------------+-------------------------+
  37. | ['hello', 'world', 'c++']  | ['world', 'c++']        |
  38. | ['a1', 'equals', 'b1']     | ['equals', 'b1']        |
  39. | ['hasnull', NULL, 'value'] | [NULL, 'value']         |
  40. | ['hasnull', NULL, 'value'] | [NULL, 'value']         |
  41. +----------------------------+-------------------------+

Negative off:

  1. mysql> select k2, k2[-2:1] from array_type_table_nullable;
  2. +-----------+--------------------------+
  3. | k2        | array_slice(`k2`, -2, 1) |
  4. +-----------+--------------------------+
  5. | [1, 2, 3] | [2]                      |
  6. | [1, 2, 3] | [2]                      |
  7. | [2, 3]    | [2]                      |
  8. | [2, 3]    | [2]                      |
  9. +-----------+--------------------------+
  11. mysql> select k2, array_slice(k2, -2, 1) from array_type_table_nullable;
  12. +-----------+--------------------------+
  13. | k2        | array_slice(`k2`, -2, 1) |
  14. +-----------+--------------------------+
  15. | [1, 2, 3] | [2]                      |
  16. | [1, 2, 3] | [2]                      |
  17. | [2, 3]    | [2]                      |
  18. | [2, 3]    | [2]                      |
  19. +-----------+--------------------------+
  21. mysql> select k2, k2[-2:2] from array_type_table_nullable_varchar;
  22. +----------------------------+--------------------------+
  23. | k2                         | array_slice(`k2`, -2, 2) |
  24. +----------------------------+--------------------------+
  25. | ['hello', 'world', 'c++']  | ['world', 'c++']         |
  26. | ['a1', 'equals', 'b1']     | ['equals', 'b1']         |
  27. | ['hasnull', NULL, 'value'] | [NULL, 'value']          |
  28. | ['hasnull', NULL, 'value'] | [NULL, 'value']          |
  29. +----------------------------+--------------------------+
  31. mysql> select k2, array_slice(k2, -2, 2) from array_type_table_nullable_varchar;
  32. +----------------------------+--------------------------+
  33. | k2                         | array_slice(`k2`, -2, 2) |
  34. +----------------------------+--------------------------+
  35. | ['hello', 'world', 'c++']  | ['world', 'c++']         |
  36. | ['a1', 'equals', 'b1']     | ['equals', 'b1']         |
  37. | ['hasnull', NULL, 'value'] | [NULL, 'value']          |
  38. | ['hasnull', NULL, 'value'] | [NULL, 'value']          |
  39. +----------------------------+--------------------------+
  1. mysql> select k2, array_slice(k2, 0) from array_type_table;
  2. +-----------+-------------------------+
  3. | k2        | array_slice(`k2`, 0) |
  4. +-----------+-------------------------+
  5. | [1, 2, 3] | []                      |
  6. +-----------+-------------------------+
  8. mysql> select k2, array_slice(k2, -5) from array_type_table;
  9. +-----------+----------------------+
  10. | k2        | array_slice(`k2`, -5) |
  11. +-----------+----------------------+
  12. | [1, 2, 3] | []                   |
  13. +-----------+----------------------+

keywords

ARRAY,SLICE,ARRAY_SLICE