--- title: "json_structure" slug: "dev-database-reference-sql-functions-json-json-structure" updated: 2026-10-30T18:19:06Z published: 2026-10-30T18:19:06Z canonical: "docs.vastdata.com/dev-database-reference-sql-functions-json-json-structure" --- > ## 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_structure *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) › Check and inspect.* Returns the shape of a JSON document as JSON: the same objects and arrays, with the SQL type of each value in place of the value. The result is in the form that [json_transform](/develop/docs/dev-database-reference-sql-functions-json-json-transform) takes as its structure. ## Syntax ``` json_structure(json) ``` | Argument | Type | Description | | --- | --- | --- | | `json` | `VARCHAR` or `JSON` | The JSON document to describe. | **Returns:** `JSON` (text; the driver returns it as a string). ## 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_structure`. 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 JSON documents stored as `VARCHAR`: two objects, an array and a row with no value: ``` 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_structure"' 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}.docs" 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\"], \"geo\": {\"city\": \"Oslo\", \"lat\": 59.9}, \"ok\": true}'), " "(2, '{\"user\": \"alan\", \"n\": \"many\", \"tags\": [], \"geo\": null}'), " "(3, '[10, 20, [30, 40], {\"k\": \"v\"}]'), " "(4, NULL)") ``` ``` [] [] [(4,)] ``` ## What it does `json_structure` parses the document and keeps its objects and arrays. Each value is replaced by a type name: | JSON value | Type name | Example | Result | | --- | --- | --- | --- | | A string | `"VARCHAR"` | `'"s"'` | `"VARCHAR"` | | An integer of 0 or more | `"UBIGINT"` | `'42'`, `'{"a": 12345678901234567890}'` | `"UBIGINT"`, `{"a":"UBIGINT"}` | | A negative integer, or `-0` | `"BIGINT"` | `'{"a": -0}'` | `{"a":"BIGINT"}` | | A number with a decimal point or an exponent, or an integer too large for `UBIGINT` | `"DOUBLE"` | `'{"a": 1.0}'`, `'{"a": 1E2}'`, `'{"a": 123456789012345678901234567890}'` | `{"a":"DOUBLE"}` | | `true` | `"BOOLEAN"` | `'true'` | `"BOOLEAN"` | | `null` | `"NULL"` | `'null'` | `"NULL"` | | An empty object | `"JSON"` | `'{}'` | `"JSON"` | | An array | its element type, in brackets | `'[1, 2]'`, `'[]'` | `["UBIGINT"]`, `["NULL"]` | An array has one element type. When the elements differ, `json_structure` combines them: | Array | Result | | --- | --- | | `[1, 2.5]` (integer and decimal) | `["DOUBLE"]` | | `[1, -1]` | `["BIGINT"]` | | `[null, 1]` | `["UBIGINT"]`: `null` elements don't count | | `[1, "a", 2.5]` (types that don't combine) | `["JSON"]` | | `[{"a": 1}, {"b": "x"}]` | `[{"a":"UBIGINT","b":"VARCHAR"}]`: the keys of all the objects | | `[{"a": 1}, {"a": "x"}]` | `[{"a":"JSON"}]` | The result is compact (no spaces), and a key that appears twice in an object appears once. ## NULLs and edge cases | Input | Result | | --- | --- | | `NULL` | `NULL` | | `'{"a": 1, "a": 2}'` (a duplicate key) | `{"a":"UBIGINT"}` | | `' {"a" : 1 } '` (extra spaces) | `{"a":"UBIGINT"}` | | A `DATE`, a `BOOLEAN` or a list, such as `json_structure([1, 2])` | Accepted: `"VARCHAR"`, `"BOOLEAN"`, `["UBIGINT"]` | | Text that isn't JSON | Error: `Malformed JSON at byte 0 of input: invalid literal. Input: not json` | | `''` (empty string) | Error: `Malformed JSON at byte 0 of input: input length is 0. Input:` | | A number | Error: `No function matches the given name and argument types 'json_structure(INTEGER_LITERAL)'` | The cases of both tables, as the cluster returns them. Each line shows the document and its structure. They use the helpers from [Set up the examples](/develop/docs/dev-database-reference-sql-functions-json-json-structure#set-up-the-examples): ``` for doc in ['"s"', '42', '{"a": 12345678901234567890}', '{"a": -0}', '{"a": 1.0}', '{"a": 1E2}', '{"a": 123456789012345678901234567890}', 'true', 'null', '{}', '[1, 2]', '[]', '[1, 2.5]', '[1, -1]', '[null, 1]', '[1, "a", 2.5]', '[{"a": 1}, {"b": "x"}]', '[{"a": 1}, {"a": "x"}]', '{"a": 1, "a": 2}', ' {"a" : 1 } ']: run(f"SELECT '{doc}', json_structure('{doc}')") run("SELECT json_structure(NULL), json_structure(DATE '2026-10-16'), json_structure(true), json_structure([1, 2])") run("SELECT json_structure('not json')") run("SELECT json_structure('')") run("SELECT json_structure(123)") ``` ``` [('"s"', '"VARCHAR"')] [('42', '"UBIGINT"')] [('{"a": 12345678901234567890}', '{"a":"UBIGINT"}')] [('{"a": -0}', '{"a":"BIGINT"}')] [('{"a": 1.0}', '{"a":"DOUBLE"}')] [('{"a": 1E2}', '{"a":"DOUBLE"}')] [('{"a": 123456789012345678901234567890}', '{"a":"DOUBLE"}')] [('true', '"BOOLEAN"')] [('null', '"NULL"')] [('{}', '"JSON"')] [('[1, 2]', '["UBIGINT"]')] [('[]', '["NULL"]')] [('[1, 2.5]', '["DOUBLE"]')] [('[1, -1]', '["BIGINT"]')] [('[null, 1]', '["UBIGINT"]')] [('[1, "a", 2.5]', '["JSON"]')] [('[{"a": 1}, {"b": "x"}]', '[{"a":"UBIGINT","b":"VARCHAR"}]')] [('[{"a": 1}, {"a": "x"}]', '[{"a":"JSON"}]')] [('{"a": 1, "a": 2}', '{"a":"UBIGINT"}')] [(' {"a" : 1 } ', '{"a":"UBIGINT"}')] [(None, '"VARCHAR"', '"BOOLEAN"', '["UBIGINT"]')] OSError: Malformed JSON at byte 0 of input: invalid literal. Input: not json OSError: Malformed JSON at byte 0 of input: input length is 0. Input: InternalError: Binder Error: No function matches the given name and argument types 'json_structure(INTEGER_LITERAL)'. You might need to add explicit type casts. ``` ## Examples The structure of each document. In row 1, `n` is `"UBIGINT"`; in row 2 it holds a string, so it's `"VARCHAR"`, and the empty `tags` array is `["NULL"]`. Row 3 is an array of numbers, arrays and an object, so its elements are `"JSON"`: ``` run(f"SELECT id, json_structure(payload) FROM {TABLE} ORDER BY id LIMIT 10") ``` ``` [(1, '{"user":"VARCHAR","n":"UBIGINT","tags":["VARCHAR"],"geo":{"city":"VARCHAR","lat":"DOUBLE"},"ok":"BOOLEAN"}'), (2, '{"user":"VARCHAR","n":"VARCHAR","tags":["NULL"],"geo":"NULL"}'), (3, '["JSON"]'), (4, None)] ``` Use a structure as the second argument of [json_transform](/develop/docs/dev-database-reference-sql-functions-json-json-transform), which needs a constant: run `json_structure` on a sample document, then pass the result, or a version you've edited, to `json_transform`. Passing the structure straight in works for a constant document: ``` SAMPLE = '{"user": "ada", "n": 3, "tags": ["a", "b"], "geo": {"city": "Oslo", "lat": 59.9}}' run(f"SELECT json_structure('{SAMPLE}')") run(f"SELECT json_transform('{SAMPLE}', json_structure('{SAMPLE}'))") ``` ``` [('{"user":"VARCHAR","n":"UBIGINT","tags":["VARCHAR"],"geo":{"city":"VARCHAR","lat":"DOUBLE"}}',)] [({'user': 'ada', 'n': 3, 'tags': ['a', 'b'], 'geo': {'city': 'Oslo', 'lat': 59.9}},)] ``` ## 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 document as text, so `json_structure` 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 computes the structure of each row it returns. - **In `WHERE`,** the scan sends the column for every row, and a `FILTER` step in the Query Engine computes the structure and keeps the rows that match. The plan of a `WHERE` condition on the structure shows the `FILTER` step above the scan: ``` plan(f"SELECT id FROM {TABLE} WHERE json_structure(payload) IS NOT NULL") ``` ``` ┌───────────────────────────┐ │ PROJECTION │ │ ──────────────────── │ │ #1 │ │ │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ FILTER │ │ ──────────────────── │ │ (json_structure(payload) │ │ IS NOT NULL) │ │ │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ VAST_SCAN │ │ ──────────────────── │ │ Function: VAST_SCAN │ │ │ │ Projections: │ │ payload │ │ id │ │ │ └───────────────────────────┘ ``` ## Make it faster - **Describe a sample, not the table.** The structure of a few documents is usually enough to write a `json_transform` structure. Select them with a pushed-down condition on another column, such as `id = 1`, so the scan returns only those rows. - **Convert once.** If you query the same fields often, convert the documents with [json_transform](/develop/docs/dev-database-reference-sql-functions-json-json-transform) when you write them, and store the fields in their own columns or a [struct column](/develop/docs/dev-database-reference-sql-functions-lists-and-structs). ## Limitations and errors - **One document at a time.** `json_group_structure`, which would combine the structures of many rows, isn't available: it fails with `The operator 'UNGROUPED_AGGREGATE' type=8 is currently not supported for this usage.` Compare the structures of a few rows instead. ``` run(f"SELECT json_group_structure(payload) FROM {TABLE}") ``` ``` InternalError: The operator 'UNGROUPED_AGGREGATE' type=8 is currently not supported for this usage. ``` - **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). - **Integers of 0 or more are `UBIGINT`.** A field that can be negative in other documents needs `BIGINT` in a `json_transform` structure: `json_transform` returns `NULL` for a negative number in a `UBIGINT` field. ## Similar functions | Function | Returns | Result for row 1 | | --- | --- | --- | | [json_type](/develop/docs/dev-database-reference-sql-functions-json-json-type) | the type of the top-level value: `OBJECT`, `ARRAY`, … | `OBJECT` | | [json_keys](/develop/docs/dev-database-reference-sql-functions-json-json-keys) | the top-level keys, as a list | `['user', 'n', 'tags', 'geo', 'ok']` | | `json_structure` | the shape and the type of every value | `{"user":"VARCHAR","n":"UBIGINT",…}` | | [typeof](/develop/docs/dev-database-reference-sql-functions-conditional-and-cast-typeof) | the SQL type of the value itself | `VARCHAR` for this column | ``` run(f"SELECT id, json_type(payload), json_keys(payload), json_structure(payload), typeof(payload) " f"FROM {TABLE} ORDER BY id LIMIT 10") ``` ``` [(1, 'OBJECT', ['user', 'n', 'tags', 'geo', 'ok'], '{"user":"VARCHAR","n":"UBIGINT","tags":["VARCHAR"],"geo":{"city":"VARCHAR","lat":"DOUBLE"},"ok":"BOOLEAN"}', 'VARCHAR'), (2, 'OBJECT', ['user', 'n', 'tags', 'geo'], '{"user":"VARCHAR","n":"VARCHAR","tags":["NULL"],"geo":"NULL"}', 'VARCHAR'), (3, 'ARRAY', [], '["JSON"]', 'VARCHAR'), (4, None, None, None, 'VARCHAR')] ``` 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): convert a document to SQL types with a structure. - [Parsing and JSON functions](/develop/docs/dev-database-reference-sql-functions-json): all the JSON functions. - [EXPLAIN](/develop/docs/dev-database-reference-sql-explain): how to read a plan.