- Categories:
Semi-structured and structured data functions (Array/Object)
ARRAY_SLICE¶
Returns an array constructed from a specified subset of elements of the input array.
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 thanfrom
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
orto
is NULL, the result is NULL.If either
from
orto
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
andto
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);Output:
+---------------------------------------------------+ | ARRAY_SLICE(ARRAY_CONSTRUCT(0,1,2,3,4,5,6), 0, 2) | |---------------------------------------------------| | [ | | 0, | | 1 | | ] | +---------------------------------------------------+
This shows the effect of using NULL as the input array:
select array_slice(NULL, 2, 3);Output:
+-------------------------+ | ARRAY_SLICE(NULL, 2, 3) | |-------------------------| | NULL | +-------------------------+
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);Output:
+------------------------------------------------------+ | ARRAY_SLICE(ARRAY_CONSTRUCT(0,1,2,3,4,5,6), NULL, 2) | |------------------------------------------------------| | NULL | +------------------------------------------------------+
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);Output:
+----------------------------------------------------+ | ARRAY_SLICE(ARRAY_CONSTRUCT(0,1,2,3,4,5,6), 0, -2) | |----------------------------------------------------| | [ | | 0, | | 1, | | 2, | | 3, | | 4 | | ] | +----------------------------------------------------+
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);Output:
+-----------------------------------------------------+ | ARRAY_SLICE(ARRAY_CONSTRUCT(0,1,2,3,4,5,6), -5, -3) | |-----------------------------------------------------| | [ | | 2, | | 3 | | ] | +-----------------------------------------------------+
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);Output:
+-----------------------------------------------------+ | ARRAY_SLICE(ARRAY_CONSTRUCT(0,1,2,3,4,5,6), 10, 12) | |-----------------------------------------------------| | [] | +-----------------------------------------------------+
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);Output:
+-------------------------------------------------------+ | ARRAY_SLICE(ARRAY_CONSTRUCT(0,1,2,3,4,5,6), -10, -12) | |-------------------------------------------------------| | [] | +-------------------------------------------------------+
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);Output:
+----------------------------------------------------------------+ | ARRAY_SLICE(ARRAY_CONSTRUCT('FOO','SNOW','FLAKE','BAR'), 1, 3) | |----------------------------------------------------------------| | [ | | "snow", | | "flake" | | ] | +----------------------------------------------------------------+