list_select

Applies to VAST Cluster 5.5.

Functions › List, struct and map functions › 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)

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), 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), 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, "<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, it reorders a list by the positions of its sorted elements: list_select(l, list_grade_up(l)) sorts l.

NULLs and edge cases

The same cases, as the cluster returns them. They use the helpers from 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 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 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

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: pick elements with a true/false mask.
• list_extract: one element by position.
• List, struct and map functions: all the list functions.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.