DuckDB has a list_slice() function that enables us to select multiple list items from a list, based on their index range within the list. For example, we can select all list items between position 5 and 10 in a list.

Syntax

There are two ways we can use this function:

list_slice(list, begin, end)
list_slice(list, begin, end, step)

• list: The list to extract the values from.
• begin: The index of the start of the range.
• end: The index at the end of the range.
• step: This is an optional argument that we can use to make the function skip a given number of elements along the range.

The accepted data types and return values are as follows:

list_slice(ANY, ANY, ANY) -> ANY
list_slice(ANY, ANY, ANY, BIGINT) -> ANY

There’s also an array_slice() function, which is an alias for list_slice() (they both do the same thing).

Example

This example demonstrates the basic three-argument syntax:

SELECT list_slice(['Sky', 'Box', 'Run', 'Ant', 'Cam'], 2, 4);

Result:

[Box, Run, Ant]

Here, we selected all elements from the range of indexes starting with 2 and ending with 4.

Here’s another example, this time using a list of numbers:

SELECT list_slice([1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20], 5, 15);

Result:

[5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15]

Specifying a Step

We can provide an optional third argument in order to include a step along the range. For example we could get every second element, or every third one, and so on.

Here’s an example of selecting every second element:

SELECT list_slice([1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20], 5, 15, 2);

Result:

[5, 7, 9, 11, 13, 15]

Here’s an example of selecting every third element:

SELECT list_slice([1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20], 5, 15, 3);

Result:

[5, 8, 11, 14]

And every fourth:

SELECT list_slice([1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20], 5, 15, 4);

Result:

[5, 9, 13]

When Elements Don’t Exist in the Range

If the specified range goes outside the range of the elements in the list, only those elements within the range are returned:

SELECT list_slice(['Sky', 'Box', 'Run'], 2, 10);

Result:

[Box, Run]

If the specified range is completely outside the range of the list, then an empty list is returned:

SELECT list_slice(['Sky', 'Box', 'Run'], 4, 10);

Result:

[]

Negative Range

We can also provide negative values for the range. When we do this, it works backwards from the end of the list. Actually, it doesn’t quite work backwards, in the sense that it changes direction. But having a negative index means that the negative index is applied from the end of the list.

Let’s walk through some examples to demonstrate how it works:

SELECT list_slice(['Sky', 'Box', 'Run'], -1, -1);

Result:

[Run]

The start of the range is -1, which is the last element in the list. The end of the range is also -1 which is also the last element in the list.

Here’s another example:

SELECT list_slice(['Sky', 'Box', 'Run'], -2, -1);

Result:

[Box, Run]

So we can see that it started at the second position from the end, and ended at the first position from the end.

Here’s another one:

SELECT list_slice(['Sky', 'Box', 'Run'], -3, -2);

Result:

[Sky, Box]

This time we started from the third position from the end, and ended at the second from the end.

When providing negative values, we need to be careful to ensure that the end position is not a smaller value than the start position:

SELECT list_slice(['Sky', 'Box', 'Run'], -1, -2);

Result:

[]

In this case we get an empty list.

We can combine negative indexes with positive ones:

SELECT list_slice(['Sky', 'Box', 'Run'], 1, -2);

Result:

[Sky, Box]

The array_slice() Function

As mentioned, the array_slice() function is an alias for list_slice(), so we can use either one to achieve the same result.

Here they are side by side:

SELECT 
    list_slice(['Sky', 'Box', 'Run', 'Ant', 'Cam'], 2, 4) AS list_slice,
    array_slice(['Sky', 'Box', 'Run', 'Ant', 'Cam'], 2, 4) AS array_slice;

Result:

+-----------------+-----------------+
|   list_slice    |   array_slice   |
+-----------------+-----------------+
| [Box, Run, Ant] | [Box, Run, Ant] |
+-----------------+-----------------+

Same result.