PostgreSQL to_json() Function

The PostgreSQL to_json() function converts a SQL value of to a JSON value and returns the result.

`to_json()` Syntax

This is the syntax of the PostgreSQL to_json() function:

to_json(any_value) -> JSON

Parameters

any_value: Required. Any SQL value to convert to a JSON value.

Return value

The PostgreSQL to_json() function returns a JSON value converted from a value of any SQL type.

`to_json()` Examples

Simple Example

This example shows how to use the PostgreSQL to_json() function to convert an SQL value to a JSON value.

SELECT
    to_json(20) AS "to_json(20)",
    to_json('x'::text) AS "to_json('x'::text)",
    to_json(true) AS "to_json(true)",
    to_json(false) AS "to_json(false)";
)";

to_json(20) | to_json('x'::text) | to_json(true) | to_json(false)
-------------+--------------------+---------------+----------------
 20          | "x"                | true          | false

Arrays

This example shows how to use the PostgreSQL to_json() function to convert an SQL array to a JSON array.

SELECT to_json(array[[1, 2], [3, 4]]);

    to_json
---------------
 [[1,2],[3,4]]

This is almost identical to the array_to_json() function.

Composite Types

This example shows how to use the PostgreSQL to_json() function to convert a SQL composite type value to a JSON object.

SELECT to_json(row('Tom', 20, 'He likes sports.'));

                   to_json
----------------------------------------------
 {"f1":"Tom","f2":20,"f3":"He likes sports."}

Here, we use the row expression row('Tom', 20, 'He likes sports.') to construct a value of a composite type. The value of this composite type contains 3 elements: 'Tom', 20 and 'He likes sports.'.

When converting a composite type value into a JSON object, the row_to_json() function automatically generates keys of the object in the format fn, where n is a number, increasing from 1.

This is almost identical to the row_to_json() function.

to_json() Syntax