unnest

Applies to VAST Cluster 5.5.

Functions › Table functions › Rows from a list. Turns a list into rows, one row per element. In FROM, it expands a list you give it; in the SELECT list of a query on a table, it expands a list column, repeating the row's other columns for each element.

Syntax

unnest(list)
unnest(list, recursive := true)
unnest(struct)

Argument	Type	Description
`list`	A list of any element type (`ANY[]`)	The list to expand: a literal or expression in `FROM`, or a list column in the `SELECT` list.
`recursive`	`BOOLEAN`	In the `SELECT` list only: `true` expands nested lists all the way down.
`struct`	A struct	In the `SELECT` list only: turns the struct's fields into columns.

Returns: one row per element, of the element type. In FROM, the column is named unnest; give it an alias: FROM unnest([1, 2]) t(v).

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_table_functions_unnest. 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 articles with a list of tags. Article 2 has no tags, article 3 has a NULL list, and one of article 4's tags is NULL:

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_table_functions_unnest"'

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).replace(BUCKET, "<bucket>"))

TABLE = f"{SCHEMA}.articles"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, title VARCHAR, tags VARCHAR[])")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, 'Intro', ['sql', 'howto']), "
    "(2, 'Notes', []), "
    "(3, 'Draft', NULL), "
    "(4, 'Guide', ['vector', NULL, 'sql'])")

[]
[]
[(4,)]

What it does

unnest works in two places:

Where	What it expands	Example
`FROM`	A list literal or an expression that returns a list	`SELECT * FROM unnest(['x', 'y'])` returns rows `'x'` and `'y'`
The `SELECT` list of a query on a table	A list column, one table row at a time	`SELECT id, unnest(tags) FROM articles` returns one row per tag, with the article's `id`

A list of lists is expanded one level: unnest([[1, 2], [3]]) gives the rows [1, 2] and [3]. A list of structs gives one struct per row. In the SELECT list, unnest(list, recursive := true) expands every level, so [[1, 2], [3]] gives 1, 2 and 3, and unnest(struct) turns a struct into one column per field.

Two unnest() calls in one SELECT list are expanded side by side: the first elements form the first row, and so on. The shorter list is padded with NULLs.

NULLs and edge cases

Input	Result
An empty list, or a typed NULL list such as `NULL::INTEGER[]`	No rows. In the `SELECT` list, the table row doesn't appear in the result.
A NULL element, such as `[1, NULL, 3]`	A NULL row for that element
An untyped `NULL`, a number, a string, a struct or a map in `FROM`	Error: `Binder Error: UNNEST requires a single list as input`
A fixed-size array, such as `[1, 2, 3]::INTEGER[3]`	Error: `Binder Error: UNNEST requires a single list as input`; cast it to a list, `::INTEGER[]`
Two lists in `FROM`, such as `unnest([1, 2], [10, 20, 30])`	Error: `No function matches the given name and argument types 'unnest(INTEGER[], INTEGER[])'`
`recursive := true` in `FROM`	Error: `Binder Error: Invalid named parameter "recursive" for function unnest`

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

run("SELECT * FROM unnest([]::INTEGER[])")
run("SELECT * FROM unnest(NULL::INTEGER[])")
run("SELECT * FROM unnest([1, NULL, 3])")
run("SELECT * FROM unnest([[1, 2], [3]])")
run("SELECT * FROM unnest(NULL)")
run("SELECT * FROM unnest([1, 2, 3]::INTEGER[3])")
run("SELECT * FROM unnest([1, 2], [10, 20, 30])")
run("SELECT * FROM unnest([[1, 2], [3]], recursive := true)")

[]
[]
[(1,), (None,), (3,)]
[([1, 2],), ([3],)]
InternalError: Binder Error: UNNEST requires a single list as input
InternalError: Binder Error: UNNEST requires a single list as input
InternalError: Binder Error: No function matches the given name and argument types 'unnest(INTEGER[], INTEGER[])'. You might need to add explicit type casts.
InternalError: Binder Error: Invalid named parameter "recursive" for function unnest

Examples

Rows from a list built by another function:

run("SELECT * FROM unnest(string_split('a,b,c', ',')) t(part)")
run("SELECT * FROM unnest(range(3))")

[('a',), ('b',), ('c',)]
[(0,), (1,), (2,)]

One row per tag of each article. Articles 2 and 3 have no tags, so they don't appear; article 4's NULL tag gives a NULL row. ORDER BY on the article alone doesn't keep the tags of an article in list order, so sort by the tag too:

run(f"SELECT id, unnest(tags) AS tag FROM {TABLE} ORDER BY id, tag LIMIT 20")

