json_transform

Applies to VAST Cluster 5.5.

Functions › Parsing and JSON functions › Convert to SQL types. Converts a JSON document to SQL types in one pass, following a structure you give: an object becomes a STRUCT, an array a list, a value a typed value. A field that's missing or doesn't convert becomes NULL. from_json is the same function.

Syntax

json_transform(json, structure)
from_json(json, structure)

Returns: the type the structure describes: a STRUCT for an object, a list for an array, the named type for a type name.

Aliases: from_json.

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_json_json_transform. 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 events with a JSON payload stored as VARCHAR, and the same profile as a struct column. In row 2, n holds a string; row 3 holds an array; row 4 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_json_json_transform"'

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}.events"
SHAPE = '{"user": "VARCHAR", "n": "BIGINT", "tags": ["VARCHAR"]}'
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, payload VARCHAR, profile STRUCT(name VARCHAR, n BIGINT))")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, '{\"user\": \"ada\", \"n\": 3, \"tags\": [\"a\", \"b\"], \"geo\": {\"city\": \"Oslo\", \"lat\": 59.9}, \"ok\": true}', "
    "{'name': 'ada', 'n': 3}), "
    "(2, '{\"user\": \"alan\", \"n\": \"many\", \"tags\": [], \"geo\": null}', {'name': 'alan', 'n': NULL}), "
    "(3, '[10, 20, [30, 40], {\"k\": \"v\"}]', NULL), "
    "(4, NULL, NULL)")

[]
[]
[(4,)]

What it does

json_transform parses the document once and walks it with the structure:

• Object structure: a STRUCT with one field per key in the structure. Keys in the document that aren't in the structure are ignored. A key that's missing gives NULL. Keys match exactly, case included: "A" doesn't fill "a".
• Array structure: a list, each element converted to the element type.
• Type name: any SQL type name, such as VARCHAR, BIGINT, DOUBLE, DECIMAL(4,1), BOOLEAN, DATE, TIMESTAMP, INTERVAL, UUID, VARCHAR[] or MAP(VARCHAR, JSON). The type JSON keeps the value as JSON text.

Values convert when they can, and become NULL when they can't:

To fail instead of returning NULL, use json_transform_strict.

NULLs and edge cases

The cases of both tables, as the cluster returns them. Each line shows the document, the structure and the result. They use the helpers from Set up the examples:

cases = [
    ('{"n": "12"}', '{"n": "BIGINT"}'), ('{"n": 1.7}', '{"n": "BIGINT"}'), ('{"x": 2.55}', '{"x": "DECIMAL(3,1)"}'),
    ('{"b": "true"}', '{"b": "BOOLEAN"}'), ('{"b": 1}', '{"b": "BOOLEAN"}'), ('{"d": "2026-10-16"}', '{"d": "DATE"}'),
    ('{"t": "2026-10-16T12:34:56Z"}', '{"t": "TIMESTAMP"}'), ('{"a": {"b": 1}}', '{"a": "VARCHAR"}'),
    ('{"a": [1, 2]}', '{"a": "VARCHAR"}'), ('{"geo": {"city": "Oslo", "lat": 59.9}}', '{"geo": "MAP(VARCHAR, JSON)"}'),
    ('{"n": "x"}', '{"n": "BIGINT"}'), ('{"n": 300}', '{"n": "TINYINT"}'), ('{"n": -1}', '{"n": "UBIGINT"}'),
    ('{"d": "16/10/2026"}', '{"d": "DATE"}'), ('[1, "2", null, "x"]', '["BIGINT"]'),
    ('null', '{"a": "BIGINT"}'), ('{"a": 1}', '{"missing": "BIGINT"}'), ('{"A": 1}', '{"a": "BIGINT"}'),
    ('{"a": 1, "a": 2}', '{"a": "BIGINT"}'), ('[1, 2]', '{"a": "BIGINT"}'), ('42', '{"a": "BIGINT"}'),
    ('{"a": 1}', '["BIGINT"]'), ('{"a": 1}', '"BIGINT"'),
]
for doc, structure in cases:
    run(f"SELECT '{doc}', '{structure}', json_transform('{doc}', '{structure}')")
