to_json

Applies to VAST Cluster 5.5.

Functions › Parsing and JSON functions › Build JSON. Converts a SQL value to JSON text: a number to a JSON number, a string to a quoted JSON string, a list to an array, and a struct or map to an object.

Syntax

to_json(value)

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_to_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 three users with columns of several types. The last row 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_to_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}.users"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, name VARCHAR, score DECIMAL(10,2), tags VARCHAR[], "
    "profile STRUCT(city VARCHAR, age INTEGER), joined DATE, active BOOLEAN)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, 'ada', 2.50, ['math', 'computing'], {'city': 'London', 'age': 36}, DATE '2026-10-16', true), "
    "(2, 'say \"hi\"', -7.25, [], {'city': NULL, 'age': NULL}, DATE '1970-01-01', false), "
    "(3, NULL, NULL, NULL, NULL, NULL, NULL)")

[]
[]
[(3,)]

What it does

Non-ASCII characters are written as they are, not as \u escapes, and / isn't escaped: to_json('</script>') returns "</script>". The result has no spaces between elements.

NULLs and edge cases

Every case on this page, as the cluster returns it. Each line shows the SQL value and the result. They use the helpers from Set up the examples:

for expr in ["42", "-1", "1.5", "1.50::DECIMAL(4,2)", "1e300", "'NaN'::DOUBLE", "'inf'::DOUBLE", "'-inf'::DOUBLE",
             "'a\"b\\c'", "chr(10)", "chr(1)", "''", "'\u00e9\U0001F1EB\U0001F1F7'", "'</script>'", "true",
             "DATE '2026-10-16'", "TIMESTAMP '2026-10-16 12:34:56.789'", "TIME '12:34:56'", "INTERVAL 1 DAY",
             "'00000000-0000-4000-8000-000000000001'::UUID", "[1, NULL, 3]", "[]", "{'a': 1, 'b': [1, 2]}",
             "{'a': NULL}", "MAP {'k': 'v'}", "MAP {1: 2}", "'{\"a\": 1}'", "'{\"a\": 1}'::JSON",
             "'\\xAA'::BLOB", "'101'::BIT", "NULL"]:
    print(expr, end="  ")
    run(f"SELECT to_json({expr})")

42  [('42',)]
-1  [('-1',)]
1.5  [('1.5',)]
1.50::DECIMAL(4,2)  [('1.5',)]
1e300  [('1e300',)]
'NaN'::DOUBLE  [('NaN',)]
'inf'::DOUBLE  [('Infinity',)]
'-inf'::DOUBLE  [('-Infinity',)]
'a"b\c'  [('"a\\"b\\\\c"',)]
chr(10)  [('"\\n"',)]
chr(1)  [('"\\u0001"',)]
''  [('""',)]
'é🇫🇷'  [('"é🇫🇷"',)]
'</script>'  [('"</script>"',)]
true  [('true',)]
DATE '2026-10-16'  [('"2026-10-16"',)]
TIMESTAMP '2026-10-16 12:34:56.789'  [('"2026-10-16 12:34:56.789"',)]
TIME '12:34:56'  [('"12:34:56"',)]
INTERVAL 1 DAY  [('"1 day"',)]
'00000000-0000-4000-8000-000000000001'::UUID  [('"00000000-0000-4000-8000-000000000001"',)]
[1, NULL, 3]  [('[1,null,3]',)]
[]  [('[]',)]
{'a': 1, 'b': [1, 2]}  [('{"a":1,"b":[1,2]}',)]
{'a': NULL}  [('{"a":null}',)]
MAP {'k': 'v'}  [('{"k":"v"}',)]
MAP {1: 2}  [('{"1":2}',)]
'{"a": 1}'  [('"{\\"a\\": 1}"',)]
'{"a": 1}'::JSON  [('{"a":1}',)]
'\xAA'::BLOB  [('',)]
'101'::BIT  [('',)]
NULL  [(None,)]

Examples

Convert each column of the table. NULL stays NULL:

