list_filter

Applies to VAST Cluster 5.5.

Functions › List, struct and map functions › Lambdas. Returns the elements of a list for which a lambda, a small condition written in the query, is true, in their original order. array_filter and filter are the same function.

Syntax

list_filter(l, x -> condition)
list_filter(l, (x, i) -> condition)
array_filter(l, x -> condition)
filter(l, x -> condition)

Returns: a list of the same type as l.

Aliases: array_filter, filter.

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_filter. 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 threshold 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_filter"'

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_filter(l, x -> condition) tests condition for each element of l, with x set to that element, and keeps the elements for which it's true. list_filter([1, 2, 3], x -> x > 1) returns [2, 3].

• Only true keeps an element. An element for which the condition is false or NULL is dropped: list_filter([1, NULL, 3], x -> x > 1) returns [3], because NULL > 1 is NULL.
• To keep or find NULL elements, test for them. x -> x IS NOT NULL removes them; x -> x IS NULL keeps only them.
• The position is available. In (x, i) -> condition, i is the position of x, starting at 1: list_filter([10, 20, 30], (x, i) -> i % 2 = 1) returns [10, 30].
• The condition can use the row's columns. list_filter(nums, x -> x >= n) compares each element with the row's n.
• NaN is larger than any number, so x -> x > 1 keeps a NaN element.
• Write a condition that returns BOOLEAN. A lambda that returns another type isn't rejected: x -> x + 1 and x -> 'yes' keep every element, and on [0, 1, 2], x -> x drops the 0.

NULLs and edge cases

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

run("SELECT list_filter(NULL::BIGINT[], x -> x > 1), list_filter([], x -> x > 1), "
    "list_filter([1, NULL, 3], x -> x > 1), list_filter([1, 2], x -> NULL)")
run("SELECT list_filter([1, NULL, 3], x -> x IS NULL), list_filter([1, NULL, 3], x -> x IS NOT NULL), "
    "list_filter(['NaN'::DOUBLE, 1.0, 2.0], x -> x > 1)")
run("SELECT list_filter([1, 2, 3], x -> x + 1), list_filter([0, 1, 2], x -> x)")
run("SELECT list_filter([1], NULL)")
run("SELECT list_filter([1, 2], (x, i, j) -> true)")
run("SELECT list_filter(123, x -> x > 1)")

[(None, [], [3], [])]
[([None], [1, 3], [nan, 2.0])]
[([1, 2, 3], [1, 2])]
InternalError: Binder Error: Invalid lambda expression!
InternalError: Binder Error: This lambda function only supports up to two lambda parameters!
InternalError: Binder Error: failed to bind function, either: Invalid LIST argument during lambda function binding!

Examples

Keep the numbers above 1, the numbers at least the row's n, and the elements after the first position:

run(f"SELECT id, list_filter(nums, x -> x > 1), filter(nums, x -> x >= n), array_filter(nums, (x, i) -> i > 1) "
    f"FROM {TABLE} ORDER BY id LIMIT 10")

[(1, [2, 3], [2, 3], [2, 3]), (2, [3, 3], [3, 3], [None, 3]), (3, [], [], []), (4, None, None, None), (5, [10], [10], [0, -5, 10])]

Filter text and lists of lists: drop the word 'b', and keep the inner lists with more than one element:

run(f"SELECT id, list_filter(words, x -> x <> 'b'), list_filter(nested, x -> len(x) > 1) "
    f"FROM {TABLE} ORDER BY id LIMIT 10")

[(1, ['a', 'c'], [[1, 2]]), (2, ['B'], []), (3, [], []), (4, None, None), (5, ['é', 'Zoë', '🇫🇷', ''], [[5, None], [6, 7, 8]])]

The rows that have at least one number above 1: filter the list, then test its length:

run(f"SELECT id FROM {TABLE} WHERE len(list_filter(nums, x -> x > 1)) > 0 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_filter() and its lambda always run in the Query Engine. Its condition filters the elements of a list, not the rows of the table:

• In the SELECT list, a projection step filters 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 len(list_filter(nums, x -> x > 1)) > 0")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│ (len(list_filter(nums)) > │
│             0)            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│            nums           │
│             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      │
│                           │
└───────────────────────────┘

• Test for one value with list_contains. To ask whether a list holds a value, list_contains(l, 3) says it in one function; len(list_filter(l, x -> x = 3)) > 0 gives the same answer.
• Store values you filter on in their own column. A condition on a list always runs in the Query Engine; a comparison of a plain column with a constant is pushed down.

Limitations and errors

• No functions to remove values by name. array_remove, array_except and list_except fail with Scalar Function with name ... does not exist!. Use list_filter: list_filter(l, x -> x <> 1) removes every 1, and list_filter(a, x -> NOT list_contains(b, x)) removes the elements of b from a.
• The x -> condition form only. lambda x: x > 1 fails with Parser Error: syntax error at or near "x". At most two lambda parameters.

run("SELECT array_remove([1, 2, 1], 1)")
run("SELECT list_filter([1, 2, 1], x -> x <> 1), list_filter([1, 2, 3], x -> NOT list_contains([2], x))")

InternalError: Catalog Error: Scalar Function with name array_remove does not exist!
[([2], [1, 3])]

Similar functions

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

[([3, 3, 10], [3, 3, 10], True, [6, 2, None, 6, 20])]

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_transform and list_reduce: the other lambda functions.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.