TO_JSON

Convert a SQL value to its JSON representation.

Category: jsonReturns: JSONDialect: PostgreSql

Syntax

TO_JSON(value)

Description

## Overview Converts an arbitrary SQL value to its JSON representation. Strings are wrapped in double quotes and escape sequences are applied per RFC 8259. Numbers and booleans become JSON numbers and booleans. SQL NULL maps to SQL NULL, not JSON null. Composite types and arrays are converted recursively, element by element. Use TO_JSON when you need a scalar JSON value as part of a larger document, or when writing one row's payload out as JSON text. For the binary variant that supports indexed access, use TO_JSONB. ## Behavior - SQL strings are emitted with surrounding double quotes and with RFC 8259 escaping for control characters and quotation marks. - Numbers and booleans are emitted in their native JSON form. - SQL NULL returns SQL NULL. To emit a JSON null, use a JSON literal 'null'::JSON. - Dates and timestamps are serialized as ISO 8601 strings. - Composite types, ROWs, and arrays are converted recursively. - The result type is JSON (text). Use TO_JSONB for the binary variant. ## Compatibility - Follows the SQL/JSON TO_JSON semantics from SQL:2016. - Output conforms to RFC 8259.

Parameters

Name	Type	Description
`value`		Specifies the value to convert to JSON. Accepts strings, numbers, booleans, arrays, composite types, and NULL. Strings are JSON-encoded with surrounding double quotes. Numbers and booleans are emitted as JSON numbers and booleans. Composite types and arrays are converted recursively.

Examples

-- Convert a string (note the JSON quoting)
SELECT TO_JSON('hello'::TEXT) AS val;

-- Convert an integer
SELECT TO_JSON(42) AS val;

-- Convert a boolean
SELECT TO_JSON(true) AS val;

-- SQL NULL maps to SQL NULL, not JSON null
SELECT TO_JSON(NULL) AS val;

-- Convert a composite type from a realistic row source
SELECT TO_JSON(t) AS doc
FROM (SELECT user_id, display_name FROM iam.directory.users LIMIT 3) t;

-- Convert an array literal
SELECT TO_JSON(ARRAY['apple', 'banana', 'cherry']) AS arr;

Pitfalls

SQL NULL maps to SQL NULL, not JSON null. Use the literal 'null'::JSON when you need a JSON null value.
Strings are JSON-encoded; the TEXT 'hello' becomes '"hello"' (with quotes) in the output.
Dates and timestamps are serialized as ISO 8601 strings with no timezone information for naive timestamps; consumers must be prepared for timezone-less values.
Floats are serialized with full precision; downstream parsers may lose digits if they use double-precision math.
The result is JSON text. For indexed access or containment, use TO_JSONB instead.