json

Applies to VAST Cluster 5.5.

Functions › Parsing and JSON functions › Build JSON. Parses JSON text and writes it back in compact form, without the spaces and line breaks between values. Invalid JSON fails with Malformed JSON, so json also checks a document as it reads it.

Syntax

json(text)

Returns: JSON. The driver returns it as text (a Python str).

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. 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: an object with extra spaces, an array, an object of numbers written in different ways, and a NULL:

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

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, doc VARCHAR)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, '  {\"user\" : \"ada\" ,  \"n\": 3, \"tags\": [\"a\", \"b\"] }  '), "
    "(2, '[10, 20, [30, 40]]'), "
    "(3, '{\"price\": 1E2, \"zero\": -0, \"big\": 12345678901234567890}'), "
    "(4, NULL)")

[]
[]
[(4,)]

What it does

json reads the whole document and writes it again:

• Spaces and line breaks between values are removed. Spaces inside strings are kept.
• Keys keep their order, and repeated keys are kept. json('{"a": 1, "a": 2}') gives {"a":1,"a":2}.
• Numbers keep their digits, including integers too large for BIGINT and values such as 1e400. Two forms change: 1E2 becomes 100.0, and -0 becomes 0.
• \u escapes become characters: "caf\u00e9" becomes "café", and a surrogate pair becomes the emoji it encodes. \u0000 and \t stay escaped.
• A scalar is a document too. '42', '"s"', 'true' and 'null' come back unchanged.

run("SELECT json('  {\"a\" : 1 }  '), json('{\"a\": 1, \"a\": 2}'), json('{\"b\": 1, \"a\": 2}')")
run("SELECT json('{\"a\": 123456789012345678901234567890}'), json('{\"a\": 1e400}'), "
    "json('{\"a\": 1E2}'), json('{\"a\": -0}')")
run("SELECT json('{\"a\": \"caf\\u00e9\"}'), json('{\"a\": \"\\u0000x\"}')")
run("SELECT json('42'), json('\"s\"'), json('true'), json('null'), typeof(json('1'))")

[('{"a":1}', '{"a":1,"a":2}', '{"b":1,"a":2}')]
[('{"a":123456789012345678901234567890}', '{"a":1e400}', '{"a":100.0}', '{"a":0}')]
[('{"a":"café"}', '{"a":"\\u0000x"}')]
[('42', '"s"', 'true', 'null', 'JSON')]

NULLs and edge cases

run("SELECT json(NULL), json('{}'), json('[]'), json(DATE '2026-10-16'), json(true), json([1, 2])")
run("SELECT json('')")
run("SELECT json('not json')")
run("SELECT json('{')")
run("SELECT json('{\"a\": \"line\nbreak\"}')")
run("SELECT json('{\"a\": \"\\ud800\"}')")
run("SELECT json(123)")

[(None, '{}', '[]', '"2026-10-16"', 'true', '[1,2]')]
OSError: Malformed JSON at byte 0 of input: input length is 0.  Input: 
OSError: Malformed JSON at byte 0 of input: invalid literal.  Input: not json
OSError: Malformed JSON at byte 1 of input: unexpected end of data.  Input: {
OSError: Malformed JSON at byte 11 of input: unexpected control character in string.  Input: {"a": "line
OSError: Malformed JSON at byte 13 of input: no low surrogate in string.  Input: {"a": "\ud800"}
InternalError: Binder Error: No function matches the given name and argument types 'json_extract(INTEGER_LITERAL, STRING_LITERAL)'. You might need to add explicit type casts.

Examples

Each document in compact form. The NULL row stays NULL:

run(f"SELECT id, json(doc) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, '{"user":"ada","n":3,"tags":["a","b"]}'), (2, '[10,20,[30,40]]'), (3, '{"price":100.0,"zero":0,"big":12345678901234567890}'), (4, None)]

A document that isn't valid JSON fails the query. Add a row that isn't JSON, then filter with json_valid to skip it:

run(f"INSERT INTO {TABLE} VALUES (5, 'not json')")
run(f"SELECT id, json(doc) FROM {TABLE} WHERE id = 5")
run(f"SELECT id, json(doc) FROM {TABLE} WHERE json_valid(doc) ORDER BY id LIMIT 10")

[(1,)]
OSError: Malformed JSON at byte 0 of input: invalid literal.  Input: not json
[(1, '{"user":"ada","n":3,"tags":["a","b"]}'), (2, '[10,20,[30,40]]'), (3, '{"price":100.0,"zero":0,"big":12345678901234567890}')]

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 JSON as text and doesn't parse it, so json is always computed by the Query Engine:

• In the SELECT list, the scan reads the whole document and the Query Engine parses it for each row it returns.
• In WHERE, a FILTER step in the Query Engine parses the document of every row the scan sends.

plan(f"SELECT id FROM {TABLE} WHERE json(doc) IS NOT NULL")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│ (json_extract(doc, '$') IS│
│          NOT NULL)        │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│            doc            │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Store documents in compact form. Run json on the documents when you write them. Then a comparison of the whole column with a compact constant, such as doc = '[10,20,[30,40]]', is evaluated in VAST DataBase, without parsing; the plan has no FILTER step:

plan(f"SELECT id FROM {TABLE} WHERE doc = '[10,20,[30,40]]'")

┌───────────────────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│      Projections: id      │
│                           │
└───────────────────────────┘

• Validate when you write. Check documents with json_valid before you insert them, so an invalid row can't stop later queries.
• Store the fields you filter on in their own columns. See Parsing and JSON functions.

Limitations and errors

• Invalid JSON fails the query with Malformed JSON at byte N of input: ..., followed by the input. Filter the rows with json_valid, or with a condition on another column.
• Numbers aren't accepted. json(123) fails with No function matches the given name and argument types 'json_extract(INTEGER_LITERAL, STRING_LITERAL)': json reads its argument with json_extract. Use to_json to turn a number into JSON.
• A BLOB isn't read as text. json('\xAA'::BLOB) fails with Malformed JSON at byte 0 of input: input length is 0. Pass the document as VARCHAR.

run("SELECT to_json(123)")
run("SELECT json('\\xAA'::BLOB)")

[('123',)]
OSError: Malformed JSON at byte 0 of input: input length is 0.  Input: 

Similar functions

run("SELECT json('{ \"a\" : [1, 2] }'), '{ \"a\" : [1, 2] }'::JSON, json_pretty('{ \"a\" : [1, 2] }')")
run("SELECT json_valid('{ \"a\" : [1, 2] }'), to_json('{ \"a\" : [1, 2] }')")
run("SELECT 'not json'::JSON")

[('{"a":[1,2]}', '{ "a" : [1, 2] }', '{\n    "a": [\n        1,\n        2\n    ]\n}')]
[(True, '"{ \\"a\\" : [1, 2] }"')]
OSError: Malformed JSON at byte 0 of input: invalid literal.  Input: not json

Clean up:

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

[]
[]

See also

• Parsing and JSON functions: all the JSON functions, and invalid JSON in a column.
• json_pretty and json_valid.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.