PostgreSQL to_jsonbb() Function

The PostgreSQL to_jsonb() function converts a SQL value to a JSONB value and returns the result.

`to_jsonb()` Syntax

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

to_jsonb(any_value) -> JSONB

Parameters

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

Return value

The PostgreSQL to_jsonb() function returns a JSONB value converted from a value of any SQL type.

`to_jsonb()` Examples

Simple Example

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

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

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

Arrays

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

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

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

This is almost identical to the array_to_jsonb() function.

Composite Types

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

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

                   to_jsonb
----------------------------------------------
 {"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_jsonb() 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_jsonb() function.

to_jsonb() Syntax