list_transform

Applies to VAST Cluster 5.5.

Functions › List, struct and map functions › Lambdas. Applies a lambda, a small function written in the query, to each element of a list, and returns the list of results. list_apply, array_transform, array_apply and apply are the same function.

Syntax

list_transform(l, x -> expression)
list_transform(l, (x, i) -> expression)
list_apply(l, x -> expression)
array_transform(l, x -> expression)
array_apply(l, x -> expression)
apply(l, x -> expression)

Returns: a list as long as l, whose element type is the type of expression.

Aliases: list_apply, array_transform, array_apply, apply.

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_transform. 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 five rows of lists (numbers, words, measurements and lists of lists) and a multiplier n. Row 3 has empty lists and row 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_transform"'

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}.samples"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, nums BIGINT[], words VARCHAR[], vals DOUBLE[], nested BIGINT[][], n BIGINT)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, [1, 2, 3], ['a', 'b', 'c'], [0.5, 2.5, -1.0], [[1, 2], [3]], 2), "
    "(2, [3, NULL, 3], ['b', NULL, 'B'], [2.0, NULL], [[], NULL, [4]], -1), "
    "(3, [], [], [], [], 0), "
    "(4, NULL, NULL, NULL, NULL, NULL), "
    "(5, [-5, 0, -5, 10], ['\u00e9', 'Zo\u00eb', '\U0001F1EB\U0001F1F7', ''], [1e308, 1e308, -1e-308], [[5, NULL], [], [6, 7, 8]], 10)")

[]
[]
[(5,)]

What it does

list_transform(l, x -> expression) computes expression once for each element of l, with x set to that element, and returns the results in the same order. list_transform([1, 2, 3], x -> x + 1) returns [2, 3, 4].

• The result type is the lambda's type. x -> x > 1 returns a BOOLEAN[], x -> x / 2 a DOUBLE[] (/ divides as floating point), x -> x::VARCHAR a VARCHAR[], x -> [x, x] a list of lists and x -> {'v': x} a list of structs.
• The position is available. In (x, i) -> expression, i is the position of x, starting at 1: list_transform([10, 20], (x, i) -> x + i) returns [11, 22].
• The lambda can use the row's columns. list_transform(nums, x -> x * n) multiplies each element by the row's n.
• Lambdas nest. list_transform(nested, x -> len(x)) returns the length of each inner list, and a list_transform inside the lambda transforms the inner lists.
• An error in the lambda stops the query. An integer overflow (Overflow in addition of INT64 (...)!) or a cast that fails (Could not convert string 'a' to INT32) fails the whole query, not just the element.

NULLs and edge cases

The same cases, as the cluster returns them. They use the helpers from Set up the examples:

run("SELECT list_transform(NULL::BIGINT[], x -> x + 1), list_transform([], x -> x + 1), "
    "list_transform([1, NULL], x -> x + 1), list_transform([1, 2], x -> NULL)")
run("SELECT list_transform([1, 2], x -> 1 / 0), list_transform([10, 20], (x, i) -> i), "
    "list_transform([{'a': 1}, {'a': 2}], e -> e.a)")
run("SELECT list_transform([1], NULL)")
run("SELECT list_transform([1], (x, i, j) -> x)")
run("SELECT list_transform([1, 2], x -> x + 9223372036854775807)")
run("SELECT list_transform(123, x -> x * 2)")

[(None, [], [2, None], [None, None])]
[([inf, inf], [1, 2], [1, 2])]
InternalError: Binder Error: Invalid lambda expression!
InternalError: Binder Error: This lambda function only supports up to two lambda parameters!
OSError: Overflow in addition of INT64 (1 + 9223372036854775807)!
InternalError: Binder Error: failed to bind function, either: Invalid LIST argument during lambda function binding!

The result types from What it does:

run("SELECT list_transform([1, 2], x -> x > 1), list_transform([1, 2], x -> x / 2), "
    "list_transform([1, 2], x -> x::VARCHAR), list_transform([1, 2], x -> [x, x]), list_transform([1, 2], x -> {'v': x})")

[([False, True], [0.5, 1.0], ['1', '2'], [[1, 1], [2, 2]], [{'v': 1}, {'v': 2}])]

