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