Welcome to the new DOCS.vastdata.com. We've added new menus and a version selector, plus new persona and resource pages to help customers get started. Looking for a previous article? Use the docs search.

Press CTRL+K to open search

struct_extract

Prev Next

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 name. s.field, s['field'] and array_extract(s, 'field') are the same function; on a struct with unnamed fields, struct_extract also takes a position.

Syntax

struct_extract(s, 'field')
s.field
s['field']
array_extract(s, 'field')
struct_extract(s, position)
Argument Type Description
s a struct The struct to read.
field a constant VARCHAR The name of the field. The match ignores case.
position a constant BIGINT On a struct with unnamed fields only, such as one from row: the position of the field, starting at 1.

Returns: the type of the field.

Aliases: s.field, s['field'], and array_extract(s, 'field') when its first argument is a struct.

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

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(s, 'field') returns the value of the field called field, with the field's type:

  • Four spellings, one function. s.field, s['field'], struct_extract(s, 'field') and array_extract(s, 'field') return the same values. The dot form also works as (s).field, table.s.field and "s"."field".
  • Names ignore case. struct_extract({'a': 1}, 'A') and ({'A': 1}).a both return 1.
  • Any name works in the string forms, including names with a space, a dot or a non-ASCII letter: struct_extract({'a.b': 1}, 'a.b') is 1. In the dot form, quote such a name: s."a b".
  • Nested fields chain: s.a.b, or struct_extract(struct_extract(s, 'a'), 'b'). A field that is a list can be indexed: ({'a': [1, 2]}).a[2] is 2.
  • The name must be a constant and the field must exist; both are checked when the query is planned, before any row is read.
  • Unnamed fields are read by position. On a struct from row, struct_extract(row(1, 'x'), 2) and row(1, 'x')[2] return 'x'. For a named struct, use struct_extract_at.

NULLs and edge cases

Input Result
A NULL struct: struct_extract(NULL::STRUCT(a BIGINT), 'a') NULL
struct_extract(NULL, 'a') NULL
A NULL name: struct_extract({'a': 1}, NULL) NULL
struct_extract({'a': 1}, 'A'), struct_extract({'A': 1}, 'a') 1
struct_extract({'a b': 1}, 'a b'), struct_extract({'a.b': 1}, 'a.b'), struct_extract({'\u00e9': 1}, '\u00e9') 1
struct_extract(struct_extract({'a': {'b': 2}}, 'a'), 'b') 2
struct_extract(row(1, 'x'), 2) 'x'
struct_extract({'a': 1}, 'b') (a missing field) Error: Binder Error: Could not find key "b" in struct, followed by the names it has
struct_extract({'a': 1}, '') (an empty name) Error: Key name for struct_extract needs to be neither NULL nor empty

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

run("SELECT struct_extract(NULL::STRUCT(a BIGINT), 'a'), struct_extract(NULL, 'a'), struct_extract({'a': 1}, NULL)")
run("SELECT struct_extract({'a': 1}, 'A'), struct_extract({'A': 1}, 'a'), struct_extract({'a b': 1}, 'a b'), "
    "struct_extract({'a.b': 1}, 'a.b'), struct_extract({'\u00e9': 1}, '\u00e9')")
run("SELECT struct_extract(struct_extract({'a': {'b': 2}}, 'a'), 'b'), struct_extract(row(1, 'x'), 2)")
run("SELECT struct_extract({'a': 1}, 'b')")
run("SELECT struct_extract({'a': 1}, '')")

[(None, None, None)]
[(1, 1, 1, 1, 1)]
[(2, 'x')]
InternalError: Binder Error: Could not find key "b" in struct
InternalError: Binder Error: Key name for struct_extract needs to be neither NULL nor empty

A field read from a NULL struct and a NULL field both give NULL. To tell them apart, test the struct itself: st IS NULL is true for row 4 only, while st.a IS NULL is true for rows 3 and 4:

run(f"SELECT id FROM {TABLE} WHERE st IS NULL ORDER BY id LIMIT 10")
run(f"SELECT id FROM {TABLE} WHERE st.a IS NULL ORDER BY id LIMIT 10")

[(4,)]
[(3,), (4,)]

Examples

Each field, in each spelling:

run(f"SELECT id, st.a, st['b'], struct_extract(st, 'a'), array_extract(st, 'b') FROM {TABLE} ORDER BY id LIMIT 10")

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

Keep only the rows whose field a is positive:

run(f"SELECT id, st.b FROM {TABLE} WHERE st.a > 0 ORDER BY id LIMIT 10")

