flatten

Applies to VAST Cluster 5.5.

Functions › List, struct and map functions › Combine and change lists. Turns a list of lists into one list by joining the inner lists in order. It removes one level of nesting.

Syntax

flatten(nested_list)

Returns: a list with one level of nesting less: BIGINT[] for a BIGINT[][], BIGINT[][] for a BIGINT[][][].

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_flatten. 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 warehouse shelves. Each shelf holds boxes, and each box is a list of item numbers. Shelf 2 has an empty box and a NULL box, shelf 3 has no boxes, 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_flatten"'

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}.shelves"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, boxes BIGINT[][])")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, [[1, 2], [3]]), "
    "(2, [[], NULL, [4]]), "
    "(3, []), "
    "(4, NULL)")

[]
[]
[(4,)]

What it does

flatten concatenates the inner lists of nested_list, in order, into one list: flatten([[1, 2], [3]]) is [1, 2, 3]. Empty inner lists add nothing and NULL inner lists are skipped. The elements themselves are kept as they are, NULL elements included.

It removes one level only. A list three levels deep, such as [[[1]], [[2, 3]]], comes back two levels deep ([[1], [2, 3]]); call flatten twice to get [1, 2, 3].

NULLs and edge cases

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

run("SELECT flatten(NULL), flatten(NULL::BIGINT[][]), flatten([[1], NULL]), "
    "flatten([NULL, NULL]::BIGINT[][]), flatten([]::BIGINT[][]), flatten([[]]), flatten([[NULL]])")
run("SELECT flatten([[[1]], [[2, 3]]]), flatten(flatten([[[1]], [[2, 3]]])), flatten([['a'], ['b', 'c']])")
run("SELECT flatten([1, 2])")

[(None, None, [1], [], [], [], [None])]
[([[1], [2, 3]], [1, 2, 3], ['a', 'b', 'c'])]
InternalError: Binder Error: No function matches the given name and argument types 'flatten(INTEGER[])'. You might need to add explicit type casts.

Examples

All the items on each shelf, as one list. The NULL box on shelf 2 is skipped; shelf 3 has no boxes, so its list is empty; the NULL row stays NULL:

run(f"SELECT id, flatten(boxes) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, [1, 2, 3]), (2, [4]), (3, []), (4, None)]

Find the shelves that hold item 4, in any box:

run(f"SELECT id FROM {TABLE} WHERE list_contains(flatten(boxes), 4) ORDER BY id LIMIT 10")

[(2,)]

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 every condition on a list runs in the Query Engine:

• In the SELECT list, the scan sends the boxes column and the Query Engine computes flatten() for each row it returns.
• In WHERE, the scan sends boxes for every row, and a FILTER step in the Query Engine flattens 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:

plan(f"SELECT id FROM {TABLE} WHERE list_contains(flatten(boxes), 4)")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│   list_contains(flatten   │
│        (boxes), 4)        │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│           boxes           │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Pair a list condition with a pushed-down one. A comparison of a plain column with a constant, such as id > 1, is evaluated in VAST DataBase, so the rows it rules out never reach the FILTER step. The plan of id > 1 on its own has no FILTER step:

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

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

• Store a flat list if you search it often. If queries usually ask "which rows hold item x" rather than "which box", write the flattened list to its own column when you load the rows, and search that column without calling flatten on every row.

Limitations and errors

• Nested lists only. A flat list, a string, a number, a date, a boolean or a BLOB fails at planning with No function matches the given name and argument types, for example 'flatten(INTEGER[])' or 'flatten(STRING_LITERAL)'. The error lists the one form flatten accepts: flatten(ANY[][]) -> ANY[].
• One level at a time. See What it does.

run("SELECT flatten('abc')")

InternalError: Binder Error: No function matches the given name and argument types 'flatten(STRING_LITERAL)'. You might need to add explicit type casts.

Similar functions

run("SELECT flatten([[1, 2], [3]]), list_concat([1, 2], [3]), array_to_string([[1, 2], [3]], ';')")

[([1, 2, 3], [1, 2, 3], '[1, 2];[3]')]

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.
• list_contains: search the flattened list.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.