--- title: "list_slice" slug: "dev-database-reference-sql-functions-lists-list-slice" updated: 2026-10-30T18:19:06Z published: 2026-10-30T18:19:06Z canonical: "docs.vastdata.com/dev-database-reference-sql-functions-lists-list-slice" --- > ## Documentation Index > Fetch the complete documentation index at: https://docs.vastdata.com/llms.txt > Use this file to discover all available pages before exploring further. # list_slice *Applies to VAST Cluster 5.5.* *[Functions](/develop/docs/dev-database-reference-sql-functions-index) › [List, struct and map functions](/develop/docs/dev-database-reference-sql-functions-lists-and-structs) › Size and elements.* Returns the part of a list from position `begin` to position `end`, both included, optionally taking every `step`th element. `array_slice` is the same function, and `l[begin:end]` and `l[begin:end:step]` are the bracket forms of it. On a `VARCHAR`, it returns part of the string. ## Syntax ``` list_slice(list, begin, end) list_slice(list, begin, end, step) array_slice(list, begin, end) array_slice(list, begin, end, step) list[begin:end] list[begin:end:step] list[:end] list[begin:] ``` | Argument | Type | Description | | --- | --- | --- | | `list` | a list or a `VARCHAR` | The list, or the string, to take part of. | | `begin` | integer | The first position to take. `1` is the first element, `-1` the last. Leave it out in a bracket form to start at the beginning. | | `end` | integer | The last position to take. It's included. Leave it out in a bracket form to go to the end. | | `step` | `BIGINT` | Take every `step`th element; negative to go backwards. Default 1. Not for strings. | **Returns:** a list of the same type as `list`; a `VARCHAR` for a string. **Aliases:** `array_slice`, `list[begin:end]`, `list[begin:end:step]`. ## Set up the examples The examples connect with the ADBC driver, as in [VAST Query Engine (ADBC)](/develop/docs/dev-database-integrations-adbc), and work in a schema called `docs_sql_fn_lists_list_slice`. Set `QE_ENDPOINT`, `AWS_ACCESS_KEY_ID`, `AWS_SECRET_ACCESS_KEY` and `VASTDB_BUCKET` ([SQL reference](/develop/docs/dev-database-reference-sql-index#example-connect-and-use-a-transaction)), and run the blocks in order in one Python session. Below each block is the output you should see. This block connects, defines the `run()` and `plan()` helpers, and creates a table of four bus routes: the stops, the minutes from the start at each stop, a number of stops `n` and a label. The second route has a NULL stop, the third has empty lists and the last row has no values: ``` import os import re import adbc_driver_manager import adbc_driver_manager.dbapi as dbapi import adbc_driver_vastdb BUCKET = os.environ["VASTDB_BUCKET"] SCHEMA = f'"{BUCKET}/docs_sql_fn_lists_list_slice"' conn = dbapi.connect( driver=adbc_driver_vastdb.get_driver_path(), db_kwargs={ "vast.db.endpoint": os.environ["QE_ENDPOINT"], "vast.db.access_key": os.environ["AWS_ACCESS_KEY_ID"], "vast.db.secret_key": os.environ["AWS_SECRET_ACCESS_KEY"], }, autocommit=True, ) def server_message(e): """The server's message in a driver error, without the request ID or the echoed statement.""" text = re.sub(r"\[req=0x[0-9a-f]+\]: ", "", str(e)) found = (re.search(r'message: "(.*?)", request_id', text, re.S) or re.search(r"description: (.*)\)", text, re.S)) message = found.group(1) if found else text.strip().splitlines()[-1] message = re.sub(r"^Plan query: '.*?' failed with: ", "", message, flags=re.S) message = re.split(r"\\n|\n", message)[0].replace('\\"', '"') return message.replace(BUCKET, "") def run(sql): """Run one statement. Print the rows it returns, or the error.""" try: with conn.cursor() as cur: cur.execute(sql) rows = cur.fetchall() if cur.description else [] except (adbc_driver_manager.Error, OSError) as e: print(f"{type(e).__name__}: {server_message(e)}") else: print(rows) def plan(sql): """Print the plan without its ~N Rows lines: the table's row count, which can still be 0 just after an insert.""" with conn.cursor() as cur: cur.execute("EXPLAIN " + sql) steps = cur.fetchall()[0][1] print("\n".join(line for line in steps.splitlines() if "Rows" not in line)) TABLE = f"{SCHEMA}.routes" run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}") run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, stops VARCHAR[], minutes BIGINT[], n BIGINT, label VARCHAR)") run(f"INSERT INTO {TABLE} VALUES " "(1, ['A', 'B', 'C', 'D'], [0, 10, 25, 40], 2, 'north'), " "(2, ['E', NULL, 'F'], [0, NULL, 9], 1, 'x'), " "(3, [], [], 1, ''), " "(4, NULL, NULL, NULL, NULL)") ``` ``` [] [] [(4,)] ``` ## What it does `list_slice(l, begin, end)` returns the elements at positions `begin` to `end` of `l`, **both included**, as a list. Positions count from 1, and a negative position counts from the end, `-1` being the last element: | Call | Positions taken | Result | | --- | --- | --- | | `list_slice([1, 2, 3, 4], 2, 3)`, `[1, 2, 3, 4][2:3]` | 2 to 3 | `[2, 3]` | | `[1, 2, 3, 4][:2]` | the start to 2 | `[1, 2]` | | `[1, 2, 3, 4][-1:]` | the last to the end | `[4]` | | `[1, 2, 3, 4][:-2]` | the start to the second to last | `[1, 2, 3]` | | `list_slice([1, 2, 3, 4], 1, 4, 2)`, `[1, 2, 3, 4][1:4:2]` | 1 to 4, every second | `[1, 3]` | | `list_slice([1, 2, 3, 4], 4, 1, -1)`, `[1, 2, 3, 4][4:1:-1]` | 4 down to 1 | `[4, 3, 2, 1]` | Because `end` is included, `l[:-1]` is the whole list, not the list without its last element: use `l[:-2]` for that. - **Positions outside the list are left out**, not an error: `list_slice([1, 2, 3, 4], 2, 100)` is `[2, 3, 4]` and `list_slice([1, 2, 3, 4], -10, 2)` is `[1, 2]`. A `begin` of `0` is the same as `1`. - **An empty range gives `[]`:** `begin` after `end` (`[1, 2, 3, 4][3:1]`), or a range past the end (`list_slice([1, 2, 3, 4], 5, 6)`). - **A negative step goes backwards** from `begin` down to `end`, so `begin` must be the larger position: `list_slice([1, 2, 3, 4], 1, 4, -1)` is `[]`. **On a string,** `list_slice(s, begin, end)` and `s[begin:end]` return the characters from `begin` to `end` as a `VARCHAR`: `list_slice('abcdef', 2, 3)` is `'bc'`, and `list_slice('abcdef', -3, -1)` is `'def'`. A step isn't available for strings. See also [substring](/develop/docs/dev-database-reference-sql-functions-string-substring). ## NULLs and edge cases | Input | Result | | --- | --- | | `NULL` list | `NULL` | | `begin`, `end` or `step` NULL | `NULL` | | `[]` | `[]` | | `list_slice([1, 2, 3, 4], 0, 2)` | `[1, 2]` | | `list_slice([1, 2, 3, 4], 2, 100)` | `[2, 3, 4]` | | `list_slice([1, 2, 3, 4], 3, 2)` | `[]` | | `list_slice([1, 2, 3, 4], 1, 4, 10)` | `[1]` | | `list_slice([1, 2, 3, 4], 1.5, 3)` | `[2, 3]` (1.5 is taken as 2) | | `list_slice([1, 2, 3, 4], 1, 4, 0)` | Error: `Slice step cannot be zero` | | `list_slice('abcdef', 1, 6, 2)` | Error: `Slice with steps has not been implemented for string types` | | A number, date or boolean | Error: `ARRAY_SLICE can only operate on LISTs and VARCHARs` | The same cases, as the cluster returns them. They use the table and helpers from [Set up the examples](/develop/docs/dev-database-reference-sql-functions-lists-list-slice#set-up-the-examples): ``` run("SELECT list_slice(NULL, 2, 3), list_slice([1, 2, 3], NULL, 2), list_slice([1, 2, 3], 1, 3, NULL), list_slice([], 1, 2)") run("SELECT list_slice([1, 2, 3, 4], 0, 2), list_slice([1, 2, 3, 4], 2, 100), list_slice([1, 2, 3, 4], 3, 2), " "list_slice([1, 2, 3, 4], 1, 4, 10), list_slice([1, 2, 3, 4], 1.5, 3)") run("SELECT [1, 2, 3, 4][:-1], [1, 2, 3, 4][:-2], [1, 2, 3, 4][-1:], list_slice('abcdef', 2, 3), 'abcdef'[2:3]") run("SELECT list_slice([1, 2, 3, 4], 1, 4, 0)") run("SELECT list_slice('abcdef', 1, 6, 2)") run("SELECT list_slice(123, 1, 2)") ``` ``` [(None, None, None, [])] [([1, 2], [2, 3, 4], [], [1], [2, 3])] [([1, 2, 3, 4], [1, 2, 3], [4], 'bc', 'bc')] OSError: Slice step cannot be zero InternalError: Not implemented Error: Slice with steps has not been implemented for string types, you can consider rewriting your query as follows: InternalError: Binder Error: ARRAY_SLICE can only operate on LISTs and VARCHARs ``` ## Examples The first two stops of each route, the stops after the first, and the last two: ``` run(f"SELECT id, stops[:2], stops[2:], stops[-2:] FROM {TABLE} ORDER BY id LIMIT 10") ``` ``` [(1, ['A', 'B'], ['B', 'C', 'D'], ['C', 'D']), (2, ['E', None], [None, 'F'], [None, 'F']), (3, [], [], []), (4, None, None, None)] ``` Every second time with `list_slice` and a step, the stops in reverse, and the first `n` stops, with `n` from a column: ``` run(f"SELECT id, list_slice(minutes, 1, 4, 2), array_slice(stops, -1, 1, -1), stops[1:n] FROM {TABLE} ORDER BY id LIMIT 10") ``` ``` [(1, [0, 25], ['D', 'C', 'B', 'A'], ['A', 'B']), (2, [0, 9], ['F', None, 'E'], ['E']), (3, [], [], []), (4, None, None, None)] ``` Keep the routes whose first two stops are A and B: ``` run(f"SELECT id FROM {TABLE} WHERE stops[1:2] = ['A', 'B'] ORDER BY id LIMIT 10") ``` ``` [(1,)] ``` ## How it runs in VAST The Query Engine runs on the cluster's CNodes, and a query on a table starts with a scan in VAST DataBase (`VAST_SCAN`). A list column is read whole, and every condition on a list runs in the Query Engine, so `list_slice()` and the slices are always computed there: - **In the `SELECT` list,** the scan reads the list and the Query Engine slices it for each row it returns. - **In `WHERE`,** the scan sends the whole list for every row, and a `FILTER` step in the Query Engine slices it and keeps the rows that match. No rows are skipped in storage. The plan of the `WHERE` example shows the `FILTER` step above the scan. The plan writes `stops[1:2]` as `array_slice(stops, 1, 2)`: ``` plan(f"SELECT id FROM {TABLE} WHERE stops[1:2] = ['A', 'B']") ``` ``` ┌───────────────────────────┐ │ PROJECTION │ │ ──────────────────── │ │ #1 │ │ │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ FILTER │ │ ──────────────────── │ │ (array_slice(stops, 1, 2) │ │ = ['A', 'B']) │ │ │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ VAST_SCAN │ │ ──────────────────── │ │ Function: VAST_SCAN │ │ │ │ Projections: │ │ stops │ │ id │ │ │ └───────────────────────────┘ ``` ## Make it faster - **Store the part you filter on.** If queries often test the first stops of a route, keep them in their own columns when you write the rows: a comparison of a column with a constant is pushed down to VAST DataBase, so rows that don't match never leave storage. The plan of `label = 'north'` has no `FILTER` step: ``` plan(f"SELECT id FROM {TABLE} WHERE label = 'north'") ``` ``` ┌───────────────────────────┐ │ VAST_SCAN │ │ ──────────────────── │ │ Function: VAST_SCAN │ │ Projections: id │ │ │ └───────────────────────────┘ ``` - **Combine it with a pushed-down condition.** In `id > 100 AND stops[1:2] = ['A', 'B']`, `id > 100` is still evaluated in VAST DataBase, so the list is sliced only for the rows that pass it. ## Limitations and errors - **No step without both bounds.** `l[::2]` and `l[::-1]` fail with `Parser Error: syntax error at or near "::"`, because `::` is read as a cast. Write both bounds: `l[1:len(l):2]`, or `l[-1:1:-1]` to reverse a list. [list_reverse](/develop/docs/dev-database-reference-sql-functions-lists-list-reverse) also reverses a list. - **A step of 0** fails with `Slice step cannot be zero`. - **No step on strings.** `list_slice('abcdef', 1, 6, 2)` fails with `Not implemented Error: Slice with steps has not been implemented for string types`. - **Lists and strings only.** A number, a date or a boolean fails with `Binder Error: ARRAY_SLICE can only operate on LISTs and VARCHARs`. ``` run("SELECT [1, 2, 3, 4][::-1]") run(f"SELECT id, minutes[1:len(minutes):2], minutes[-1:1:-1], list_reverse(minutes) FROM {TABLE} ORDER BY id LIMIT 10") ``` ``` InternalError: Parse query: 'SELECT [1, 2, 3, 4][::-1]' failed with: Parser Error: syntax error at or near "::" [(1, [0, 25], [40, 25, 10, 0], [40, 25, 10, 0]), (2, [0, 9], [9, None, 0], [9, None, 0]), (3, [], [], []), (4, None, None, None)] ``` ## Similar functions | Function | Returns | Result for `[3, 1, NULL, 3, 10]` | | --- | --- | --- | | `l[2:3]`, `list_slice(l, 2, 3)` | the elements at positions 2 to 3 | `[1, NULL]` | | [list_select](/develop/docs/dev-database-reference-sql-functions-lists-list-select) `(l, [2, 3])` | the elements at a list of positions | `[1, NULL]` | | [list_where](/develop/docs/dev-database-reference-sql-functions-lists-list-where) `(l, [false, true, true, false, false])` | the elements where a mask is true | `[1, NULL]` | | [array_pop_front](/develop/docs/dev-database-reference-sql-functions-lists-array-pop-front) | all but the first element | `[1, NULL, 3, 10]` | | [array_pop_back](/develop/docs/dev-database-reference-sql-functions-lists-array-pop-back) | all but the last element | `[3, 1, NULL, 3]` | | [list_resize](/develop/docs/dev-database-reference-sql-functions-lists-list-resize) `(l, 2)` | the list resized to 2 elements | `[3, 1]` | | [list_extract](/develop/docs/dev-database-reference-sql-functions-lists-list-extract) `(l, 2)`, `l[2]` | one element, not a list | `1` | ``` run("SELECT l[2:3], list_slice(l, 2, 3), list_select(l, [2, 3]), list_where(l, [false, true, true, false, false]), " "array_pop_front(l), array_pop_back(l), list_resize(l, 2), l[2] FROM (SELECT [3, 1, NULL, 3, 10] AS l)") ``` ``` [([1, None], [1, None], [1, None], [1, None], [1, None, 3, 10], [3, 1, None, 3], [3, 1], 1)] ``` Clean up: ``` run(f"DROP TABLE IF EXISTS {TABLE}") run(f"DROP SCHEMA IF EXISTS {SCHEMA}") conn.close() ``` ``` [] [] ``` ## See also - [List, struct and map functions](/develop/docs/dev-database-reference-sql-functions-lists-and-structs): all the list functions. - [list_extract](/develop/docs/dev-database-reference-sql-functions-lists-list-extract) and [substring](/develop/docs/dev-database-reference-sql-functions-string-substring). - [Where filters run](/develop/docs/dev-database-reference-sql-select#where-filters-run) and [pushdowns](/develop/docs/dev-database-reference-pushdowns). - [EXPLAIN](/develop/docs/dev-database-reference-sql-explain): how to read a plan.