struct_concat

Applies to VAST Cluster 5.5.

Functions › List, struct and map functions › Add fields. Returns one struct with the fields of every struct you give, in order.

Syntax

struct_concat(s1, s2, ...)

Returns: a STRUCT with the fields of s1, then those of s2, and so on: typeof(struct_concat({'a': 1}, {'b': 2})) is STRUCT(a INTEGER, b INTEGER).

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_concat. 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 books, each with a struct of details (meta), a shelf and a number of copies. Book 3 has an empty shelf and a struct of NULL fields, and book 4 has no values. run() also prints the error the Python client raises when it can't convert a result:

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

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.sub(r"^Parse 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}.books"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, meta STRUCT(lang VARCHAR, pages INTEGER), shelf VARCHAR, copies BIGINT)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, {'lang': 'en', 'pages': 12}, 'A1', 3), "
    "(2, {'lang': 'de', 'pages': NULL}, 'B2', 1), "
    "(3, {'lang': NULL, 'pages': NULL}, '', 0), "
    "(4, NULL, NULL, NULL)")

[]
[]
[(4,)]

What it does

struct_concat({'a': 1}, {'b': 2}, {'c': 3}) is {'a': 1, 'b': 2, 'c': 3}. The fields keep their types, so a field can be a list or another struct: struct_concat({'a': {'x': 1}}, {'b': [1]}) is {'a': {'x': 1}, 'b': [1]}. With one struct, it returns that struct.

It's the way to add the fields of a struct you already have, such as a struct column or a struct_pack result. To add fields one by one by name, struct_insert does the same. It doesn't change the table: it returns a new struct for each row.

NULLs and edge cases

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

run("SELECT struct_concat(NULL::STRUCT(a BIGINT), {'b': 1}), struct_concat(NULL::STRUCT(a BIGINT), {'b': 1}) IS NULL, "
    "struct_concat({'a': 1}, NULL::STRUCT(b BIGINT)), struct_concat({'a': 1})")
run("SELECT struct_concat({'a': 1}, NULL)")
run("SELECT struct_concat('abc', {'b': 1})")
run("SELECT struct_concat({'a': 1}, {'a': 2})")
run("SELECT struct_concat({'a': 1}, {'A': 2})")
run("SELECT struct_concat({'a': 1}, row(2))")
run("SELECT struct_concat()")
run("SELECT struct_concat({'a': 1}, {})")

[({'a': None, 'b': 1}, False, {'a': 1, 'b': None}, {'a': 1})]
InternalError: Invalid Input Error: struct_concat: Argument at position "2" is not a STRUCT
InternalError: Invalid Input Error: struct_concat: Argument at position "1" is not a STRUCT
InternalError: Invalid Input Error: struct_concat: Arguments contain duplicate STRUCT entry "a"
InternalError: Invalid Input Error: struct_concat: Arguments contain case-insensitive duplicate STRUCT entry "A" and "a"
InternalError: Invalid Input Error: struct_concat: Cannot mix named and unnamed STRUCTs
InternalError: Invalid Input Error: struct_concat: At least one argument is required
InternalError: Parser Error: syntax error at or near "}"

Examples

Join each book's details with a struct of its shelf and copies. Book 4's meta, shelf and copies are NULL, so the result is a struct whose fields are all NULL, not a NULL struct:

