json_structure

Applies to VAST Cluster 5.5.

Functions › Parsing and JSON functions › 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 takes as its structure.

Syntax

json_structure(json)

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), 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), 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, "<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:

An array has one element type. When the elements differ, json_structure combines them:

The result is compact (no spaces), and a key that appears twice in an object appears once.

NULLs and edge cases

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:

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, 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 when you write them, and store the fields in their own columns or a struct column.

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.
• 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

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: convert a document to SQL types with a structure.
• Parsing and JSON functions: all the JSON functions.
• EXPLAIN: how to read a plan.