run("SELECT json_transform(NULL, '{\"a\": \"BIGINT\"}'), json_transform('{\"a\": 1}', NULL), "
    "json_transform(true, '{\"a\": \"BIGINT\"}')")
run("SELECT json_transform('{\"n\": 3}', '{\"n\": \"FOO\"}')")
run("SELECT json_transform('{\"n\": 3}', 'not a structure')")
run("SELECT json_transform('{\"n\": 3}', '{}')")
run("SELECT json_transform('not json', '{\"a\": \"BIGINT\"}')")
run("SELECT json_transform(123, '{\"a\": \"BIGINT\"}')")

[('{"n": "12"}', '{"n": "BIGINT"}', {'n': 12})]
[('{"n": 1.7}', '{"n": "BIGINT"}', {'n': 2})]
[('{"x": 2.55}', '{"x": "DECIMAL(3,1)"}', {'x': Decimal('2.6')})]
[('{"b": "true"}', '{"b": "BOOLEAN"}', {'b': True})]
[('{"b": 1}', '{"b": "BOOLEAN"}', {'b': True})]
[('{"d": "2026-10-16"}', '{"d": "DATE"}', {'d': datetime.date(2026, 10, 16)})]
[('{"t": "2026-10-16T12:34:56Z"}', '{"t": "TIMESTAMP"}', {'t': datetime.datetime(2026, 10, 16, 12, 34, 56)})]
[('{"a": {"b": 1}}', '{"a": "VARCHAR"}', {'a': '{"b":1}'})]
[('{"a": [1, 2]}', '{"a": "VARCHAR"}', {'a': '[1,2]'})]
[('{"geo": {"city": "Oslo", "lat": 59.9}}', '{"geo": "MAP(VARCHAR, JSON)"}', {'geo': [('city', '"Oslo"'), ('lat', '59.9')]})]
[('{"n": "x"}', '{"n": "BIGINT"}', {'n': None})]
[('{"n": 300}', '{"n": "TINYINT"}', {'n': None})]
[('{"n": -1}', '{"n": "UBIGINT"}', {'n': None})]
[('{"d": "16/10/2026"}', '{"d": "DATE"}', {'d': None})]
[('[1, "2", null, "x"]', '["BIGINT"]', [1, 2, None, None])]
[('null', '{"a": "BIGINT"}', None)]
[('{"a": 1}', '{"missing": "BIGINT"}', {'missing': None})]
[('{"A": 1}', '{"a": "BIGINT"}', {'a': None})]
[('{"a": 1, "a": 2}', '{"a": "BIGINT"}', {'a': 1})]
[('[1, 2]', '{"a": "BIGINT"}', {'a': None})]
[('42', '{"a": "BIGINT"}', {'a': None})]
[('{"a": 1}', '["BIGINT"]', None)]
[('{"a": 1}', '"BIGINT"', None)]
[(None, None, {'a': None})]
InternalError: Catalog Error: Type with name FOO does not exist!
InternalError: Invalid Input Error: Malformed JSON at byte 0 of input: invalid literal.  Input: not a structure
InternalError: Binder Error: Empty object in JSON structure
OSError: Malformed JSON at byte 0 of input: invalid literal.  Input: not json
InternalError: Binder Error: No function matches the given name and argument types 'json_transform(INTEGER_LITERAL, STRING_LITERAL)'. You might need to add explicit type casts.

Examples

Convert each payload to a struct with a user, a number and a list of tags. Row 2's n is a string, so it becomes NULL; row 3 is an array, so every field is NULL; row 4 is NULL. from_json gives the same result:

run(f"SELECT id, json_transform(payload, '{SHAPE}') FROM {TABLE} ORDER BY id LIMIT 10")
run(f"SELECT id, from_json(payload, '{SHAPE}') FROM {TABLE} ORDER BY id LIMIT 10")

