struct_pack

Applies to VAST Cluster 5.5.

Functions › List, struct and map functions › Build a struct. Returns a struct with the named fields you give it, each with the type of its value. The literal {'name': value, ...} builds the same struct.

Syntax

struct_pack(name := value, ...)
struct_pack(name => value, ...)
{'name': value, ...}

Returns: STRUCT(name type, ...), with the fields in the order you give them.

Aliases: the {'name': value, ...} literal.

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_structs_struct_pack. 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 with a struct column st (fields a INTEGER and b VARCHAR), a string k and a list ks. 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_structs_struct_pack"'

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}.items"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, st STRUCT(a INTEGER, b VARCHAR), k VARCHAR, ks VARCHAR[])")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, {'a': 1, 'b': 'x'}, 'de', ['en', 'de']), "
    "(2, {'a': -7, 'b': NULL}, 'fr', ['\u00e9', '\u00fc']), "
    "(3, {'a': NULL, 'b': NULL}, '', []), "
    "(4, NULL, NULL, NULL), "
    "(5, {'a': 2147483647, 'b': ''}, 'k3', ['k1', 'k2', 'k3'])")

[]
[]
[(5,)]

What it does

struct_pack makes one struct per row from the values you name:

• Each argument is a field. struct_pack(a := 1, b := 'x') is {'a': 1, 'b': 'x'}, of type STRUCT(a INTEGER, b VARCHAR). => works in place of :=.
• Any value type, including lists, maps, dates and other structs: struct_pack(a := struct_pack(b := 1)) is {'a': {'b': 1}}. Python receives a struct as a dict.
• The literal is the same struct. struct_pack(a := 1) = {'a': 1} is true. In the literal, a name can be quoted or not: {a: 1} is {'a': 1}.
• Names that aren't plain words are quoted: struct_pack("a b" := 1) has a field a b.
• A cast reshapes a struct by field name. {'a': 1, 'b': 2}::STRUCT(b BIGINT, a BIGINT) reorders the fields, a field the target type adds is NULL, and a field it leaves out is dropped. At least one name must match.

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 struct_pack(a := NULL), struct_pack(a := NULL) IS NULL, struct_pack(a := NULL, b := NULL)")
run("SELECT {'a': 1}::STRUCT(a BIGINT, b BIGINT), {'a': 1, 'b': 2}::STRUCT(a BIGINT), "
    "{'a': 1, 'b': 2}::STRUCT(b BIGINT, a BIGINT)")
run("SELECT struct_pack(a := 1, A := 2)")
run("SELECT struct_pack()")
run("SELECT {'a': 1}::STRUCT(z BIGINT)")

[({'a': None}, False, {'a': None, 'b': None})]
[({'a': 1, 'b': None}, {'a': 1}, {'b': 2, 'a': 1})]
InternalError: Binder Error: Duplicate struct entry name "A"
InternalError: Invalid Input Error: Can't pack nothing into a struct
InternalError: Binder Error: STRUCT to STRUCT cast must have at least one matching member

Examples

A struct per row from other columns, with struct_pack and with the literal. A NULL value gives a NULL field, so row 4 still gets a struct:

run(f"SELECT id, struct_pack(id := id, label := st.b), {{'id': id, 'n': len(ks)}} FROM {TABLE} ORDER BY id LIMIT 10")

[(1, {'id': 1, 'label': 'x'}, {'id': 1, 'n': 2}), (2, {'id': 2, 'label': None}, {'id': 2, 'n': 2}), (3, {'id': 3, 'label': None}, {'id': 3, 'n': 0}), (4, {'id': 4, 'label': None}, {'id': 4, 'n': None}), (5, {'id': 5, 'label': ''}, {'id': 5, 'n': 3})]

Write structs to a struct column. This block creates a second table, copies, and inserts a struct built by struct_pack from the first table, a literal with a missing field, and a literal with an extra field. The read-back shows what each row stores:

COPIES = f"{SCHEMA}.copies"
run(f"CREATE TABLE IF NOT EXISTS {COPIES} (id BIGINT, st STRUCT(a INTEGER, b VARCHAR))")
run(f"INSERT INTO {COPIES} SELECT id, struct_pack(a := st.a, b := k) FROM {TABLE} WHERE id = 1")
run(f"INSERT INTO {COPIES} VALUES (2, {{'a': 1}}), (3, {{'a': 1, 'b': 'x', 'c': 2}})")
run(f"SELECT id, st FROM {COPIES} ORDER BY id LIMIT 10")

