--- title: "flatten" slug: "dev-database-reference-sql-functions-lists-flatten" updated: 2026-10-30T18:19:11Z published: 2026-10-30T18:19:11Z canonical: "docs.vastdata.com/dev-database-reference-sql-functions-lists-flatten" --- > ## 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. # flatten *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.* 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) ``` | Argument | Type | Description | | --- | --- | --- | | `nested_list` | a list of lists, such as `BIGINT[][]` | The list to flatten. | **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)](/develop/docs/dev-database-integrations-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](/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 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, "") 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 | Input | Result | | --- | --- | | `NULL` | `NULL` | | `NULL::BIGINT[][]` | `NULL` | | `[[1], NULL]` (a NULL inner list) | `[1]` | | `[NULL, NULL]::BIGINT[][]` (only NULL inner lists) | `[]` | | `[]::BIGINT[][]` (no inner lists) | `[]` | | `[[]]` (one empty inner list) | `[]` | | `[[NULL]]` (a NULL element) | `[NULL]` | | `[[[1]], [[2, 3]]]` (three levels) | `[[1], [2, 3]]` | | `flatten(flatten([[[1]], [[2, 3]]]))` | `[1, 2, 3]` | | `[['a'], ['b', 'c']]` | `['a', 'b', 'c']` | | A list that isn't nested, such as `[1, 2]` | Error: `No function matches the given name and argument types 'flatten(INTEGER[])'` | The same cases, as the cluster returns them. They use the table and helpers from [Set up the examples](/develop/docs/dev-database-reference-sql-functions-lists-flatten#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](/develop/docs/dev-database-reference-sql-functions-lists-flatten#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 | Function | What it does | Result for `[[1, 2], [3]]`, or `[1, 2]` and `[3]` | | --- | --- | --- | | `flatten` | joins all the inner lists of one nested list | `[1, 2, 3]` | | [list_concat](/develop/docs/dev-database-reference-sql-functions-lists-list-concat) | joins two lists | `[1, 2, 3]` | | [array_to_string](/develop/docs/dev-database-reference-sql-functions-lists-array-to-string) | joins the elements of a list into text | `'[1, 2];[3]'` with separator `';'` | | [unnest](/develop/docs/dev-database-reference-sql-functions-table-functions-unnest) | turns the elements of a list into rows | rows, not a list | ``` 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](/develop/docs/dev-database-reference-sql-functions-lists-and-structs): all the list functions. - [list_contains](/develop/docs/dev-database-reference-sql-functions-lists-list-contains): search the flattened list. - [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.