DuckDB provides us with a few options for slicing lists. Slicing a list is where we take a sublist or “slice” from the list, rather than the whole list. We specify the start and end position for which to return the slice. We can also specify a step, which allows us to skip one or more elements along the slice.

1. Bracket Notation

We can use bracket/slice notation to extract the slice from the list. It goes like this:

list[start:end:step]

Where:

• list is the list to slice.
• start is the position within that list to start the slice.
• end is the position to end the slice.
• step is how many elements to skip within the slice. This is optional.

Here’s a basic example:

SELECT (['a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i'])[2:5];

Result:

[b, c, d, e]

Here, I extracted a slice that started at position two and ended at position five. I didn’t specify a step value, and so it returned all values within the specified range.

Here’s an example that uses a step to skip some values:

SELECT 
    (['a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i'])[2:8] AS without_step,
    (['a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i'])[2:8:2] AS with_step;

Result:

+-----------------------+--------------+
|     without_step      |  with_step   |
+-----------------------+--------------+
| [b, c, d, e, f, g, h] | [b, d, f, h] |
+-----------------------+--------------+

I specified a step of 2, which resulted in every second value being skipped.

Here it is with a step of 3:

SELECT 
    (['a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i'])[2:8] AS without_step,
    (['a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i'])[2:8:3] AS with_step;

Result:

+-----------------------+-----------+
|     without_step      | with_step |
+-----------------------+-----------+
| [b, c, d, e, f, g, h] | [b, e, h] |
+-----------------------+-----------+

2. The list_slice() Function

As its name suggests, the list_slice() function is designed specifically for returning a slice from a list. We can use this function to return the same result that we’d get when using the slice notation:

SELECT list_slice(['a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i'], 2, 5);

Result:

[b, c, d, e]

And just like with slice notation, we can specify a step:

SELECT 
    list_slice(['a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i'], 2, 8) AS without_step,
    list_slice(['a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i'], 2, 8, 2) AS with_step;

Result:

+-----------------------+--------------+
|     without_step      |  with_step   |
+-----------------------+--------------+
| [b, c, d, e, f, g, h] | [b, d, f, h] |
+-----------------------+--------------+

3. The array_slice() Function

As its name suggests, the array_slice() function is an alias for list_slice(). So we can do exactly the same thing:

SELECT array_slice(['a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i'], 2, 5);

Result:

[b, c, d, e]

And here it is with a step:

SELECT 
    array_slice(['a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i'], 2, 8) AS without_step,
    array_slice(['a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i'], 2, 8, 2) AS with_step;

Result:

+-----------------------+--------------+
|     without_step      |  with_step   |
+-----------------------+--------------+
| [b, c, d, e, f, g, h] | [b, d, f, h] |
+-----------------------+--------------+

Exactly the same result, as expected.