[]
[(1,)]
[(2,)]
[(1, {'a': 1, 'b': 'de'}), (2, {'a': 1, 'b': None}), (3, {'a': 1, 'b': '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). struct_pack builds its struct in the Query Engine:

• In the SELECT list, the scan reads the columns the fields use, and the Query Engine builds the struct for each row it returns.
• In WHERE, a condition on a struct you build, such as struct_pack(a := st.a) = {'a': 1}, runs in a FILTER step in the Query Engine: the scan sends the columns for every row. No rows are skipped in storage.

The plan of that condition shows the FILTER step above the scan:

run(f"SELECT id FROM {TABLE} WHERE struct_pack(a := st.a) = {{'a': 1}} ORDER BY id LIMIT 10")
plan(f"SELECT id FROM {TABLE} WHERE struct_pack(a := st.a) = {{'a': 1}}")

[(1,)]
┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│        (struct_pack       │
│ (struct_extract(st, 'a')) │
│        = {'a': 1})        │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│            st.a           │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Compare fields, not structs you build. st.a = 1 returns the same rows as struct_pack(a := st.a) = {'a': 1}, and it's evaluated in VAST DataBase: its plan has no FILTER step:

run(f"SELECT id FROM {TABLE} WHERE st.a = 1 ORDER BY id LIMIT 10")
plan(f"SELECT id FROM {TABLE} WHERE st.a = 1")

[(1,)]
┌───────────────────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│      Projections: id      │
│                           │
└───────────────────────────┘

Limitations and errors

• Every argument needs a name. struct_pack(42) fails with Need named argument for struct pack. An empty struct can't be made: struct_pack() fails with Can't pack nothing into a struct, and {} with a syntax error.
• Names are unique, ignoring case. struct_pack(a := 1, A := 2) and {'a': 1, 'a': 2} fail with Duplicate struct entry name.
• Writing to a struct column matches fields by name. A struct whose names don't match any field of the column fails with STRUCT to STRUCT cast must have at least one matching member; a value that doesn't convert to the field's type fails with a conversion error, such as Could not convert string 'notint' to INT32.
• Other SQL dialects' forms aren't available. named_struct('a', 1) fails with Scalar Function with name named_struct does not exist!, and struct(1 AS a) and ROW(1 AS a) with a syntax error. Use struct_pack(a := 1) or {'a': 1}.

run("SELECT struct_pack(42)")
run("SELECT {'a': 1, 'a': 2}")
run(f"INSERT INTO {COPIES} VALUES (4, {{'x': 1, 'y': 'x'}})")
run(f"INSERT INTO {COPIES} VALUES (5, {{'a': 'notint', 'b': 'x'}})")
run("SELECT named_struct('a', 1, 'b', 'x')")

InternalError: Binder Error: Need named argument for struct pack, e.g. STRUCT_PACK(a := b)
InternalError: Binder Error: Duplicate struct entry name "a"
InternalError: Binder Error: STRUCT to STRUCT cast must have at least one matching member
InternalError: Conversion Error: Could not convert string 'notint' to INT32
InternalError: Catalog Error: Scalar Function with name named_struct does not exist!

Similar functions

run("SELECT struct_pack(a := 1, b := 'x'), {'a': 1, 'b': 'x'}, typeof(row(1, 'x')), to_json(row(1, 'x'))")
run("SELECT struct_insert({'a': 1}, b := 'x'), struct_concat({'a': 1}, {'b': 'x'})")

[({'a': 1, 'b': 'x'}, {'a': 1, 'b': 'x'}, 'STRUCT(INTEGER, VARCHAR)', '{"":1,"":"x"}')]
[({'a': 1, 'b': 'x'}, {'a': 1, 'b': 'x'})]

Clean up:

run(f"DROP TABLE IF EXISTS {COPIES}")
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 struct functions.
• struct_extract: read a field.
• Data types in SQL: struct columns.
• Where filters run and pushdowns.