[(1, 'x'), (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 scan reads the field the query names, and the Query Engine returns it.
  • In WHERE, a comparison of a field with a constant is evaluated in VAST DataBase when it's written s.field or struct_extract(s, 'field'): =, > and BETWEEN are pushed down, rows that don't match never leave storage, and the plan has no FILTER step.
  • Other conditions on a field run in a FILTER step in the Query Engine: the bracket form s['field'] = ..., array_extract(s, 'field') = ..., IN (...), LIKE, IS NULL and IS NOT NULL, and a comparison of the whole struct.

The plan of st.a = 1 has no FILTER step; the same condition written with brackets has one:

plan(f"SELECT id FROM {TABLE} WHERE st.a = 1")
plan(f"SELECT id FROM {TABLE} WHERE st['a'] = 1")

┌───────────────────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│      Projections: id      │
│                           │
└───────────────────────────┘
┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│ (array_extract(st, 'a') = │
│             1)            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│            st.a           │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

  • Filter with s.field or struct_extract(s, 'field'), not with s['field'] or array_extract: only the first two are pushed down to VAST DataBase.
  • Use BETWEEN for a range of values. st.a BETWEEN 0 AND 2 is pushed down; st.a IN (1, -7) runs in a FILTER step:
plan(f"SELECT id FROM {TABLE} WHERE st.a BETWEEN 0 AND 2")
plan(f"SELECT id FROM {TABLE} WHERE st.a IN (1, -7)")

┌───────────────────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│      Projections: id      │
│                           │
└───────────────────────────┘
┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│ ((struct_extract(st, 'a') │
│ = 1) OR (struct_extract(st│
│       , 'a') = -7))       │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│            st.a           │
│             id            │
│                           │
└───────────────────────────┘
  • Select the fields you need, not the whole struct. SELECT st.b reads only that field. The plan lists what the scan reads:
plan(f"SELECT st.b FROM {TABLE}")

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

Limitations and errors

  • The field must exist. A missing name fails when the query is planned with Could not find key "..." in struct, even if no row would reach it.
  • The name must be a constant. struct_extract(st, k) with a column k, and st[k], fail with Key name for struct_extract needs to be a constant string.
  • Names on named structs, positions on unnamed ones. A position on a named struct fails with struct_extract with an integer key can only be used on unnamed structs, use a string key instead; a name on a struct from row() fails with struct_extract with a string key cannot be used on an unnamed struct, use a numeric index instead.
  • A field keeps its type in arithmetic. st.a is an INTEGER, so st.a + 1 on row 5 fails with Overflow in addition of INT32 (2147483647 + 1)!. Cast the field to BIGINT first.
  • 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 struct_extract(STRUCT, VARCHAR) and struct_extract(STRUCT, BIGINT). On a list, array_extract reads an element by position instead (list_extract).
  • Other SQL dialects' names aren't available. get_field fails with Scalar Function with name get_field does not exist!. Use struct_extract or s.field.
run(f"SELECT id, st.c FROM {TABLE} ORDER BY id LIMIT 10")
run("SELECT struct_extract({'a': 1}, 1)")
run("SELECT struct_extract(row(1, 'x'), 'a')")
run(f"SELECT id, st.a + 1 FROM {TABLE} ORDER BY id LIMIT 10")
run(f"SELECT id, CAST(st.a AS BIGINT) + 1 FROM {TABLE} ORDER BY id LIMIT 10")
run("SELECT get_field({'a': 1}, 'a')")

InternalError: Binder Error: Could not find key "c" in struct
InternalError: Binder Error: struct_extract with an integer key can only be used on unnamed structs, use a string key instead
InternalError: Binder Error: struct_extract with a string key cannot be used on an unnamed struct, use a numeric index instead
OSError: Overflow in addition of INT32 (2147483647 + 1)!
[(1, 2), (2, -6), (3, None), (4, None), (5, 2147483648)]
InternalError: Catalog Error: Scalar Function with name get_field does not exist!

Similar functions

Function Reads Result for {'a': 1, 'b': 'x'}
struct_extract(s, 'b'), s.b, s['b'], array_extract(s, 'b') a field by name 'x'
struct_extract_at(s, 2) a field by position 'x'
json_extract_string(to_json(s), '$.b') a field of the struct's JSON text 'x' 
run("SELECT struct_extract({'a': 1, 'b': 'x'}, 'b'), {'a': 1, 'b': 'x'}.b, {'a': 1, 'b': 'x'}['b'], "
    "array_extract({'a': 1, 'b': 'x'}, 'b'), struct_extract_at({'a': 1, 'b': 'x'}, 2), "
    "json_extract_string(to_json({'a': 1, 'b': 'x'}), '$.b')")

[('x', 'x', 'x', 'x', 'x', 'x')]

Clean up:

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

[]
[]

See also

Related articles
  • struct_extract_at
    Developer Guide > VAST DataBase > Reference > SQL reference > Functions > Struct functions
  • struct_pack
    Developer Guide > VAST DataBase > Reference > SQL reference > Functions > Struct functions
  • row
    Developer Guide > VAST DataBase > Reference > SQL reference > Functions > Struct functions