array_slice

SinceVersion 1.2.0

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

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

keywords

ARRAY,SLICE,ARRAY_SLICE