--- title: "list_select" slug: "dev-database-reference-sql-functions-lists-list-select" updated: 2026-10-30T18:19:06Z published: 2026-10-30T18:19:06Z canonical: "docs.vastdata.com/dev-database-reference-sql-functions-lists-list-select" --- > ## 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_select *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) › Combine and change lists.* Returns the elements at the positions you list, in that order. `array_select` is the same function. ## Syntax ``` list_select(list, positions) array_select(list, positions) ``` | Argument | Type | Description | | --- | --- | --- | | `list` | A list of any type | The list to pick from. | | `positions` | An integer list (`BIGINT[]`) | The positions to pick, starting at 1, in the order you want them. No NULLs. | **Returns:** a list of the same type as `list`, with one element for each entry of `positions`. **Aliases:** `array_select`. ## 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_select`. 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 surveys, each with a list of answers (`answers`) and the positions of the answers to report (`report`). Survey 2 has a NULL answer and a position past the end, survey 3 has empty lists, and survey 4 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_select"' 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}.surveys" run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}") run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, answers VARCHAR[], report BIGINT[])") run(f"INSERT INTO {TABLE} VALUES " "(1, ['yes', 'no', 'maybe'], [3, 1]), " "(2, ['no', NULL], [2, 5]), " "(3, [], []), " "(4, NULL, NULL)") ``` ``` [] [] [(4,)] ``` ## What it does `list_select` builds a list from the elements of `list` at each position in `positions`, in the order of `positions`: `list_select([10, 20, 30], [3, 1])` is `[30, 10]`. Positions start at 1, and the same position can be picked more than once: `list_select([10, 20, 30], [1, 1, 1])` is `[10, 10, 10]`. The result has as many elements as `positions`. A position outside the list (0, a negative number, or past the end) gives a NULL element: `list_select([10, 20, 30], [4])` is `[NULL]`. Unlike `l[-1]`, a negative position doesn't count from the end. With [list_grade_up](/develop/docs/dev-database-reference-sql-functions-lists-list-grade-up), it reorders a list by the positions of its sorted elements: `list_select(l, list_grade_up(l))` sorts `l`. ## NULLs and edge cases | Input | Result | | --- | --- | | `list_select(NULL, [1])` (a NULL list) | `NULL` | | `list_select([1, 2], NULL)` (NULL positions) | `NULL` | | `list_select([10, 20, 30], [])` | `[]` | | `list_select([10, 20, 30], [0])`, `[-1]` or `[4]` | `[NULL]` | | `list_select([], [1])` | `[NULL]` | | `list_select([[1], [2]], [2])` | `[[2]]` | | `list_select([10, 20, 30], range(1, 4))` | `[10, 20, 30]` | | `list_select([1, 2], [NULL, 2])` (a NULL position) | Error: `NULLs are not allowed as list elements in the second input parameter.` | | `list_select([10, 20, 30], [1.5])` or `['1']` | Error: `No function matches the given name and argument types`, for example `'list_select(INTEGER[], DECIMAL(2,1)[])'` | | `list_select([10, 20, 30], 1)` (one position, not a list) | Error: `No function matches the given name and argument types 'list_select(INTEGER[], INTEGER_LITERAL)'` | | A number, string, date, boolean or `BLOB` as `list` | Error: `No function matches the given name and argument types`, for example `'list_select(INTEGER_LITERAL, INTEGER[])'` | The same cases, as the cluster returns them. They use the helpers from [Set up the examples](/develop/docs/dev-database-reference-sql-functions-lists-list-select#set-up-the-examples): ``` run("SELECT list_select(NULL, [1]), list_select([1, 2], NULL), list_select([10, 20, 30], []), " "list_select([10, 20, 30], [0]), list_select([10, 20, 30], [-1]), list_select([10, 20, 30], [4])") run("SELECT list_select([], [1]), list_select([[1], [2]], [2]), list_select([10, 20, 30], range(1, 4))") run("SELECT list_select([1, 2], [NULL, 2])") run("SELECT list_select([10, 20, 30], [1.5])") run("SELECT list_select([10, 20, 30], 1)") run("SELECT list_select(123, [1, 3])") ``` ``` [(None, None, [], [None], [None], [None])] [([None], [[2]], [10, 20, 30])] OSError: NULLs are not allowed as list elements in the second input parameter. InternalError: Binder Error: No function matches the given name and argument types 'list_select(INTEGER[], DECIMAL(2,1)[])'. You might need to add explicit type casts. InternalError: Binder Error: No function matches the given name and argument types 'list_select(INTEGER[], INTEGER_LITERAL)'. You might need to add explicit type casts. InternalError: Binder Error: No function matches the given name and argument types 'list_select(INTEGER_LITERAL, INTEGER[])'. You might need to add explicit type casts. ``` ## Examples The third and first answers of each survey. Survey 2 has no third answer, so that element is NULL; survey 3's empty list gives NULLs too: ``` run(f"SELECT id, list_select(answers, [3, 1]) FROM {TABLE} ORDER BY id LIMIT 10") ``` ``` [(1, ['maybe', 'yes']), (2, [None, 'no']), (3, [None, None]), (4, None)] ``` Pick each survey's own positions from the `report` column. `array_select` gives the same lists: ``` run(f"SELECT id, list_select(answers, report), array_select(answers, report) FROM {TABLE} ORDER BY id LIMIT 10") ``` ``` [(1, ['maybe', 'yes'], ['maybe', 'yes']), (2, [None, None], [None, None]), (3, [], []), (4, None, None)] ``` Keep the surveys whose first and second answers are `'yes'` and `'no'`: ``` run(f"SELECT id FROM {TABLE} WHERE list_select(answers, [1, 2]) = ['yes', 'no'] 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`). The scan reads a list column whole, and VAST DataBase doesn't evaluate list functions, so `list_select()` is always computed by the Query Engine: - **In the `SELECT` list,** the scan reads the list columns and the Query Engine computes `list_select()` for each row it returns. - **In `WHERE`,** the scan sends the list for every row, and a `FILTER` step in the Query Engine computes the condition 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: ``` plan(f"SELECT id FROM {TABLE} WHERE list_select(answers, [1, 2]) = ['yes', 'no']") ``` ``` ┌───────────────────────────┐ │ PROJECTION │ │ ──────────────────── │ │ #1 │ │ │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ FILTER │ │ ──────────────────── │ │ (list_select(answers, [1, │ │ 2]) = ['yes', 'no']) │ │ │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ VAST_SCAN │ │ ──────────────────── │ │ Function: VAST_SCAN │ │ │ │ Projections: │ │ answers │ │ id │ │ │ └───────────────────────────┘ ``` ## Make it faster - **For one element, use `l[n]`.** `answers[1] = 'yes'` reads one element and compares it, without building a list. It still runs in a `FILTER` step: ``` plan(f"SELECT id FROM {TABLE} WHERE answers[1] = 'yes'") ``` ``` ┌───────────────────────────┐ │ PROJECTION │ │ ──────────────────── │ │ #1 │ │ │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ FILTER │ │ ──────────────────── │ │ (array_extract(answers, 1)│ │ = 'yes') │ │ │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ VAST_SCAN │ │ ──────────────────── │ │ Function: VAST_SCAN │ │ │ │ Projections: │ │ answers │ │ id │ │ │ └───────────────────────────┘ ``` - **For a range of positions, use [list_slice](/develop/docs/dev-database-reference-sql-functions-lists-list-slice)** or `l[2:3]`. - **Pair a list condition with a pushed-down condition** on a plain column, such as `id > 1`, so the list condition runs only on the rows that pass it. - **Keep values you filter on outside lists.** If queries often test the same answer, store it in its own column when you write the rows, so the filter can be pushed down. ## Limitations and errors - **No NULLs in `positions`.** A NULL position fails the query with `NULLs are not allowed as list elements in the second input parameter.` If the positions come from data, remove the NULLs first: `list_filter(p, x -> x IS NOT NULL)`. - **Integer positions only.** A decimal or string list, or a single number, fails with `No function matches the given name and argument types 'list_select(...)'`. Wrap a single position in a list: `[2]`. - **No negative positions.** `-1` gives a NULL element, not the last one. Use `l[-1]` or [list_last](/develop/docs/dev-database-reference-sql-functions-lists-list-last) for the last element. ``` run("SELECT list_select([10, 20, 30], list_filter([NULL, 2], x -> x IS NOT NULL)), [10, 20, 30][-1]") ``` ``` [([20], 30)] ``` ## Similar functions | Function | Result for `[3, 1, NULL, 3, 10]` | | --- | --- | | `list_select(l, [2])` | `[1]` | | `l[2]` or [list_extract](/develop/docs/dev-database-reference-sql-functions-lists-list-extract): `list_extract(l, 2)`, one element, not a list | `1` | | `list_select(l, [2, 3])` | `[1, NULL]` | | [list_slice](/develop/docs/dev-database-reference-sql-functions-lists-list-slice): `list_slice(l, 2, 3)` | `[1, NULL]` | | [list_where](/develop/docs/dev-database-reference-sql-functions-lists-list-where): `list_where(l, [false, true, true, false, false])`, by a mask | `[1, NULL]` | | `list_select(l, list_grade_up(l))`, with [list_grade_up](/develop/docs/dev-database-reference-sql-functions-lists-list-grade-up) | `[1, 3, 3, 10, NULL]` | ``` run("SELECT list_select(l, [2]), l[2], list_extract(l, 2), list_select(l, [2, 3]), list_slice(l, 2, 3), " "list_where(l, [false, true, true, false, false]), list_select(l, list_grade_up(l)) " "FROM (SELECT [3, 1, NULL, 3, 10] AS l)") ``` ``` [([1], 1, 1, [1, None], [1, None], [1, None], [1, 3, 3, 10, None])] ``` Clean up: ``` run(f"DROP TABLE IF EXISTS {TABLE}") run(f"DROP SCHEMA IF EXISTS {SCHEMA}") conn.close() ``` ``` [] [] ``` ## See also - [list_where](/develop/docs/dev-database-reference-sql-functions-lists-list-where): pick elements with a true/false mask. - [list_extract](/develop/docs/dev-database-reference-sql-functions-lists-list-extract): one element by position. - [List, struct and map functions](/develop/docs/dev-database-reference-sql-functions-lists-and-structs): all the list functions. - [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.