union_extract

Applies to VAST Cluster 5.5.

Functions › List, struct and map functions › Union values. Returns the value of one member of a UNION value, by its tag: union_extract(union_value(k := 1), 'k') is 1. If the union holds a different member, the result is NULL. Union values are built in a query with union_value; a table can't have a UNION column.

Syntax

union_extract(u, tag)

Returns: the type of that member: for UNION(k INTEGER, s VARCHAR), 'k' gives an INTEGER and 's' a VARCHAR.

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_utility_union_extract. 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 sensor readings. A reading is either a number or a text status; the last row 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_utility_union_extract"'

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}.readings"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, num BIGINT, status VARCHAR)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, 21, NULL), "
    "(2, NULL, 'offline'), "
    "(3, -4, NULL), "
    "(4, NULL, 'calibrating'), "
    "(5, NULL, NULL)")

[]
[]
[(5,)]

What it does

union_extract(u, tag) returns the member tag of u if that's the member u holds, and NULL if u holds another member:

To find out which member a union holds, use union_tag(u)::VARCHAR.

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 union_extract(union_value(k := NULL), 'k'), union_extract(union_value(k := 1), NULL)")
run("SELECT union_extract(union_value(k := 1)::UNION(k INTEGER, s VARCHAR), 'k'), "
    "union_extract(union_value(k := 1)::UNION(k INTEGER, s VARCHAR), 's')")
run("SELECT union_extract(union_value(k := 1), 'm')")
run("SELECT union_extract(1, 'k')")

[(None, None)]
[(1, None)]
InternalError: Binder Error: Could not find key "m" in union
InternalError: Binder Error: No function matches the given name and argument types 'union_extract(INTEGER_LITERAL, STRING_LITERAL)'. You might need to add explicit type casts.

Examples

Wrap each numeric reading in a union and read it back. The result has the column's type, BIGINT:

run(f"SELECT id, union_extract(union_value(num := num), 'num'), "
    f"typeof(union_extract(union_value(num := num), 'num')) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 21, 'BIGINT'), (2, None, 'BIGINT'), (3, -4, 'BIGINT'), (4, None, 'BIGINT'), (5, None, 'BIGINT')]

One value per reading, a number or a status, of one UNION(num BIGINT, status VARCHAR) type; then the tag and both members of each. The member a row doesn't hold is NULL:

run(f"SELECT id, union_tag(r)::VARCHAR, union_extract(r, 'num'), union_extract(r, 'status') FROM ("
    "SELECT id, CASE WHEN num IS NOT NULL "
    "THEN union_value(num := num)::UNION(num BIGINT, status VARCHAR) "
    "ELSE union_value(status := status)::UNION(num BIGINT, status VARCHAR) END AS r "
    f"FROM {TABLE}) ORDER BY id LIMIT 10")

[(1, 'num', 21, None), (2, 'status', None, 'offline'), (3, 'num', -4, None), (4, 'status', None, 'calibrating'), (5, 'status', None, None)]

Keep only the rows whose union holds a number:

run(f"SELECT id FROM {TABLE} WHERE union_extract(union_value(num := num), 'num') IS NOT NULL ORDER BY id LIMIT 10")

[(1,), (3,)]

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 stores no UNION columns and can't evaluate a function of a column, so union_extract() is always computed by the Query Engine:

• In the SELECT list, the scan reads the columns and the Query Engine builds and reads the union for each row it returns.
• In WHERE, the scan sends the columns for every row, and a FILTER step in the Query Engine computes union_extract() and keeps the rows that match. No rows are skipped in storage.

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

plan(f"SELECT id FROM {TABLE} WHERE union_extract(union_value(num := num), 'num') IS NOT NULL")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│ (union_extract(union_value│
│ (num), 'num') IS NOT NULL)│
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│            num            │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Filter on the raw columns. num IS NOT NULL keeps the same rows as the last example without building a union. A condition on a raw column is pushed down to VAST DataBase, so rows that don't match never leave storage. Compare its plan with the one above:

plan(f"SELECT id FROM {TABLE} WHERE num IS NOT NULL")

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

• Build and read unions last, in the SELECT list or a subquery, from rows already filtered on their columns.

Limitations and errors

• The tag must be a member of the union's type. Otherwise the query fails with Could not find key "m" in union. Cast the union to a type that lists the member, as in the examples, to read a member it may not hold.
• Unions only. Any other first argument fails with No function matches the given name and argument types. For a struct, use struct_extract.
• No UNION columns. Union values exist only inside a query; see union_value.

run("SELECT union_extract(struct_pack(k := 1), 'k')")
run("SELECT struct_extract(struct_pack(k := 1), 'k')")

InternalError: Binder Error: No function matches the given name and argument types 'union_extract(STRUCT(k INTEGER), STRING_LITERAL)'. You might need to add explicit type casts.
[(1,)]

Similar functions

run("SELECT union_extract(union_value(k := 1), 'k'), union_value(k := 1), union_tag(union_value(k := 1))::VARCHAR")
run("SELECT struct_extract(struct_pack(k := 1), 'k')")

[(1, 1, 'k')]
[(1,)]

Clean up:

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

[]
[]

See also

• union_value.
• List, struct and map functions.
• Data types.
• Where filters run and pushdowns.