struct_extract_at

Applies to VAST Cluster 5.5.

Functions › List, struct and map functions › Read a field. Returns the value of one field of a struct, by its position: 1 for the first field. It works on structs with named and with unnamed fields.

Syntax

struct_extract_at(s, position)

Returns: the type of the field.

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_extract_at. 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 of two fields, a INTEGER and b VARCHAR. Row 2 has a NULL field, row 3 has two NULL fields, row 4 has a NULL struct, and row 5 has the largest INTEGER and an empty string:

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

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))")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, {'a': 1, 'b': 'x'}), "
    "(2, {'a': -7, 'b': NULL}), "
    "(3, {'a': NULL, 'b': NULL}), "
    "(4, NULL), "
    "(5, {'a': 2147483647, 'b': ''})")

[]
[]
[(5,)]

What it does

struct_extract_at(s, i) returns the i-th field of s, in the order the struct type declares its fields:

• Positions start at 1. In STRUCT(a INTEGER, b VARCHAR), position 1 is a and position 2 is b. The result has that field's type: typeof(struct_extract_at({'a': 1, 'b': 'x'}, 2)) is VARCHAR.
• Named and unnamed structs. It reads a struct from row as well as one with names. struct_extract takes a position only on unnamed structs.
• The position must be a constant within the struct's fields; both are checked when the query is planned. A string that converts to a whole number, such as '1', is accepted; 1.5 isn't.
• The result can be read further. struct_extract_at({'a': {'b': 2}}, 1).b is 2.

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_extract_at(NULL::STRUCT(a BIGINT), 1), struct_extract_at(NULL, 2), struct_extract_at({'a': 1}, NULL)")
run("SELECT struct_extract_at({'a': 1, 'b': 'x'}, 1), struct_extract_at({'a': 1}, '1'), struct_extract_at(row(1, 'x'), 2), "
    "struct_extract_at({'a': {'b': 2}}, 1).b")
run("SELECT struct_extract_at({'a': 1}, 0)")
run("SELECT struct_extract_at({'a': 1}, 2)")
run("SELECT struct_extract_at({'a': 1}, 1.5)")

[(None, None, None)]
[(1, 1, 'x', 2)]
InternalError: Binder Error: Key index 0 for struct_extract out of range - expected an index between 1 and 1
InternalError: Binder Error: Key index 2 for struct_extract out of range - expected an index between 1 and 1
InternalError: Binder Error: No function matches the given name and argument types 'struct_extract_at(STRUCT(a INTEGER), DECIMAL(2,1))'. You might need to add explicit type casts.

Examples

The first and second field of each row's struct. A NULL field and a NULL struct both give NULL:

run(f"SELECT id, struct_extract_at(st, 1), struct_extract_at(st, 2) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 1, 'x'), (2, -7, None), (3, None, None), (4, None, None), (5, 2147483647, '')]

Keep only the rows whose second field isn't NULL:

run(f"SELECT id FROM {TABLE} WHERE struct_extract_at(st, 2) IS NOT NULL ORDER BY id LIMIT 10")

[(1,), (5,)]

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 the SELECT list, the Query Engine returns the field for each row the scan sends. The plan in Make it faster shows what the scan reads.
• In WHERE, with IS NULL or IS NOT NULL, the scan sends the field (st.b in the plan below) for every row, and a FILTER step in the Query Engine reads the field and keeps the rows that match. No rows are skipped in storage.
• In WHERE, a comparison with a value fails (see Limitations and errors). Compare the field by name instead.

The plan of the WHERE example shows the FILTER step above the scan:

plan(f"SELECT id FROM {TABLE} WHERE struct_extract_at(st, 2) IS NOT NULL")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│ (struct_extract_at(st, 2) │
│        IS NOT NULL)       │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│            st.b           │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Filter by name. st.a = 1 and struct_extract(st, 'a') = 1 are evaluated in VAST DataBase, so rows that don't match never leave storage; their plan has no FILTER step:

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

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

• Select the fields you need, not the whole struct. SELECT st.b and SELECT struct_extract_at(st, 2) both read only the field st.b. The plan lists what the scan reads:

plan(f"SELECT st.b FROM {TABLE}")
plan(f"SELECT struct_extract_at(st, 2) FROM {TABLE}")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│  struct_extract(st, 'b')  │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│     Projections: st.b     │
│                           │
└───────────────────────────┘
┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│  struct_extract_at(st, 2) │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│     Projections: st.b     │
│                           │
└───────────────────────────┘

Limitations and errors

• A comparison in WHERE fails. WHERE struct_extract_at(st, 1) = 1 fails with We encountered an internal error. Please try again. Write the condition with the field's name, st.a = 1 or struct_extract(st, 'a') = 1, which returns the rows and is pushed down.
• The position must be a constant. struct_extract_at(st, id) fails with Key index for struct_extract needs to be a constant value.
• The position must be within the struct. 0, a negative number or a number beyond the last field fails with Key index ... for struct_extract out of range - expected an index between 1 and ....
• The first argument must be a struct. A string, number, list, map or date fails with No function matches the given name and argument types; the error lists the one form, struct_extract_at(STRUCT, BIGINT) -> ANY. For an element of a list, use list_extract.

run(f"SELECT id FROM {TABLE} WHERE struct_extract_at(st, 1) = 1 ORDER BY id LIMIT 10")
run(f"SELECT id, struct_extract_at(st, id) FROM {TABLE} ORDER BY id LIMIT 10")
run("SELECT struct_extract_at([1, 2], 2)")

OSError: We encountered an internal error. Please try again.
InternalError: Binder Error: Key index for struct_extract needs to be a constant value
InternalError: Binder Error: No function matches the given name and argument types 'struct_extract_at(INTEGER[], INTEGER_LITERAL)'. You might need to add explicit type casts.

Similar functions

run("SELECT struct_extract_at({'a': 1, 'b': 'x'}, 2), struct_extract({'a': 1, 'b': 'x'}, 'b'), "
    "struct_extract_at(row(1, 'x'), 2), struct_extract(row(1, 'x'), 2), row(1, 'x')[2]")
run("SELECT {'a': 1}[1]")

[('x', 'x', 'x', 'x', 'x')]
InternalError: Binder Error: struct_extract with an integer key can only be used on unnamed structs, use a string key instead

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 struct functions.
• row: build a struct with unnamed fields.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.