--- title: "json_transform" slug: "dev-database-reference-sql-functions-json-json-transform" updated: 2026-10-30T18:19:06Z published: 2026-10-30T18:19:06Z canonical: "docs.vastdata.com/dev-database-reference-sql-functions-json-json-transform" --- > ## 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 *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 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) ``` | Argument | Type | Description | | --- | --- | --- | | `json` | `VARCHAR` or `JSON` | The JSON document to convert. | | `structure` | `VARCHAR`, a constant | The shape and types to convert to, written as JSON: an object of field names and type names (`'{"user": "VARCHAR", "n": "BIGINT"}'`), an array with one element type (`'["BIGINT"]'`), or one type name (`'"BIGINT"'`). Objects and arrays nest. | **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)](/develop/docs/dev-database-integrations-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](/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`, 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, "") 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: | Value | Target | Result | | --- | --- | --- | | `"12"` (a string of digits) | `BIGINT` | `12` | | `1.7` | `BIGINT` | `2` (rounded) | | `2.55` | `DECIMAL(3,1)` | `2.6` | | `"true"` or `1` | `BOOLEAN` | `true` | | `"2026-10-16"` | `DATE` | 2026-10-16 | | `"2026-10-16T12:34:56Z"` | `TIMESTAMP` | 2026-10-16 12:34:56 | | An object or array | `VARCHAR` | its JSON text, compact: `'{"b":1}'`, `'[1,2]'` | | An object | `MAP(VARCHAR, JSON)` | a map of key to JSON text | | `"x"` or `"many"` | `BIGINT` | `NULL` | | `300` | `TINYINT` | `NULL` (out of range) | | `-1` | `UBIGINT` | `NULL` | | `"16/10/2026"` | `DATE` | `NULL` (not `YYYY-MM-DD`) | | `[1, "2", null, "x"]` | `["BIGINT"]` | `[1, 2, None, None]` | To fail instead of returning `NULL`, use [json_transform_strict](/develop/docs/dev-database-reference-sql-functions-json-json-transform-strict). ## NULLs and edge cases | Input | Result | | --- | --- | | `NULL` document or structure | `NULL` | | The document `'null'` | `NULL` | | A key the document doesn't have | `NULL` for that field | | A key that appears twice, `'{"a": 1, "a": 2}'` | The first value, `1` | | An array, number or string where the structure has an object | A struct whose fields are all `NULL`: `{'a': None}` | | An object where the structure has an array or a type name | `NULL` | | A `DATE`, `BOOLEAN` or list as the document | A struct whose fields are all `NULL` | | An unknown type name, `'{"n": "FOO"}'` | Error: `Catalog Error: Type with name FOO does not exist!` | | A structure that isn't JSON | Error: `Invalid Input Error: Malformed JSON at byte 0 of input: invalid literal. Input: not a structure` | | An empty structure, `'{}'` | Error: `Binder Error: Empty object in JSON structure` | | A document that isn't JSON | Error: `Malformed JSON at byte 0 of input: invalid literal. Input: not json` | | A number as the document | Error: `No function matches the given name and argument types 'json_transform(INTEGER_LITERAL, STRING_LITERAL)'` | 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](/develop/docs/dev-database-reference-sql-functions-json-json-transform#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](/develop/docs/dev-database-reference-sql-functions-json-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](/develop/docs/dev-database-reference-sql-functions-json-json-transform-strict), which fails on the first problem, or check the field with [json_exists](/develop/docs/dev-database-reference-sql-functions-json-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](/develop/docs/dev-database-reference-sql-functions-json). - **An empty structure, an unknown type name or a structure that isn't JSON** fail the query; see [NULLs and edge cases](/develop/docs/dev-database-reference-sql-functions-json-json-transform#nulls-and-edge-cases). ## Similar functions | Function | Result for `{"n": 3}` | Result for `{"n": "x"}` | | --- | --- | --- | | `json_transform(doc, '{"n": "BIGINT"}')` | `{'n': 3}` | `{'n': None}` | | [json_transform_strict](/develop/docs/dev-database-reference-sql-functions-json-json-transform-strict)`(doc, '{"n": "BIGINT"}')` | `{'n': 3}` | Error: `Failed to cast value to numerical: "x"` | | `(doc ->> '$.n')::BIGINT` ([json_extract_string](/develop/docs/dev-database-reference-sql-functions-json-json-extract-string) and a cast) | `3` | (not shown) | | [TRY_CAST](/develop/docs/dev-database-reference-sql-functions-conditional-and-cast-try-cast)`(doc ->> '$.n' AS BIGINT)` | `3` | `None` | | `doc::JSON::STRUCT(n BIGINT)` (a cast) | `{'n': 3}` | (not shown) | ``` 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](/develop/docs/dev-database-reference-sql-functions-json-json-transform-strict) 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. - [List, struct and map functions](/develop/docs/dev-database-reference-sql-functions-lists-and-structs): working with the struct that `json_transform` returns. - [Where filters run](/develop/docs/dev-database-reference-sql-select#where-filters-run) and [pushdowns](/develop/docs/dev-database-reference-pushdowns).