--- title: "json_object" slug: "dev-database-reference-sql-functions-json-json-object" updated: 2026-10-30T18:19:06Z published: 2026-10-30T18:19:06Z canonical: "docs.vastdata.com/dev-database-reference-sql-functions-json-json-object" --- > ## Documentation Index > Fetch the complete documentation index at: https://docs.vastdata.com/llms.txt > Use this file to discover all available pages before exploring further. # json_object *Applies to VAST Cluster 5.5.* *[Functions](/develop/docs/dev-database-reference-sql-functions-index) › [Parsing and JSON functions](/develop/docs/dev-database-reference-sql-functions-json) › 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)](/develop/docs/dev-database-integrations-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](/develop/docs/dev-database-reference-sql-index#example-connect-and-use-a-transaction)), 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, "") 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](/develop/docs/dev-database-reference-sql-functions-json-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](/develop/docs/dev-database-reference-sql-functions-json-row-to-json) | an object from a struct: `row_to_json({'a': 1})` | `{"a":1}` | | [to_json](/develop/docs/dev-database-reference-sql-functions-json-to-json) | JSON from any value: `to_json({'a': 1})` | `{"a":1}` | | [json_merge_patch](/develop/docs/dev-database-reference-sql-functions-json-json-merge-patch) | an object from merged documents: `json_merge_patch('{"a": 1}', '{"b": 2}')` | `{"a":1,"b":2}` | | [json_array](/develop/docs/dev-database-reference-sql-functions-json-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() ``` ``` [] [] ``` ## See also - [Parsing and JSON functions](/develop/docs/dev-database-reference-sql-functions-json): all the JSON functions. - [json_array](/develop/docs/dev-database-reference-sql-functions-json-json-array) and [row_to_json](/develop/docs/dev-database-reference-sql-functions-json-row-to-json). - [Where filters run](/develop/docs/dev-database-reference-sql-select#where-filters-run) and [pushdowns](/develop/docs/dev-database-reference-pushdowns). - [EXPLAIN](/develop/docs/dev-database-reference-sql-explain): how to read a plan.