run(f"SELECT id, struct_concat(meta, {{'shelf': shelf, 'copies': copies}}) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, {'lang': 'en', 'pages': 12, 'shelf': 'A1', 'copies': 3}), (2, {'lang': 'de', 'pages': None, 'shelf': 'B2', 'copies': 1}), (3, {'lang': None, 'pages': None, 'shelf': '', 'copies': 0}), (4, {'lang': None, 'pages': None, 'shelf': None, 'copies': None})]

Join three structs, one of them from struct_pack:

run(f"SELECT id, struct_concat({{'id': id}}, meta, struct_pack(shelf := shelf)) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, {'id': 1, 'lang': 'en', 'pages': 12, 'shelf': 'A1'}), (2, {'id': 2, 'lang': 'de', 'pages': None, 'shelf': 'B2'}), (3, {'id': 3, 'lang': None, 'pages': None, 'shelf': ''}), (4, {'id': 4, 'lang': None, 'pages': None, 'shelf': None})]

Filter on a field of the result:

run(f"SELECT id FROM {TABLE} WHERE struct_concat(meta, {{'copies': copies}}).copies > 0 ORDER BY id LIMIT 10")

[(1,), (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). VAST DataBase can evaluate a comparison of a column, or of a struct field, with a constant, but not a function of a column, so struct_concat() is always computed by the Query Engine:

• In the SELECT list, the scan reads the columns and the Query Engine builds the new struct for each row it returns.
• In WHERE, the scan sends the columns for every row, and a FILTER step in the Query Engine computes the condition 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 struct_concat(meta, {{'copies': copies}}).copies > 0")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #2            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│      (struct_extract      │
│    (struct_concat(meta,   │
│   struct_pack(copies)),   │
│       'copies') > 0)      │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│            meta           │
│           copies          │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Filter on the stored column or field, not on the joined struct. copies > 0 and meta.pages > 5 compare a column or a struct field with a constant, which VAST DataBase evaluates, so rows that don't match never leave storage. The plan has no FILTER step:

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

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

• Build the struct in the SELECT list. Filter with the plain columns in WHERE, and join the structs only for the rows you return.

Limitations and errors

• Field names must be different, without regard to case. A struct joined with itself fails with struct_concat: Arguments contain duplicate STRUCT entry "...". Rename the fields first, for example with a literal: struct_concat(meta, {'lang2': meta.lang}).
• Every argument must be a typed struct. Anything else, including an untyped NULL, fails with struct_concat: Argument at position "n" is not a STRUCT. Cast a NULL: NULL::STRUCT(b BIGINT).
• Named or unnamed, not both. A struct from row can't be joined with a named struct: Cannot mix named and unnamed STRUCTs. Two unnamed structs can be joined, but the Python client can't read the result: ValueError: Converting to Python dictionary is not supported when duplicate field names are present. Check its type with typeof(), or read its fields by position.

run(f"SELECT id, struct_concat(meta, meta) FROM {TABLE} ORDER BY id LIMIT 10")
run(f"SELECT id, struct_concat(meta, {{'lang2': meta.lang}}) FROM {TABLE} ORDER BY id LIMIT 10")
run("SELECT struct_concat(row(1), row(2))")
run("SELECT typeof(struct_concat(row(1), row(2))), struct_concat(row(1), row(2))[2]")

InternalError: Invalid Input Error: struct_concat: Arguments contain duplicate STRUCT entry "lang"
[(1, {'lang': 'en', 'pages': 12, 'lang2': 'en'}), (2, {'lang': 'de', 'pages': None, 'lang2': 'de'}), (3, {'lang': None, 'pages': None, 'lang2': None}), (4, {'lang': None, 'pages': None, 'lang2': None})]
ValueError: Converting to Python dictionary is not supported when duplicate field names are present
[('STRUCT(INTEGER, INTEGER)', 2)]

Similar functions

run("SELECT struct_concat({'a': 1}, {'b': 'x'}), struct_concat({'a': 1}, struct_pack(b := 'x')), "
    "struct_insert({'a': 1}, b := 'x')")
run("SELECT struct_concat(NULL::STRUCT(a BIGINT), {'b': 1}), struct_insert(NULL::STRUCT(a BIGINT), b := 1)")

[({'a': 1, 'b': 'x'}, {'a': 1, 'b': 'x'}, {'a': 1, 'b': 'x'})]
[({'a': None, 'b': 1}, {'a': None, 'b': 1})]

Clean up:

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

[]
[]

See also

• struct_insert: add named fields one by one.
• struct_pack and struct_extract: build a struct and read its fields.
• List, struct and map functions: all the struct functions.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.