list_zip

Applies to VAST Cluster 5.5.

Functions › List, struct and map functions › Combine and change lists. Pairs up the elements of two or more lists, position by position, and returns a list of structs: one struct per position, one field per list. array_zip is the same function.

Syntax

list_zip(list1, list2 [, ...] [, truncate])
array_zip(list1, list2 [, ...] [, truncate])

Returns: a list of structs with one unnamed field per list, such as STRUCT(INTEGER, VARCHAR)[].

Aliases: array_zip.

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_zip. 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 weather stations, each with a list of sensor names and a list of readings. Station 2 has more sensors than readings, station 3 has empty lists, and the last row has no values. run() also catches ValueError: that's how the Python driver reports a result it can't convert to Python objects, such as the unnamed struct fields that list_zip returns:

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

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, ValueError) 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}.stations"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, sensors VARCHAR[], readings DOUBLE[])")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, ['t1', 't2'], [20.5, 21.0]), "
    "(2, ['t1', 't2', 't3'], [19.0]), "
    "(3, [], []), "
    "(4, NULL, NULL)")

[]
[]
[(4,)]

What it does

list_zip builds one struct per position: the first struct holds the first element of each list, the second struct the second elements, and so on. list_zip([1, 2], ['a', 'b']) is [(1, a), (2, b)], of type STRUCT(INTEGER, VARCHAR)[].

• Lists of different lengths. By default the result is as long as the longest list, and a shorter list contributes NULL: list_zip([1, 2], ['a']) is [(1, a), (2, NULL)]. With true as the last argument, the result stops at the shortest list: [(1, a)].
• The struct fields have no names. Read them by position inside the query: z[1], z[2], or struct_extract(z, 1). The Python driver can't convert a struct with unnamed fields to Python objects, so a query that returns the result of list_zip as it is ends with ValueError: Converting to Python dictionary is not supported when duplicate field names are present, although the query ran on the cluster. Return the fields you need, or cast the result to VARCHAR or convert it with to_json.

NULLs and edge cases

The same cases, as the cluster returns them, cast to VARCHAR so the driver can read them. They use the table and helpers from Set up the examples:

run("SELECT CAST(list_zip(NULL, NULL) AS VARCHAR), CAST(list_zip([1], NULL) AS VARCHAR), "
    "CAST(list_zip([], []) AS VARCHAR)")
run("SELECT CAST(list_zip([1, 2], ['a']) AS VARCHAR), CAST(list_zip([1, 2], ['a'], true) AS VARCHAR), "
    "CAST(list_zip([1, 2], ['a'], false) AS VARCHAR)")
run("SELECT CAST(list_zip([1], ['a'], [true]) AS VARCHAR), CAST(list_zip([1, 2]) AS VARCHAR)")
run("SELECT list_zip()")
run("SELECT list_zip(1, 2)")

[('[]', '[(1, NULL)]', '[]')]
[('[(1, a), (2, NULL)]', '[(1, a)]', '[(1, a), (2, NULL)]')]
[('[(1, a, true)]', '[(1), (2)]')]
InternalError: Binder Error: Provide at least one argument to list_zip
InternalError: Binder Error: Parameter type needs to be List

Examples

The result as it is can't be read by the Python driver; the same query cast to VARCHAR can. Station 2 has three sensors and one reading, so the last two pairs have a NULL reading; the NULL lists of station 4 give an empty list:

run(f"SELECT id, list_zip(sensors, readings) FROM {TABLE} ORDER BY id LIMIT 10")
run(f"SELECT id, CAST(list_zip(sensors, readings) AS VARCHAR) FROM {TABLE} ORDER BY id LIMIT 10")

ValueError: Converting to Python dictionary is not supported when duplicate field names are present
[(1, '[(t1, 20.5), (t2, 21.0)]'), (2, '[(t1, 19.0), (t2, NULL), (t3, NULL)]'), (3, '[]'), (4, '[]')]

Read the fields by position. z[1] is the sensor and z[2] the reading; with true, the pairs stop at the shorter list:

run(f"SELECT id, list_transform(list_zip(sensors, readings, true), z -> z[1] || '=' || z[2]) "
    f"FROM {TABLE} ORDER BY id LIMIT 10")
run("SELECT list_zip([1, 2], ['a', 'b'])[2][2], struct_extract(list_zip([1, 2], ['a', 'b'])[1], 1)")

[(1, ['t1=20.5', 't2=21.0']), (2, ['t1=19.0']), (3, []), (4, [])]
[('b', 1)]

As JSON, the fields have empty names:

run("SELECT to_json(list_zip([1, 2], ['a', 'b']))")

[('[{"":1,"":"a"},{"":2,"":"b"}]',)]

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 list columns whole, and list_zip() is always computed by the Query Engine:

• In the SELECT list, the scan sends sensors and readings, and the Query Engine builds the structs for each row it returns.
• In WHERE, a FILTER step in the Query Engine computes list_zip() for every row the scan sends. No rows are skipped in storage.

The plan of a condition on the result shows the FILTER step above the scan:

plan(f"SELECT id FROM {TABLE} WHERE len(list_zip(sensors, readings)) > 2")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #2            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│   (len(list_zip(sensors,  │
│       readings)) > 2)     │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│          sensors          │
│          readings         │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Return only what you need. Rather than the whole list of structs, return the values you use, such as list_transform(list_zip(a, b), z -> z[2]): the result is a plain list, which the driver reads directly, and there's no cast to text.
• Pair a condition on the result 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.

run(f"SELECT id, list_transform(list_zip(sensors, readings), z -> z[2]) FROM {TABLE} WHERE id > 1 ORDER BY id LIMIT 10")

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

Limitations and errors

• The Python driver can't read the result as it is. See What it does. This is a conversion error in the client, after the query ran.
• Lists only. Arguments that aren't lists fail with Parameter type needs to be List; a call with no argument fails with Provide at least one argument to list_zip.
• No field names. To get named fields, build the structs yourself with a lambda, as in the next section.

Similar functions

run("SELECT CAST(list_zip([1, 2], ['a', 'b']) AS VARCHAR), "
    "CAST(list_transform(range(1, 3), i -> {'n': [1, 2][i], 's': ['a', 'b'][i]}) AS VARCHAR), "
    "CAST(map([1, 2], ['a', 'b']) AS VARCHAR)")

[('[(1, a), (2, b)]', "[{'n': 1, 's': a}, {'n': 2, 's': b}]", '{1=a, 2=b}')]

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.
• struct_extract: read a struct field.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.