json_pretty

Applies to VAST Cluster 5.5.

Functions › Parsing and JSON functions › Build JSON. Writes a JSON document for people to read: each member and element on its own line, indented by four spaces per level. The result is VARCHAR.

Syntax

json_pretty(json)

Returns: VARCHAR, with \n line breaks.

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_pretty. 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: a nested object, an array, an object of empty values, 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_pretty"'

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\", \"tags\": [\"a\", \"b\"], \"geo\": {\"city\": \"Oslo\", \"lat\": 59.9}}'), "
    "(2, '[10, 20, [30, 40]]'), "
    "(3, '{\"empty\": {}, \"none\": []}'), "
    "(4, NULL)")

[]
[]
[(4,)]

What it does

json_pretty parses the document and writes it again with one member or element per line:

• Indentation is four spaces per level of nesting.
• An empty object or array stays on one line: {} and [].
• A scalar document is unchanged: '42', '"s"', 'true' and 'null'.
• Keys keep their order, and repeated keys are kept, as with json. \u escapes become characters.

run() prints the line breaks as \n. To see the layout, print the value itself:

with conn.cursor() as cur:
    cur.execute(f"SELECT json_pretty(doc) FROM {TABLE} WHERE id = 1")
    print(cur.fetchall()[0][0])

{
    "user": "ada",
    "tags": [
        "a",
        "b"
    ],
    "geo": {
        "city": "Oslo",
        "lat": 59.9
    }
}

NULLs and edge cases

run("SELECT json_pretty(NULL), json_pretty('{}'), json_pretty('[]'), json_pretty('42'), "
    "json_pretty('\"s\"'), json_pretty('null')")
run("SELECT json_pretty('{\"a\": 1, \"a\": 2}'), json_pretty(DATE '2026-10-16'), json_pretty(true), "
    "json_pretty([1, 2])")
run("SELECT json_pretty('')")
run("SELECT json_pretty('not json')")
run("SELECT json_pretty(123)")

[(None, '{}', '[]', '42', '"s"', 'null')]
[('{\n    "a": 1,\n    "a": 2\n}', '"2026-10-16"', 'true', '[\n    1,\n    2\n]')]
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
InternalError: Binder Error: No function matches the given name and argument types 'json_pretty(INTEGER_LITERAL)'. You might need to add explicit type casts.

Examples

Every document, as run() prints it. Row 3 keeps {} and [] on one line, and the NULL row stays NULL:

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

[(1, '{\n    "user": "ada",\n    "tags": [\n        "a",\n        "b"\n    ],\n    "geo": {\n        "city": "Oslo",\n        "lat": 59.9\n    }\n}'), (2, '[\n    10,\n    20,\n    [\n        30,\n        40\n    ]\n]'), (3, '{\n    "empty": {},\n    "none": []\n}'), (4, None)]

The result is VARCHAR, not JSON:

run("SELECT typeof(json_pretty('{}')), typeof(json('{}'))")

[('VARCHAR', 'JSON')]

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_pretty is always computed by the Query Engine:

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

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

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│  (json_pretty(CAST(doc AS │
│     JSON)) IS NOT NULL)   │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│            doc            │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Format only the rows you read. Use json_pretty in the SELECT list, and select the rows with a condition on a column, such as id = 1, which VAST DataBase evaluates. The plan has no FILTER step:

plan(f"SELECT json_pretty(doc) FROM {TABLE} WHERE id = 1")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│  json_pretty(CAST(doc AS  │
│           JSON))          │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│      Projections: doc     │
│                           │
└───────────────────────────┘

• Don't store or compare formatted JSON. The formatted text is longer than the compact form from json. Format documents when you show them.

Limitations and errors

• Invalid JSON fails the query with Malformed JSON at byte N of input: .... Filter the rows with json_valid first.
• Numbers aren't accepted. json_pretty(123) fails with No function matches the given name and argument types 'json_pretty(INTEGER_LITERAL)'. Convert the value with to_json first.
• A BLOB isn't read as text. json_pretty('\xAA'::BLOB) fails with Malformed JSON at byte 0 of input: input length is 0. Pass the document as VARCHAR.
• The indentation is fixed at four spaces.

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

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

Similar functions

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

[('{\n    "a": [\n        1,\n        2\n    ]\n}', '{"a":[1,2]}', '{ "a" : [1, 2] }')]

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.
• json and json_valid.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.