Examples

Double each number, multiply it by the row's n, and weight it by its position. apply and array_transform are the same function:

run(f"SELECT id, list_transform(nums, x -> x * 2), apply(nums, x -> x * n), array_transform(nums, (x, i) -> x * i) "
    f"FROM {TABLE} ORDER BY id LIMIT 10")

[(1, [2, 4, 6], [2, 4, 6], [1, 4, 9]), (2, [6, None, 6], [-3, None, -3], [3, None, 9]), (3, [], [], []), (4, None, None, None), (5, [-10, 0, -10, 20], [-50, 0, -50, 100], [-5, 0, -15, 40])]

Transform text, measurements and lists of lists: upper-case each word, round each measurement times 10, and count the elements of each inner list:

run(f"SELECT id, list_transform(words, x -> upper(x)), list_transform(vals, x -> round(x * 10)), "
    f"list_transform(nested, x -> len(x)) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, ['A', 'B', 'C'], [5.0, 25.0, -10.0], [2, 1]), (2, ['B', None, 'B'], [20.0, None], [0, None, 1]), (3, [], [], []), (4, None, None, None), (5, ['É', 'ZOË', '🇫🇷', ''], [inf, inf, -0.0], [2, 0, 3])]

Keep the rows where any measurement is above 1: transform each measurement to a boolean, then test whether any is true with list_bool_or:

run(f"SELECT id FROM {TABLE} WHERE list_bool_or(list_transform(vals, x -> x > 1)) ORDER BY id LIMIT 10")

[(1,), (2,), (5,)]

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 list_transform() and its lambda always run in the Query Engine:

• In the SELECT list, a projection step runs the lambda over each row's list after the scan.
• In WHERE, the scan sends the list for every row, and a FILTER step runs the lambda 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_bool_or(list_transform(vals, x -> x > 1))")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│  list_aggr(list_transform │
│     (vals), 'bool_or')    │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│            vals           │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Add a condition on a plain column. A comparison of a column with a constant, such as id > 1, is evaluated in VAST DataBase, so the lambda runs only on the rows that pass it. The plan for id > 1 alone has no FILTER step:

plan(f"SELECT id FROM {TABLE} WHERE id > 1")

┌───────────────────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│      Projections: id      │
│                           │
└───────────────────────────┘

• Store values you filter on in their own column. If queries often filter on a value computed from a list, compute it when you write the rows: a comparison of a plain column with a constant is pushed down, a condition on a list isn't.

Limitations and errors

• The x -> expression form only. lambda x: x + 1 fails with Parser Error: syntax error at or near "x".
• At most two lambda parameters. A third fails with This lambda function only supports up to two lambda parameters!.
• A lambda, not a value. A constant as the second argument, as in list_transform([1, 2], 1), fails with No function matches the given name and argument types; NULL fails with Invalid lambda expression!.
• Names the lambda doesn't define are columns. x -> y fails with Referenced column "y" not found in FROM clause! when the query has no column y.
• transform isn't a name for it. transform(l, x -> ...) fails with Scalar Function with name transform does not exist!. Use list_transform or one of its aliases.
• ORDER BY needs a LIMIT. Without one, the query fails with The operator 'ORDER_BY' type=1 is currently not supported for this usage.

run("SELECT list_transform([1, 2], lambda x: x + 1)")
run("SELECT list_transform([1, 2], x -> y)")
run("SELECT transform([1, 2], x -> x + 1)")

InternalError: Parse query: 'SELECT list_transform([1, 2], lambda x: x + 1)' failed with: Parser Error: syntax error at or near "x"
InternalError: Binder Error: Referenced column "y" not found in FROM clause!
InternalError: Catalog Error: Scalar Function with name transform does not exist!

Similar functions

run("SELECT list_transform(l, x -> x * 2), [x * 2 FOR x IN l], list_filter(l, x -> x > 1), list_aggregate(l, 'sum') "
    "FROM (SELECT [3, 1, NULL, 3, 10] AS l)")

[([6, 2, None, 6, 20], [6, 2, None, 6, 20], [3, 3, 10], Decimal('17'))]

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: all the list functions, and how lambdas work.
• list_filter and list_reduce: the other lambda functions.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.