array_to_json

Applies to VAST Cluster 5.5.

Functions › Parsing and JSON functions › Build JSON. Converts a list to a JSON array. It takes only a list; for other values, use to_json.

Syntax

array_to_json(list)

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_array_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 four rows with two list columns. Row 2 has empty lists, row 3 a NULL element, and row 4 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_array_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}.readings"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, scores BIGINT[], tags VARCHAR[])")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, [1, 2, 3], ['math', 'computing']), "
    "(2, [], []), "
    "(3, [NULL, 4], ['say \"hi\"', NULL]), "
    "(4, NULL, NULL)")

[]
[]
[(4,)]

What it does

array_to_json writes the list as a JSON array, converting each element 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 ["[1, 2, 3]", "['a', '\u00e9']", "[NULL]", "[[1, 2], [3]]", "[{'a': 1}]", "[DATE '2026-10-16']",
             "['{\"a\": 1}'::JSON]", "['{\"a\": 1}']", "NULL", "[]", "[1.5, 'NaN'::DOUBLE]",
             "123", "'abc'", "{'a': 1}", "DATE '2026-10-16'", "'[1, 2]'", "[1, 2]::INTEGER[2]"]:
    print(expr, end="  ")
    run(f"SELECT array_to_json({expr})")

[1, 2, 3]  [('[1,2,3]',)]
['a', 'é']  [('["a","é"]',)]
[NULL]  [('[null]',)]
[[1, 2], [3]]  [('[[1,2],[3]]',)]
[{'a': 1}]  [('[{"a":1}]',)]
[DATE '2026-10-16']  [('["2026-10-16"]',)]
['{"a": 1}'::JSON]  [('[{"a":1}]',)]
['{"a": 1}']  [('["{\\"a\\": 1}"]',)]
NULL  [(None,)]
[]  [('[]',)]
[1.5, 'NaN'::DOUBLE]  [('[1.5,NaN]',)]
123  InternalError: Invalid Input Error: array_to_json() argument type must be LIST
'abc'  InternalError: Invalid Input Error: array_to_json() argument type must be LIST
{'a': 1}  InternalError: Invalid Input Error: array_to_json() argument type must be LIST
DATE '2026-10-16'  InternalError: Invalid Input Error: array_to_json() argument type must be LIST
'[1, 2]'  InternalError: Invalid Input Error: array_to_json() argument type must be LIST
[1, 2]::INTEGER[2]  InternalError: Invalid Input Error: array_to_json() argument type must be LIST

Examples

Convert both list columns. Row 3's NULL elements become null, and the double quotes are escaped:

run(f"SELECT id, array_to_json(scores), array_to_json(tags) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, '[1,2,3]', '["math","computing"]'), (2, '[]', '[]'), (3, '[null,4]', '["say \\"hi\\"",null]'), (4, None, None)]

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

plan(f"SELECT id, array_to_json(scores) FROM {TABLE}")

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

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 array_to_json.
• Filter on the list, not on its JSON. List functions work on the list column directly, with no conversion to text.

Limitations and errors

• Lists only. Anything else, including JSON text and fixed-size arrays, fails with array_to_json() argument type must be LIST. Use to_json for other values.
• No aggregation into an array. array_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. Read the rows with your client and build the array there.

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

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

Similar functions

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

[('[1,2]', '[1,2]', '[1,2]', '[1,2]', '{"a":1}', '{"a":1}', '{"a":1}')]
InternalError: Invalid Input Error: array_to_json() argument type must be LIST
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

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