struct_insert

Applies to VAST Cluster 5.5.

Functions › List, struct and map functions › Add fields. Returns a struct with the fields of the original struct followed by the named fields you give.

Syntax

struct_insert(s, name := value, ...)

Returns: a STRUCT with the fields of s, then the new fields in the order given: typeof(struct_insert({'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_insert. 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_insert"'

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}.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_insert({'a': 1}, b := 2) is {'a': 1, 'b': 2}. You can add several fields in one call, of any type, including lists and other structs: struct_insert({'a': 1}, b := 2, c := 'x') is {'a': 1, 'b': 2, 'c': 'x'}. The new fields always go at the end.

It doesn't change the table: it returns a new struct for each row. It only adds fields; it can't replace or remove one (see Limitations and errors).

NULLs and edge cases

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

run("SELECT struct_insert(NULL::STRUCT(a BIGINT), b := 1), struct_insert(NULL::STRUCT(a BIGINT), b := 1) IS NULL, "
    "struct_insert({'a': 1}, b := NULL)")
run("SELECT struct_insert({'a': 1}, b := {'c': 3}), struct_insert({'a': 1}, b := [1, 2]), "
    "struct_insert({'a': 1}, b := 2) = {'a': 1, 'b': 2}")
run("SELECT struct_insert(NULL, b := 1)")
run("SELECT struct_insert('abc', b := 1)")
run("SELECT struct_insert({'a': 1}, A := 2)")
run("SELECT struct_insert({'a': 1})")
run("SELECT struct_insert({'a': 1}, 2)")

[({'a': None, 'b': 1}, False, {'a': 1, 'b': None})]
[({'a': 1, 'b': {'c': 3}}, {'a': 1, 'b': [1, 2]}, True)]
InternalError: Invalid Input Error: The first argument to struct_insert must be a STRUCT
InternalError: Invalid Input Error: The first argument to struct_insert must be a STRUCT
InternalError: Binder Error: Duplicate struct entry name "A"
InternalError: Invalid Input Error: Can't insert nothing into a STRUCT
InternalError: Binder Error: Need named argument for struct insert, e.g., a := b

Examples

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

run(f"SELECT id, struct_insert(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})]

Add a field computed from another field:

run(f"SELECT id, struct_insert(meta, lang_upper := upper(meta.lang)) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, {'lang': 'en', 'pages': 12, 'lang_upper': 'EN'}), (2, {'lang': 'de', 'pages': None, 'lang_upper': 'DE'}), (3, {'lang': None, 'pages': None, 'lang_upper': None}), (4, {'lang': None, 'pages': None, 'lang_upper': None})]

Filter on a field of the result:

run(f"SELECT id FROM {TABLE} WHERE struct_insert(meta, copies := copies).copies = 3 ORDER BY id LIMIT 10")

[(1,)]

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_insert() 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_insert(meta, copies := copies).copies = 3")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #2            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│      (struct_extract      │
│    (struct_insert(meta,   │
│   copies), 'copies') = 3) │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│            meta           │
│           copies          │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Filter on the stored column or field, not on the new struct. copies = 3 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 meta.pages > 5")

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

• Build the struct in the SELECT list. Filter with the plain columns in WHERE, and add the fields only to the rows you return.

Limitations and errors

• Only new names. A field the struct already has fails with Duplicate struct entry name "...", and names are compared without regard to case: A clashes with a. To change a field's value, build the struct again with a literal: {'lang': meta.lang, 'pages': 0}.
• No functions to update or drop a field. struct_update and struct_drop fail with Scalar Function with name ... does not exist!. Build a struct with the fields you want to keep.
• The first argument must be a typed struct. Anything else, including an untyped NULL, fails with The first argument to struct_insert must be a STRUCT. Cast a NULL: NULL::STRUCT(a BIGINT).
• Every new field needs a name. A plain value fails with Need named argument for struct insert, e.g., a := b.
• Adding to a row. struct_insert(row(1, 'x'), b := 2) runs, but the result has unnamed fields, so the Python client raises ValueError: Converting to Python dictionary is not supported when duplicate field names are present.

run(f"SELECT id, {{'lang': meta.lang, 'pages': 0}} FROM {TABLE} ORDER BY id LIMIT 10")
run("SELECT struct_update({'a': 1}, a := 2)")
run("SELECT struct_insert(row(1, 'x'), b := 2)")

[(1, {'lang': 'en', 'pages': 0}), (2, {'lang': 'de', 'pages': 0}), (3, {'lang': None, 'pages': 0}), (4, {'lang': None, 'pages': 0})]
InternalError: Catalog Error: Scalar Function with name struct_update does not exist!
ValueError: Converting to Python dictionary is not supported when duplicate field names are present

Similar functions

run("SELECT struct_insert({'a': 1}, b := 'x'), struct_concat({'a': 1}, {'b': 'x'}), "
    "struct_concat({'a': 1}, struct_pack(b := 'x'))")
run("SELECT struct_insert(NULL::STRUCT(a BIGINT), b := 1), struct_concat(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_concat: join the fields of several structs.
• 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.