json_transform_strict

Applies to VAST Cluster 5.5.

Functions › Parsing and JSON functions › Convert to SQL types. Converts a JSON document to SQL types following a structure, like json_transform, but fails the query when a key is missing, a key appears twice, or a value doesn't convert, instead of returning NULL. from_json_strict is the same function.

Syntax

json_transform_strict(json, structure)
from_json_strict(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_strict.

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_strict. 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. In row 2, n holds a string; row 4 has no payload:

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

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)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, '{\"user\": \"ada\", \"n\": 3, \"tags\": [\"a\", \"b\"]}'), "
    "(2, '{\"user\": \"alan\", \"n\": \"many\", \"tags\": []}'), "
    "(3, '{\"user\": \"grace\", \"n\": 7, \"tags\": [\"c\"]}'), "
    "(4, NULL)")

[]
[]
[(4,)]

What it does

json_transform_strict reads the document with the structure exactly as json_transform does, and converts the same values the same way: "12" to the BIGINT 12, 1.7 to 2, 1 to true, an object to its JSON text for a VARCHAR field. Where json_transform would return NULL, it stops the query with an error instead:

A long document is shortened in the message, ending in ....

NULLs and edge cases

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

cases = [
    ('{"n": "12"}', '{"n": "BIGINT"}'), ('{"n": 1.7}', '{"n": "BIGINT"}'), ('{"b": 1}', '{"b": "BOOLEAN"}'),
    ('{"a": {"b": 1}}', '{"a": "VARCHAR"}'), ('null', '{"a": "BIGINT"}'),
    ('{"n": "x"}', '{"n": "BIGINT"}'), ('{"n": 300}', '{"n": "TINYINT"}'), ('{"d": "16/10/2026"}', '{"d": "DATE"}'),
    ('{"A": 1}', '{"a": "BIGINT"}'), ('{"a": 1, "a": 2}', '{"a": "BIGINT"}'), ('[1, 2]', '{"a": "BIGINT"}'),
    ('{"a": 1}', '["BIGINT"]'), ('[1, "2", null, "x"]', '["BIGINT"]'),
]
for doc, structure in cases:
    print(doc, structure, end="  ")
    run(f"SELECT json_transform_strict('{doc}', '{structure}')")
run("SELECT json_transform_strict(NULL, '{\"a\": \"BIGINT\"}'), json_transform_strict('{\"a\": 1}', NULL)")
run("SELECT json_transform_strict(true, '{\"a\": \"BIGINT\"}')")
run("SELECT json_transform_strict(DATE '2026-10-16', '{\"a\": \"BIGINT\"}')")
run("SELECT json_transform_strict(123, '{\"a\": \"BIGINT\"}')")

{"n": "12"} {"n": "BIGINT"}  [({'n': 12},)]
{"n": 1.7} {"n": "BIGINT"}  [({'n': 2},)]
{"b": 1} {"b": "BOOLEAN"}  [({'b': True},)]
{"a": {"b": 1}} {"a": "VARCHAR"}  [({'a': '{"b":1}'},)]
null {"a": "BIGINT"}  [(None,)]
{"n": "x"} {"n": "BIGINT"}  OSError: Failed to cast value to numerical: "x"
{"n": 300} {"n": "TINYINT"}  OSError: Failed to cast value to numerical: 300
{"d": "16/10/2026"} {"d": "DATE"}  OSError: invalid date field format: "16/10/2026", expected format is (YYYY-MM-DD)
{"A": 1} {"a": "BIGINT"}  OSError: Object {"A":1} does not have key "a"
{"a": 1, "a": 2} {"a": "BIGINT"}  OSError: Duplicate key "a" in object {"a":1,"a":2}
[1, 2] {"a": "BIGINT"}  OSError: Expected OBJECT, but got ARRAY: [1,2]
{"a": 1} ["BIGINT"]  OSError: Expected ARRAY, but got OBJECT: {"a":1}
[1, "2", null, "x"] ["BIGINT"]  OSError: Failed to cast value to numerical: "x"
[(None, None)]
OSError: Expected OBJECT, but got BOOLEAN: true
OSError: Expected OBJECT, but got VARCHAR: "2026-10-16"
InternalError: Binder Error: No function matches the given name and argument types 'json_transform_strict(INTEGER_LITERAL, STRING_LITERAL)'. You might need to add explicit type casts.

Examples

On the whole table, row 2's "many" stops the query. json_transform returns NULL for that field instead. from_json_strict gives the same result as json_transform_strict:

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

OSError: Failed to cast value to numerical: "many"
OSError: Failed to cast value to numerical: "many"
[(1, {'user': 'ada', 'n': 3, 'tags': ['a', 'b']}), (2, {'user': 'alan', 'n': None, 'tags': []}), (3, {'user': 'grace', 'n': 7, 'tags': ['c']}), (4, None)]

A condition on id is pushed down to VAST DataBase, so the rows it excludes never reach the function. On row 1 alone, the conversion succeeds:

run(f"SELECT id, json_transform_strict(payload, '{SHAPE}') FROM {TABLE} WHERE id = 1")

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

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_strict 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. The first row that fails stops the query.
• In WHERE, the scan sends the column for every row, and a FILTER step in the Query Engine converts each document. A row that fails to convert stops the query, even one that the condition wouldn't have kept: here, row 2 fails although the condition asks for n = 3:

run("SELECT id FROM " + TABLE + " WHERE json_transform_strict(payload, '{\"n\": \"BIGINT\"}').n = 3 "
    "ORDER BY id LIMIT 10")
plan("SELECT id FROM " + TABLE + " WHERE json_transform_strict(payload, '{\"n\": \"BIGINT\"}').n = 3")

OSError: Failed to cast value to numerical: "many"
┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│      (struct_extract      │
│   (json_transform_strict  │
│ (payload, '{"n": "BIGINT"}│
│       '), 'n') = 3)       │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│          payload          │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Validate when you write, convert when you read. Run json_transform_strict on new documents before you insert them, so a bad document is caught once instead of stopping later queries.
• Store the converted fields in their own columns or a struct column. A comparison of a column or a struct field with a constant is pushed down to VAST DataBase; see json_transform.
• Narrow the rows first with a pushed-down condition on another column, as in the id = 1 example: fewer documents reach the function.

Limitations and errors

• One bad value fails the whole query. Use json_transform_strict to check documents, and json_transform when a NULL for a bad value is acceptable.
• The structure must be a constant. A structure from a column fails with Binder Error: JSON structure must be a constant!.
• One invalid document fails the query with Malformed JSON at byte ..., as for every JSON function; see invalid JSON in a column.

Similar functions

for doc, structure in [('{"n": "x"}', '{"n": "BIGINT"}'), ('{"a": 1}', '{"missing": "BIGINT"}')]:
    run(f"SELECT json_transform('{doc}', '{structure}'), json_valid('{doc}')")
    run(f"SELECT json_transform_strict('{doc}', '{structure}')")

[({'n': None}, True)]
OSError: Failed to cast value to numerical: "x"
[({'missing': None}, True)]
OSError: Object {"a":1} does not have key "missing"

Clean up:

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

[]
[]

See also

• json_transform and json_structure.
• Parsing and JSON functions: all the JSON functions.
• Where filters run and pushdowns.