SLICE or ARRAY RANGES

Like we have seen with selecting an individual value from an array type column, we can also return a subset/slice of the array using index ranges. Diffent SQL flavors implement this differently.

SYNTAX

For example, in Presto, the implementation is done by passing an array, starting index and ending index into the SLICE function.

SLICE(some_array, start_index, stop_index)

Another common implementation is array indecing similar to most programming languages implement directly on a column with ARRAY type. For example

ARRAY[start_index:end_index]

SLICING in Action

Our boardgamepoints table looks like this:

SELECT * from boardgamepoints;

 player |    points
-------+---------------
Lato   | {4,5,1}
Mario  | {9,1,5}
Kalie  | {7,2,3}
Leo    | {5,2,3,9}
Jeny   | {5,2,0,1,2,4}
Pro    | {1,2,0,1,4}
(6 rows)

To obtain the second to the 4 entries in the points column, we can implement the slice/index range as seen below:

SELECT 
    player, 
    points, 
    points[2:4] as point_slice
FROM boardgamepoints;

 player |    points     | points
-------+---------------+---------
Lato   | {4,5,1}       | {5,1}
Mario  | {9,1,5}       | {1,5}
Kalie  | {7,2,3}       | {2,3}
Leo    | {5,2,3,9}     | {2,3,9}
Jeny   | {5,2,0,1,2,4} | {2,0,1}
Pro    | {1,2,0,1,4}   | {2,0,1}
(6 rows)

Notice that SQL is less strict about the ranges as perhaps other programming languages. Even with arrays, that might be out of range, we will still get the results.