row_to_json

Applies to VAST Cluster 5.5.

Functions › Parsing and JSON functions › Build JSON. Converts a struct to a JSON object, with the struct's field names as keys. It takes only a struct; for other values, use to_json.

Syntax

row_to_json(struct)

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_row_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 people with a struct column. Row 2's struct has NULL fields, and row 3 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_row_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}.people"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, name VARCHAR, tags VARCHAR[], "
    "profile STRUCT(city VARCHAR, age INTEGER))")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, 'ada', ['math', 'computing'], {'city': 'London', 'age': 36}), "
    "(2, 'grace', [], {'city': NULL, 'age': NULL}), "
    "(3, NULL, NULL, NULL)")

[]
[]
[(3,)]

What it does

row_to_json writes each field of the struct as a key and a value, in the struct's field order, converting each value as to_json does:

The result is compact, with no spaces.

NULLs and edge cases

The cases of both tables, as the cluster returns them. Each line shows the argument and the result. They use the helpers from Set up the examples:

for expr in ["{'a': 1}", "{'a': {'b': [1, 2]}}", "{'a': NULL}", "{'\u00e9': 1, 'a b': 2}", "{'j': '{\"x\": 1}'::JSON}",
             "{'d': DATE '2026-10-16', 'f': 'NaN'::DOUBLE}", "NULL", "row(1, 'x')", "{'n': 1, 's': 'x'}",
             "123", "'abc'", "[1, 2]", "'{\"a\": 1}'"]:
    print(expr, end="  ")
    run(f"SELECT row_to_json({expr})")

{'a': 1}  [('{"a":1}',)]
{'a': {'b': [1, 2]}}  [('{"a":{"b":[1,2]}}',)]
{'a': NULL}  [('{"a":null}',)]
{'é': 1, 'a b': 2}  [('{"é":1,"a b":2}',)]
{'j': '{"x": 1}'::JSON}  [('{"j":{"x":1}}',)]
{'d': DATE '2026-10-16', 'f': 'NaN'::DOUBLE}  [('{"d":"2026-10-16","f":NaN}',)]
NULL  [(None,)]
row(1, 'x')  [('{"":1,"":"x"}',)]
{'n': 1, 's': 'x'}  [('{"n":1,"s":"x"}',)]
123  InternalError: Invalid Input Error: row_to_json() argument type must be STRUCT
'abc'  InternalError: Invalid Input Error: row_to_json() argument type must be STRUCT
[1, 2]  InternalError: Invalid Input Error: row_to_json() argument type must be STRUCT
'{"a": 1}'  InternalError: Invalid Input Error: row_to_json() argument type must be STRUCT

Examples

Convert the struct column. Row 2's NULL fields become null; row 3's NULL struct stays NULL:

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

[(1, '{"city":"London","age":36}'), (2, '{"city":null,"age":null}'), (3, None)]

Build one object per row from several columns, by putting them in a struct literal. The literal's names become the keys:

run(f"SELECT id, row_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":"grace","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). row_to_json runs in the Query Engine: the scan reads the columns it uses, and a projection step above it builds the JSON for each row it returns. In WHERE, a condition on row_to_json() runs in a FILTER step in the Query Engine.

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

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             id            │
│    row_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 row_to_json.
• Filter on the struct's fields, not on its JSON. A comparison of a struct field with a constant, such as profile.age = 36, can be pushed down; see struct fields.

Limitations and errors

• Structs only. Anything else, including JSON text, fails with row_to_json() argument type must be STRUCT. Use to_json for other values.
• Unnamed fields give empty keys. row(...) builds a struct without field names, and every key in the result is "". Use a struct literal with names.
• NaN and infinity aren't standard JSON; see to_json.

Similar functions

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

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

Clean up:

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

[]
[]

See also

• to_json: the conversion rules for each value type.
• List, struct and map functions: building structs.
• Parsing and JSON functions: all the JSON functions.