ARRAY_SLICE¶

Syntax¶

ARRAY_SLICE( <array> , <from> , <to> )

Arguments¶

array

The source array of which a subset of the elements are used to construct the resulting array.

from

A position in the source array. The position of the first element is 0. Elements from positions less than from are not included in the resulting array.

to

A position in the source array. Elements from positions equal to or greater than to are not included in the resulting array.

Returns¶

The data type of the returned value is ARRAY.

Usage notes¶

• The output includes elements up to, but not including the element specified by the parameter to.

• If the array is NULL, the result is NULL.

• If either from or to is NULL, the result is NULL.

• If either from or to is negative, it is relative to the end of the array, not the beginning of the array. E.g. “-2” refers to the second-from-the-last position in the array.

• If from and to are both beyond the upper end of the array, or are both beyond the lower end of the array, then the result is the empty set.

• When you pass a structured array to the function, the function returns a structured array of the same type.

Note that many of these rules (e.g. interpretation of negative numbers as indexes from the end of the array, and the rule that the slice is up to, but not including, the to index), are similar to the rules for array slices in programming languages such as Python.

Each of these rules is illustrated in at least one example below.

Examples¶

This shows a simple example of slicing an array:

select array_slice(array_construct(0,1,2,3,4,5,6), 0, 2);

Copy

Output:

+---------------------------------------------------+
| ARRAY_SLICE(ARRAY_CONSTRUCT(0,1,2,3,4,5,6), 0, 2) |
|---------------------------------------------------|
| [                                                 |
|   0,                                              |
|   1                                               |
| ]                                                 |
+---------------------------------------------------+

Copy

This shows the effect of using NULL as the input array:

select array_slice(NULL, 2, 3);

Copy

Output:

+-------------------------+
| ARRAY_SLICE(NULL, 2, 3) |
|-------------------------|
| NULL                    |
+-------------------------+

Copy

This shows the effect of using NULL as one of the slice indexes:

select array_slice(array_construct(0,1,2,3,4,5,6), NULL, 2);

Copy

Output:

+------------------------------------------------------+
| ARRAY_SLICE(ARRAY_CONSTRUCT(0,1,2,3,4,5,6), NULL, 2) |
|------------------------------------------------------|
| NULL                                                 |
+------------------------------------------------------+

Copy

This shows the effect of using a negative number as an index; the number is interpreted as the offset from the end of the array:

select array_slice(array_construct(0,1,2,3,4,5,6), 0, -2);

Copy

Output:

+----------------------------------------------------+
| ARRAY_SLICE(ARRAY_CONSTRUCT(0,1,2,3,4,5,6), 0, -2) |
|----------------------------------------------------|
| [                                                  |
|   0,                                               |
|   1,                                               |
|   2,                                               |
|   3,                                               |
|   4                                                |
| ]                                                  |
+----------------------------------------------------+

Copy

Both indexes can be negative, i.e. both can be relative to the end of the array:

select array_slice(array_construct(0,1,2,3,4,5,6), -5, -3);

Copy

Output:

+-----------------------------------------------------+
| ARRAY_SLICE(ARRAY_CONSTRUCT(0,1,2,3,4,5,6), -5, -3) |
|-----------------------------------------------------|
| [                                                   |
|   2,                                                |
|   3                                                 |
| ]                                                   |
+-----------------------------------------------------+

Copy

In this example, both indexes are beyond the end of the array:

select array_slice(array_construct(0,1,2,3,4,5,6), 10, 12);

Copy

Output:

+-----------------------------------------------------+
| ARRAY_SLICE(ARRAY_CONSTRUCT(0,1,2,3,4,5,6), 10, 12) |
|-----------------------------------------------------|
| []                                                  |
+-----------------------------------------------------+

Copy

In this example, both indexes are before the start of the array:

select array_slice(array_construct(0,1,2,3,4,5,6), -10, -12);

Copy

Output:

+-------------------------------------------------------+
| ARRAY_SLICE(ARRAY_CONSTRUCT(0,1,2,3,4,5,6), -10, -12) |
|-------------------------------------------------------|
| []                                                    |
+-------------------------------------------------------+

Copy

Although the indexes must be numeric, the elements of the array do not need to be numeric:

SELECT ARRAY_SLICE(ARRAY_CONSTRUCT('foo','snow','flake','bar'), 1, 3);

Copy

Output:

+----------------------------------------------------------------+
| ARRAY_SLICE(ARRAY_CONSTRUCT('FOO','SNOW','FLAKE','BAR'), 1, 3) |
|----------------------------------------------------------------|
| [                                                              |
|   "snow",                                                      |
|   "flake"                                                      |
| ]                                                              |
+----------------------------------------------------------------+

Copy