[(1, 'howto'), (1, 'sql'), (4, 'sql'), (4, 'vector'), (4, None)]

The tags of one article, and the number of tags of all articles:

run(f"SELECT id, title, unnest(tags) AS tag FROM {TABLE} WHERE id = 1")
run(f"SELECT count(*) FROM (SELECT unnest(tags) FROM {TABLE})")

[(1, 'Intro', 'sql'), (1, 'Intro', 'howto')]
[(5,)]

Two lists side by side, a nested list expanded all the way down, and a struct turned into columns:

run("SELECT unnest([1, 2]), unnest(['a', 'b', 'c'])")
run("SELECT unnest([[1, 2], [3]], recursive := true)")
run("SELECT unnest({'a': 1, 'b': 'x'})")

[(1, 'a'), (2, 'b'), (None, 'c')]
[(1,), (2,), (3,)]
[(1, 'x')]

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).

In FROM, unnest replaces the scan: it produces its rows in the Query Engine, and the plan shows an UNNEST step where VAST_SCAN would be. A WHERE condition on the rows runs in a FILTER step above it.
In the SELECT list, the scan reads the list column in VAST DataBase, and an UNNEST step above it turns each list into rows. A WHERE condition on the table's columns, such as id = 1, is evaluated in the scan, before the lists are expanded.

The plans show both:

plan("SELECT * FROM unnest([1, 2, 3]) t(v) WHERE v > 1")
plan(f"SELECT id, unnest(tags) AS tag FROM {TABLE} WHERE id = 1")

┌───────────────────────────┐
│           FILTER          │
│    ────────────────────   │
│          (v > 1)          │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│          UNNEST           │
│    ────────────────────   │
│      Function: UNNEST     │
│                           │
└───────────────────────────┘
┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             id            │
│            tag            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           UNNEST          │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│             id            │
│            tags           │
│                           │
└───────────────────────────┘

Make it faster

Expand list columns in the SELECT list. SELECT id, unnest(tags) FROM t reads only id and tags, and conditions on the other columns still push down to VAST DataBase (pushdowns).
Filter rows before you expand them. Put conditions on the table's columns in WHERE of the same query, as in WHERE id = 1 above, so fewer lists are expanded.

Limitations and errors

Not in WHERE. WHERE unnest(tags) = 'sql' fails with Binder Error: UNNEST not supported here. To keep the rows that have a value in a list, use list_contains.
No lateral unnest in FROM. FROM t, unnest(t.tags) and FROM t CROSS JOIN unnest(tags) fail with The operator 'HASH_GROUP_BY' type=9 is currently not supported. Use unnest() in the SELECT list.
No nested calls. unnest(unnest(x)) fails with Binder Error: Nested UNNEST calls are not supported - use UNNEST(x, recursive := true) to unnest multiple levels.
No GROUP BY on the elements. It fails with The operator 'HASH_GROUP_BY' type=9 is currently not supported. An aggregate over all the elements, such as count(*), works.
ORDER BY needs a LIMIT. Without one it fails with The operator 'ORDER_BY' type=1 is currently not supported for this usage.

run(f"SELECT id FROM {TABLE} WHERE unnest(tags) = 'sql'")
run(f"SELECT id, tag FROM {TABLE}, unnest(tags) t(tag)")
run("SELECT unnest(unnest([[1, 2], [3]]))")
run(f"SELECT tag, count(*) FROM (SELECT unnest(tags) AS tag FROM {TABLE}) GROUP BY tag ORDER BY tag LIMIT 20")
run(f"SELECT id FROM {TABLE} WHERE list_contains(tags, 'sql') ORDER BY id LIMIT 10")

InternalError: Binder Error: UNNEST not supported here
InternalError: The operator 'HASH_GROUP_BY' type=9 is currently not supported.
InternalError: Binder Error: Nested UNNEST calls are not supported - use UNNEST(x, recursive := true) to unnest multiple levels
InternalError: The operator 'HASH_GROUP_BY' type=9 is currently not supported.
[(1,), (4,)]

Similar functions

Function	Turns	Into
`unnest`	a list	one row per element
range and generate_series in `FROM`	a start, a stop and a step	one row per value
repeat in `FROM`	a value and a count	the same value in that many rows
string_split	a string	a list, which `unnest` can expand

run("SELECT * FROM unnest([10, 20, 30])")
run("SELECT * FROM range(10, 31, 10)")
run("SELECT * FROM repeat(10, 3)")

[(10,), (20,), (30,)]
[(10,), (20,), (30,)]
[(10,), (10,), (10,)]

Clean up:

run(f"DROP TABLE IF EXISTS {TABLE}")
run(f"DROP SCHEMA IF EXISTS {SCHEMA}")
conn.close()

[]
[]