json_quote

Applies to VAST Cluster 5.5.

Functions › Parsing and JSON functions › Build JSON. Converts a SQL value to JSON text. A string becomes a quoted JSON string with its special characters escaped, so it can be put inside a JSON document safely. For every kind of value it gives the same result as to_json.

Syntax

json_quote(value)

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_json_quote. 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 five comments. They hold a double quote, a backslash and a line break; the last row has no value:

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_quote"'

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}.comments"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, author VARCHAR, body VARCHAR)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, 'ada', 'plain text'), "
    "(2, 'grace', 'say \"hi\"'), "
    "(3, 'alan', 'C:\\temp'), "
    "(4, 'jose', 'line\nbreak'), "
    "(5, NULL, NULL)")

[]
[]
[(5,)]

What it does

json_quote turns a value into its JSON text:

Non-ASCII characters and / are written as they are. The rules for every type are the ones on the to_json page.

NULLs and edge cases

Every case on this page, as the cluster returns it, with to_json beside it. Each line shows the SQL value and both results. They use the helpers from Set up the examples:

for expr in ["'a\"b\\c'", "chr(10)", "chr(1)", "''", "'\u00e9'", "'</script>'", "1.50::DECIMAL(4,2)", "true",
             "DATE '2026-10-16'", "[1, NULL, 3]", "{'a': NULL}", "MAP {'k': 'v'}", "'{\"a\": 1}'",
             "'NaN'::DOUBLE", "'inf'::DOUBLE", "'\\xAA'::BLOB", "NULL"]:
    print(expr, end="  ")
    run(f"SELECT json_quote({expr}), to_json({expr})")

'a"b\c'  [('"a\\"b\\\\c"', '"a\\"b\\\\c"')]
chr(10)  [('"\\n"', '"\\n"')]
chr(1)  [('"\\u0001"', '"\\u0001"')]
''  [('""', '""')]
'é'  [('"é"', '"é"')]
'</script>'  [('"</script>"', '"</script>"')]
1.50::DECIMAL(4,2)  [('1.5', '1.5')]
true  [('true', 'true')]
DATE '2026-10-16'  [('"2026-10-16"', '"2026-10-16"')]
[1, NULL, 3]  [('[1,null,3]', '[1,null,3]')]
{'a': NULL}  [('{"a":null}', '{"a":null}')]
MAP {'k': 'v'}  [('{"k":"v"}', '{"k":"v"}')]
'{"a": 1}'  [('"{\\"a\\": 1}"', '"{\\"a\\": 1}"')]
'NaN'::DOUBLE  [('NaN', 'NaN')]
'inf'::DOUBLE  [('Infinity', 'Infinity')]
'\xAA'::BLOB  [('', '')]
NULL  [(None, None)]

Examples

Quote each comment. The double quote, the backslash and the line break are escaped:

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

[(1, '"plain text"'), (2, '"say \\"hi\\""'), (3, '"C:\\\\temp"'), (4, '"line\\nbreak"'), (5, None)]

Put the quoted text inside a document built with ||, and check the result with json_valid:

run("SELECT id, '{\"author\": ' || json_quote(author) || ', \"body\": ' || json_quote(body) || '}' AS doc, "
    "json_valid('{\"body\": ' || json_quote(body) || '}') "
    f"FROM {TABLE} WHERE id < 5 ORDER BY id LIMIT 10")

[(1, '{"author": "ada", "body": "plain text"}', True), (2, '{"author": "grace", "body": "say \\"hi\\""}', True), (3, '{"author": "alan", "body": "C:\\\\temp"}', True), (4, '{"author": "jose", "body": "line\\nbreak"}', True)]

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_quote runs in the Query Engine: the scan reads the column, and a projection step above it quotes the value of each row it returns. In WHERE, a condition on json_quote() runs in a FILTER step in the Query Engine.

plan(f"SELECT id, json_quote(body) FROM {TABLE}")

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

Make it faster

• Filter before you quote. 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 json_quote.

Limitations and errors

• NULL stays NULL, not the JSON null. To write null for a missing value, build the document with to_json of a struct: to_json({'a': NULL}) gives {"a":null}.
• Text is quoted, not parsed. To embed a VARCHAR that holds a JSON document, cast it to JSON first.
• BLOB gives an empty string, and NaN and infinity aren't standard JSON; see to_json.

Similar functions

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

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

Clean up:

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

[]
[]

See also

• to_json: the full conversion rules.
• Parsing and JSON functions: all the JSON functions.
• EXPLAIN: how to read a plan.