json_object

Applies to VAST Cluster 5.5.

Functions › Parsing and JSON functions › Build JSON. Builds a JSON object from key-value pairs: json_object('id', 1, 'name', 'ada') gives {"id":1,"name":"ada"}. Values can be of any type; keys are written as strings.

Syntax

json_object(key, value, ...)
json_object()

Argument	Type	Description
`key`	Any type, usually `VARCHAR`	The member's name. A number or a date is written as a string.
`value`	Any type	The member's value.

Arguments come in pairs: key, value, key, value.

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_object. 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. Alan has no city, 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_object"'

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, city VARCHAR, n BIGINT, tags VARCHAR[])")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, 'ada', 'Oslo', 3, ['a', 'b']), "
    "(2, 'alan', NULL, 0, []), "
    "(3, 'grace', 'New York', -1, ['c']), "
    "(4, NULL, NULL, NULL, NULL)")

[]
[]
[(4,)]

What it does

json_object writes one member per key-value pair, in the order given:

Keys are written as JSON strings: json_object(1, 2) gives {"1":2}, and a date key gives {"2026-10-16":1}. A double quote in a key is escaped.
Values are converted like json_array elements: numbers stay numbers, text becomes a string, a list becomes an array, a struct an object, and a JSON value is embedded as it is.
Text that holds JSON is still text. json_object('k', '{"a": 1}') gives {"k":"{\"a\": 1}"}; cast the value with ::JSON to embed the document.

run("SELECT json_object(1, 2), json_object(DATE '2026-10-16', 1), json_object('a\"b', 1)")
run("SELECT json_object('k', [1, 2], 'm', {'a': 1})")
run("SELECT json_object('k', '{\"a\": 1}'::JSON), json_object('k', '{\"a\": 1}')")

[('{"1":2}', '{"2026-10-16":1}', '{"a\\"b":1}')]
[('{"k":[1,2],"m":{"a":1}}',)]
[('{"k":{"a":1}}', '{"k":"{\\"a\\": 1}"}')]

NULLs and edge cases

Input	Result
`json_object()` (no arguments)	`{}`
`json_object('k', NULL)`	`{"k":null}`
`json_object(NULL, 1)` (a NULL key)	`{}`: the pair is left out
`json_object('k', 1, 'k', 2)` (a repeated key)	`{"k":1,"k":2}`: both members are kept
`json_object('k', 'NaN'::DOUBLE)`	`{"k":NaN}`, which isn't valid JSON
`json_object('k')` (an odd number of arguments)	Error: `json_object() requires an even number of arguments`

run("SELECT json_object(), json_object('k', NULL), json_object(NULL, 1), json_object('k', 1, 'k', 2)")
run("SELECT json_object('k', 'NaN'::DOUBLE)")
run("SELECT json_object('k')")

[('{}', '{"k":null}', '{}', '{"k":1,"k":2}')]
[('{"k":NaN}',)]
InternalError: Invalid Input Error: json_object() requires an even number of arguments

Examples

One object per row. A NULL column gives a null member; the member isn't left out:

run(f"SELECT id, json_object('id', id, 'name', name, 'city', city) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, '{"id":1,"name":"ada","city":"Oslo"}'), (2, '{"id":2,"name":"alan","city":null}'), (3, '{"id":3,"name":"grace","city":"New York"}'), (4, '{"id":4,"name":null,"city":null}')]

Nest a list and another object:

run(f"SELECT id, json_object('name', name, 'tags', tags, 'stats', json_object('n', n)) "
    f"FROM {TABLE} ORDER BY id LIMIT 10")

[(1, '{"name":"ada","tags":["a","b"],"stats":{"n":3}}'), (2, '{"name":"alan","tags":[],"stats":{"n":0}}'), (3, '{"name":"grace","tags":["c"],"stats":{"n":-1}}'), (4, '{"name":null,"tags":null,"stats":{"n":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_object is always computed by the Query Engine:

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

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

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

Make it faster

Filter on the columns, not on the object. A comparison of a column with a constant, such as name = 'ada', 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_object('id', id, 'city', city) FROM {TABLE} WHERE name = 'ada'")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│   json_object('id', id,   │
│       'city', city)       │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│             id            │
│            city           │
│                           │
└───────────────────────────┘

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

Limitations and errors

Arguments come in pairs. An odd number fails with Invalid Input Error: json_object() requires an even number of arguments.
Repeated keys aren't checked. Both members are written. Use each key once.
NaN isn't valid JSON. A NaN double is written as NaN. If the JSON goes to a strict reader, replace those values before you build the object.
One object per row. There's no aggregate that builds one object from many rows: json_group_object fails with The operator 'UNGROUPED_AGGREGATE' type=8 is currently not supported for this usage. Read the rows with your client and build the object there.

run(f"SELECT json_group_object(name, n) FROM {TABLE}")

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

Similar functions

Function	What it builds	Result
`json_object('a', 1)`	an object from key-value pairs	`{"a":1}`
row_to_json	an object from a struct: `row_to_json({'a': 1})`	`{"a":1}`
to_json	JSON from any value: `to_json({'a': 1})`	`{"a":1}`
json_merge_patch	an object from merged documents: `json_merge_patch('{"a": 1}', '{"b": 2}')`	`{"a":1,"b":2}`
json_array	an array

run("SELECT json_object('a', 1), row_to_json({'a': 1}), to_json({'a': 1})")
run("SELECT json_merge_patch('{\"a\": 1}', '{\"b\": 2}'), json_object('a', 1, 'b', 2)")

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

Clean up:

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

[]
[]