[(1, {'user': 'ada', 'n': 3, 'tags': ['a', 'b']}), (2, {'user': 'alan', 'n': None, 'tags': []}), (3, {'user': None, 'n': None, 'tags': None}), (4, None)]
[(1, {'user': 'ada', 'n': 3, 'tags': ['a', 'b']}), (2, {'user': 'alan', 'n': None, 'tags': []}), (3, {'user': None, 'n': None, 'tags': None}), (4, None)]

The result is a struct: read a field with .name, and a list element with [i], counting from 1. typeof shows the type the structure produced:

run(f"SELECT id, json_transform(payload, '{SHAPE}').user, json_transform(payload, '{SHAPE}').tags[1] "
    f"FROM {TABLE} ORDER BY id LIMIT 10")
run(f"SELECT typeof(json_transform(payload, '{SHAPE}')) FROM {TABLE} WHERE id = 1")

[(1, 'ada', 'a'), (2, 'alan', None), (3, None, None), (4, None, None)]
[('STRUCT("user" VARCHAR, n BIGINT, tags VARCHAR[])',)]

Filter on a converted field:

run("SELECT id FROM " + TABLE + " WHERE json_transform(payload, '{\"user\": \"VARCHAR\"}').user = 'ada' "
    "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 stores the payload as text, so json_transform always runs in the Query Engine and parses the document of every row it gets:

• In the SELECT list, the scan reads the column and the Query Engine converts each row it returns.
• In WHERE, the scan sends the column for every row, and a FILTER step in the Query Engine converts each document and keeps the rows that match.

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

plan("SELECT id FROM " + TABLE + " WHERE json_transform(payload, '{\"user\": \"VARCHAR\"}').user = 'ada'")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│      (struct_extract      │
│ (json_transform(payload,  │
│  '{"user": "VARCHAR"}'),  │
│      'user') = 'ada')     │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│          payload          │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Store the converted fields. A struct column holds the same fields with their types, and a comparison of a struct field with a constant is pushed down to VAST DataBase. The plan for profile.n = 3 has no FILTER step:

plan(f"SELECT id FROM {TABLE} WHERE profile.n = 3")

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

• Narrow the rows with a pushed-down condition on another column, such as id = 1, so fewer documents reach the FILTER step.

Limitations and errors

• The structure must be a constant. A structure from a column fails with Binder Error: JSON structure must be a constant!. Write it as a literal; json_structure can draft one from a sample document.

run(f"SELECT id, json_transform(payload, payload) FROM {TABLE} ORDER BY id LIMIT 10")

InternalError: Binder Error: JSON structure must be a constant!

• NULL hides bad values. A NULL field can mean the key is missing, the value is null, or the value didn't convert. To tell them apart, use json_transform_strict, which fails on the first problem, or check the field with json_exists.
• One invalid document fails the query. If any row the function reads isn't valid JSON, the query stops with Malformed JSON at byte ...; see invalid JSON in a column.
• An empty structure, an unknown type name or a structure that isn't JSON fail the query; see NULLs and edge cases.

Similar functions

run("SELECT json_transform('{\"n\": 3}', '{\"n\": \"BIGINT\"}'), json_transform_strict('{\"n\": 3}', '{\"n\": \"BIGINT\"}'), "
    "('{\"n\": 3}' ->> '$.n')::BIGINT, TRY_CAST('{\"n\": 3}' ->> '$.n' AS BIGINT), '{\"n\": 3}'::JSON::STRUCT(n BIGINT)")
run("SELECT json_transform('{\"n\": \"x\"}', '{\"n\": \"BIGINT\"}'), TRY_CAST('{\"n\": \"x\"}' ->> '$.n' AS BIGINT)")
run("SELECT json_transform_strict('{\"n\": \"x\"}', '{\"n\": \"BIGINT\"}')")

[({'n': 3}, {'n': 3}, 3, 3, {'n': 3})]
[({'n': None}, None)]
OSError: Failed to cast value to numerical: "x"

Clean up:

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

[]
[]

See also

• json_transform_strict and json_structure.
• Parsing and JSON functions: all the JSON functions.
• List, struct and map functions: working with the struct that json_transform returns.
• Where filters run and pushdowns.