---
title: "json_quote"
slug: "dev-database-reference-sql-functions-json-json-quote"
updated: 2026-10-30T18:19:06Z
published: 2026-10-30T18:19:06Z
canonical: "docs.vastdata.com/dev-database-reference-sql-functions-json-json-quote"
---

> ## 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_quote

*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.* 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](/develop/docs/dev-database-reference-sql-functions-json-to-json).

## Syntax

```
json_quote(value)
```

| Argument | Type | Description |
| --- | --- | --- |
| `value` | any type | The value to convert. |

**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)](/develop/docs/dev-database-integrations-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](/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 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:

| SQL value | Result | Example |
| --- | --- | --- |
| `VARCHAR` | a quoted string; `&quot;` becomes `\&quot;`, `\` becomes `\\`, a line break `\n`, `chr(1)` `\u0001` \| `json_quote(&apos;a&quot;b\c&apos;)` gives `&quot;a\&quot;b\\c&quot;` |  |
| Number | a JSON number, without trailing zeros | `json_quote(1.50::DECIMAL(4,2))` gives `1.5` |
| `BOOLEAN` | `true` or `false` | `json_quote(true)` gives `true` |
| `DATE`, `TIMESTAMP`, `TIME`, `INTERVAL`, `UUID` | a quoted string | `json_quote(DATE &apos;2026-10-16&apos;)` gives `&quot;2026-10-16&quot;` |
| List, struct, map | an array or an object | `json_quote([1, NULL, 3])` gives `[1,null,3]` |

Non-ASCII characters and `/` are written as they are. The rules for every type are the ones on the [to_json](/develop/docs/dev-database-reference-sql-functions-json-to-json#what-it-does) page.

## NULLs and edge cases

| Input | Result |
| --- | --- |
| `NULL` | `NULL` (not the JSON `null`) |
| `&apos;&apos;` (empty string) | `&quot;&quot;` |
| A `VARCHAR` holding JSON text, `&apos;{&quot;a&quot;: 1}&apos;` | A JSON string, `&quot;{\&quot;a\&quot;: 1}&quot;`: the text is quoted, not parsed |
| `&apos;NaN&apos;::DOUBLE`, `&apos;inf&apos;::DOUBLE` | `NaN`, `Infinity`: not part of the JSON standard |
| A `BLOB` value | `&apos;&apos;`, an empty string, which isn't valid JSON |

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](/develop/docs/dev-database-reference-sql-functions-json-json-quote#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](/develop/docs/dev-database-reference-sql-functions-json-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](/develop/docs/dev-database-reference-sql-functions-json-to-json) of a struct: `to_json({&apos;a&apos;: NULL})` gives `{&quot;a&quot;: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](/develop/docs/dev-database-reference-sql-functions-json-to-json#limitations-and-errors).

## Similar functions

| Function | Use it to | Example | Result |
| --- | --- | --- | --- |
| [to_json](/develop/docs/dev-database-reference-sql-functions-json-to-json) | convert a value; the same results as `json_quote` | `to_json(&apos;a&quot;b&apos;)` | `&quot;a\&quot;b&quot;` |
| [json_object](/develop/docs/dev-database-reference-sql-functions-json-json-object) | build an object from keys and values | `json_object(&apos;a&apos;, 1)` | `{&quot;a&quot;:1}` |
| [json_array](/develop/docs/dev-database-reference-sql-functions-json-json-array) | build an array from values | `json_array(1, 2)` | `[1,2]` |
| [to_json](/develop/docs/dev-database-reference-sql-functions-json-to-json) of a struct | build a document from columns | `to_json({&apos;a&apos;: 1})` | `{&quot;a&quot;:1}` |

```
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](/develop/docs/dev-database-reference-sql-functions-json-to-json): the full conversion rules.
- [Parsing and JSON functions](/develop/docs/dev-database-reference-sql-functions-json): all the JSON functions.
- [EXPLAIN](/develop/docs/dev-database-reference-sql-explain): how to read a plan.