Using Bracket Notation to Slice a List in DuckDB

by

DuckDB provides a few options for slicing lists, including the list_slice() function and its alias, array_slice(). We can also use bracket notation for a slightly more concise option. Bracket notation is where we append the slice details to the list.

Below are examples of using bracket notation to slice lists in DuckDB.

Syntax

The syntax 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.

Example

Here’s a basic example:

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

Result:

[b, c, d, e]

This example extracts a slice that starts at position two and ends at position five. I didn’t specify a step value, and so it returned all values within the specified range.

Specifying a Step

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] |
+-----------------------+-----------+

Omitting the Start and End Values

When using bracket notation, we can omit the start and/or the end value. When we omit the start value, the slice starts at the beginning of the list. When we omit the end value it ends at the end of the list. Omitting both returns the whole list.

Here’s an example of omitting the start value:

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

Result:

[a, b, c]

Here’s an example of omitting the end value:

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

Result:

[e, f, g, h, i]

And here’s what happens when we omit both start and end values:

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

Result:

[a, b, c, d, e, f, g, h, i]

If we omit the start and end values and use both colons, then we’ll need to insert a hyphen in between them:

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

Result:

[a, b, c, d, e, f, g, h, i]

If we don’t do this an error is likely to occur:

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

Result:

Parser Error:
syntax error at or near "::"

LINE 1: SELECT (['a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i'])[::];
                                                               ^

This is useful to know when omitting the start and end values while including a step value:

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

Result:

[a, d, g]

Specifying Negative Values

We can specify negative values for any of the arguments. When we do this, it works backwards from the end. Here’s an example that encompasses a few different scenarios:

SET VARIABLE list = ['a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i'];
SELECT 
    getvariable('list')[-8:-2] AS a,
    getvariable('list')[-8:-2:3] AS b,
    getvariable('list')[-2:-8:-3] AS c,
    getvariable('list')[-8:-2:-3] AS d;

Result:

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

We need to be careful when using negative values. If our values select no elements, we’ll get an empty list, just like column d in our example.