run(f"SELECT id, to_json(name), to_json(score), to_json(tags), to_json(profile), to_json(joined), to_json(active) "
    f"FROM {TABLE} ORDER BY id LIMIT 10")

[(1, '"ada"', '2.5', '["math","computing"]', '{"city":"London","age":36}', '"2026-10-16"', 'true'), (2, '"say \\"hi\\""', '-7.25', '[]', '{"city":null,"age":null}', '"1970-01-01"', 'false'), (3, None, None, None, None, None, None)]

Build one document per row from several columns, with a struct:

run(f"SELECT id, to_json({{'name': name, 'tags': tags, 'city': profile.city}}) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, '{"name":"ada","tags":["math","computing"],"city":"London"}'), (2, '{"name":"say \\"hi\\"","tags":[],"city":null}'), (3, '{"name":null,"tags":null,"city":null}')]

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). to_json runs in the Query Engine: the scan reads the columns the function uses, and a projection step above it builds the JSON for each row it returns. In WHERE, a condition on to_json() runs in a FILTER step in the Query Engine.

plan(f"SELECT id, to_json(profile) FROM {TABLE}")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             id            │
│      to_json(profile)     │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│             id            │
│          profile          │
│                           │
└───────────────────────────┘

Make it faster

• Filter before you build. A comparison of a plain column with a constant, such as id = 1, is pushed down to VAST DataBase, so only the rows you return reach to_json.
• Filter on the columns, not on the JSON. A condition on to_json(...) runs in a FILTER step for every row the scan sends; the same condition on the column itself can be pushed down.

Limitations and errors

• Text is quoted, not parsed. A VARCHAR that holds a JSON document becomes a JSON string. To embed it as a document, cast it: to_json(payload::JSON).
• No aggregation into JSON. to_json(list(id)), which would build one array from many rows, fails with The operator 'HASH_GROUP_BY' type=9 is currently not supported., and json_group_array and json_group_object (not available) fail with The operator 'UNGROUPED_AGGREGATE' type=8 is currently not supported for this usage. Read the rows with your client and build the array there.

run(f"SELECT to_json(list(id)) FROM {TABLE}")

InternalError: The operator 'HASH_GROUP_BY' type=9 is currently not supported.

• BLOB and BIT give an empty string. Convert them to a string first, with a function from binary, hash and UUID functions.
• NaN and infinity aren't standard JSON. Some JSON readers reject them; replace them, for example with NULL, before converting.

Similar functions

run("SELECT to_json([1, 2]), json_quote([1, 2]), array_to_json([1, 2]), json_array(1, 2), "
    "to_json({'a': 1}), json_quote({'a': 1}), row_to_json({'a': 1}), json_object('a', 1)")
run("SELECT to_json('{\"a\": 1}'), json_quote('{\"a\": 1}'), json('{\"a\": 1}'), '{\"a\": 1}'::JSON, "
    "json_array('{\"a\": 1}'), json_array('{\"a\": 1}'::JSON)")
run("SELECT array_to_json({'a': 1})")
run("SELECT array_to_json('{\"a\": 1}')")
run("SELECT row_to_json([1, 2])")
run("SELECT row_to_json('{\"a\": 1}')")

[('[1,2]', '[1,2]', '[1,2]', '[1,2]', '{"a":1}', '{"a":1}', '{"a":1}', '{"a":1}')]
[('"{\\"a\\": 1}"', '"{\\"a\\": 1}"', '{"a":1}', '{"a": 1}', '["{\\"a\\": 1}"]', '[{"a":1}]')]
InternalError: Invalid Input Error: array_to_json() argument type must be LIST
InternalError: Invalid Input Error: array_to_json() argument type must be LIST
InternalError: Invalid Input Error: row_to_json() argument type must be STRUCT
InternalError: Invalid Input Error: row_to_json() argument type must be STRUCT

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.
• List, struct and map functions: building the lists and structs to convert.
• EXPLAIN: how to read a plan.