--- title: "json_transform_strict" slug: "dev-database-reference-sql-functions-json-json-transform-strict" updated: 2026-10-30T18:19:06Z published: 2026-10-30T18:19:06Z canonical: "docs.vastdata.com/dev-database-reference-sql-functions-json-json-transform-strict" --- > ## Documentation Index > Fetch the complete documentation index at: https://docs.vastdata.com/llms.txt > Use this file to discover all available pages before exploring further. # json_transform_strict *Applies to VAST Cluster 5.5.* *[Functions](/develop/docs/dev-database-reference-sql-functions-index) › [Parsing and JSON functions](/develop/docs/dev-database-reference-sql-functions-json) › Convert to SQL types.* Converts a JSON document to SQL types following a structure, like [json_transform](/develop/docs/dev-database-reference-sql-functions-json-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) ``` | Argument | Type | Description | | --- | --- | --- | | `json` | `VARCHAR` or `JSON` | The JSON document to convert. | | `structure` | `VARCHAR`, a constant | The shape and types to convert to, as for [json_transform](/develop/docs/dev-database-reference-sql-functions-json-json-transform): an object of field names and type names, an array with one element type, or one type name. | **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)](/develop/docs/dev-database-integrations-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](/develop/docs/dev-database-reference-sql-index#example-connect-and-use-a-transaction)), 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, "") 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](/develop/docs/dev-database-reference-sql-functions-json-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: | Problem | Example document and structure | Error | | --- | --- | --- | | A value that doesn't convert | `'{"n": "x"}'`, `'{"n": "BIGINT"}'` | `Failed to cast value to numerical: "x"` | | A number out of range for the type | `'{"n": 300}'`, `'{"n": "TINYINT"}'` | `Failed to cast value to numerical: 300` | | A date not in `YYYY-MM-DD` form | `'{"d": "16/10/2026"}'`, `'{"d": "DATE"}'` | `invalid date field format: "16/10/2026", expected format is (YYYY-MM-DD)` | | A missing key, or one that differs in case | `'{"A": 1}'`, `'{"a": "BIGINT"}'` | `Object {"A":1} does not have key "a"` | | A key that appears twice | `'{"a": 1, "a": 2}'`, `'{"a": "BIGINT"}'` | `Duplicate key "a" in object {"a":1,"a":2}` | | An array where the structure has an object | `'[1, 2]'`, `'{"a": "BIGINT"}'` | `Expected OBJECT, but got ARRAY: [1,2]` | | An object where the structure has an array | `'{"a": 1}'`, `'["BIGINT"]'` | `Expected ARRAY, but got OBJECT: {"a":1}` | | A list element that doesn't convert | `'[1, "2", null, "x"]'`, `'["BIGINT"]'` | `Failed to cast value to numerical: "x"` | A long document is shortened in the message, ending in `...`. ## NULLs and edge cases | Input | Result | | --- | --- | | `NULL` document or structure | `NULL` | | The document `'null'` | `NULL` | | A `BOOLEAN` or `DATE` as the document | Error: `Expected OBJECT, but got BOOLEAN: true`, `Expected OBJECT, but got VARCHAR: "2026-10-16"` | | An unknown type name, an empty structure `'{}'`, or a structure that isn't JSON | The same errors as [json_transform](/develop/docs/dev-database-reference-sql-functions-json-json-transform#nulls-and-edge-cases) | | A number as the document | Error: `No function matches the given name and argument types 'json_transform_strict(INTEGER_LITERAL, STRING_LITERAL)'` | 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](/develop/docs/dev-database-reference-sql-functions-json-json-transform-strict#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](/develop/docs/dev-database-reference-sql-functions-json-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](/develop/docs/dev-database-reference-sql-functions-json-json-transform#make-it-faster). - **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](/develop/docs/dev-database-reference-sql-functions-json-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](/develop/docs/dev-database-reference-sql-functions-json). ## Similar functions | Function | `'{"n": "x"}'` with `'{"n": "BIGINT"}'` | `'{"a": 1}'` with `'{"missing": "BIGINT"}'` | | --- | --- | --- | | `json_transform_strict` | Error: `Failed to cast value to numerical: "x"` | Error: `Object {"a":1} does not have key "missing"` | | [json_transform](/develop/docs/dev-database-reference-sql-functions-json-json-transform) | `{'n': None}` | `{'missing': None}` | | [json_valid](/develop/docs/dev-database-reference-sql-functions-json-json-valid) | `true`: it checks the JSON syntax only | `true` | ``` 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](/develop/docs/dev-database-reference-sql-functions-json-json-transform) and [json_structure](/develop/docs/dev-database-reference-sql-functions-json-json-structure). - [Parsing and JSON functions](/develop/docs/dev-database-reference-sql-functions-json): all the JSON functions. - [Where filters run](/develop/docs/dev-database-reference-sql-select#where-filters-run) and [pushdowns](/develop/docs/dev-database-reference-pushdowns).