json_array

Applies to VAST Cluster 5.5.

Functions › Parsing and JSON functions › Build JSON. Builds a JSON array from its arguments: one element per argument, in the order given. The arguments can be of any type, and a NULL becomes null.

Syntax

json_array(value, ...)
json_array()

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_array. 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 people. The second name holds double quotes, the third a backslash, and 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_json_array"'

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, n BIGINT, tags VARCHAR[], joined DATE)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, 'ada', 3, ['a', 'b'], DATE '2026-10-16'), "
    "(2, 'say \"hi\"', 0, [], DATE '2026-01-01'), "
    "(3, 'C:\\temp', -1, ['c'], DATE '2024-02-29'), "
    "(4, NULL, NULL, NULL, NULL)")

[]
[]
[(4,)]

What it does

json_array writes each argument as one JSON element:

Text that holds JSON is still text: json_array('{"a": 1}') gives an array holding one string, ["{\"a\": 1}"]. Cast it to JSON to embed the document instead.

The same cases, as the cluster returns them. They use the helpers from Set up the examples:

run("SELECT json_array(1, 1.5), json_array('a'), json_array(DATE '2026-10-16'), json_array(true)")
run("SELECT json_array([1, 2]), json_array({'a': 1}), json_array(NULL)")
run("SELECT json_array('{\"a\": 1}'::JSON), json_array('{\"a\": 1}')")

[('[1,1.5]', '["a"]', '["2026-10-16"]', '[true]')]
[('[[1,2]]', '[{"a":1}]', '[null]')]
[('[{"a":1}]', '["{\\"a\\": 1}"]')]

NULLs and edge cases

run("SELECT json_array(), json_array(NULL, NULL), json_array(json_array(1), json_array())")
run("SELECT json_array('a\"b\\c', 'tab\there', chr(1), '</script>')")

[('[]', '[null,null]', '[[1],[]]')]
[('["a\\"b\\\\c","tab\\there","\\u0001","</script>"]',)]

Examples

One array per row, built from three columns. The quotes in row 2 and the backslash in row 3 are escaped, and the empty row gives [4,null,null]:

run(f"SELECT id, json_array(id, name, n) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, '[1,"ada",3]'), (2, '[2,"say \\"hi\\"",0]'), (3, '[3,"C:\\\\temp",-1]'), (4, '[4,null,null]')]

A list column becomes a nested array and a date a quoted string:

run(f"SELECT id, json_array(name, tags, joined) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, '["ada",["a","b"],"2026-10-16"]'), (2, '["say \\"hi\\"",[],"2026-01-01"]'), (3, '["C:\\\\temp",["c"],"2024-02-29"]'), (4, '[null,null,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). json_array is always computed by the Query Engine:

• In the SELECT list, the scan reads the columns and the Query Engine builds the array for each row it returns.
• In WHERE, a FILTER step in the Query Engine builds the array for every row the scan sends. Because the result is never NULL, json_array(...) IS NOT NULL keeps every row.

plan(f"SELECT id FROM {TABLE} WHERE json_array(n, name) IS NOT NULL")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #2            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│  (json_array(n, name) IS  │
│          NOT NULL)        │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│             n             │
│            name           │
│             id            │
│                           │
└───────────────────────────┘

Make it faster

• Filter on the columns, not on the array. A comparison of a column with a constant, such as id = 2, is evaluated in VAST DataBase, so only the matching rows reach the Query Engine to be turned into JSON. The plan has no FILTER step:

plan(f"SELECT json_array(id, name) FROM {TABLE} WHERE id = 2")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│    json_array(id, name)   │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│             id            │
│            name           │
│                           │
└───────────────────────────┘

• Build only what you return. Each json_array call in the SELECT list is computed for every returned row.

Limitations and errors

• One array per row. There's no aggregate that collects rows into one array: json_group_array fails with The operator 'UNGROUPED_AGGREGATE' type=8 is currently not supported for this usage., and to_json(list(id)) fails with The operator 'HASH_GROUP_BY' type=9 is currently not supported. Read the rows with your client and build the array there.
• Text isn't parsed. A VARCHAR argument is always written as a JSON string. Cast it with ::JSON to embed it as JSON.

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

InternalError: The operator 'UNGROUPED_AGGREGATE' type=8 is currently not supported for this usage.
InternalError: The operator 'HASH_GROUP_BY' type=9 is currently not supported.

Similar functions

run("SELECT json_array(1, 2), to_json([1, 2]), array_to_json([1, 2]), json_quote([1, 2])")

[('[1,2]', '[1,2]', '[1,2]', '[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_object and to_json.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.