Introduction to the PostgreSQL to_jsonb() function

Theto_jsonb() function allows you to convert an SQL value to aJSONB value.

Here’s the syntax of theto_jsonb() function:

to_jsonb (value ) → jsonb

In this syntax, you specify an SQL value that you want to convert to aJSONB value.

Theto_jsonb() function returns a value converted to aJSONB value. If the value is an array or a composite value, the function will convert to arrays or objects in JSON.

PostgreSQL to_jsonb() function examples

Let’s explore some examples of using theto_jsonb() function.

1) Converting a text string to a JSONB value

The following example uses theto_jsonb() function to convert a text string into aJSONB value:

SELECT to_jsonb('Hello'::text);

Output:

to_jsonb---------- "Hello"(1 row)

The “Hello” is aJSONB value.

To verify it, you can pass the result of theto_jsonb() function to thejsonb_typeof() function.

Thejsonb_typeof() function returns the type of a top-level JSON value as a text string.

For example:

SELECT  JSONB_TYPEOF(    to_jsonb('Hello' ::text)  );

Output:

jsonb_typeof-------------- string(1 row)

2) Converting numbers to a JSONB values

The following example uses theto_jsonb() function to convert numbers toJSONB values:

SELECT  to_jsonb(10 ::int),  to_jsonb(9.99 ::numeric);

Output:

to_jsonb | to_jsonb----------+---------- 10       | 9.99(1 row)

3) Converting bool values to a JSONB values

The following example uses theto_jsonb() function to convert boolean values toJSONB values:

SELECT  to_jsonb(true :: bool),  to_jsonb(false :: bool);

Output:

to_jsonb | to_jsonb----------+---------- true     | false(1 row)

4) Converting NULL to a JSONB value

The following example uses theto_jsonb() function to convertNULL to aJSONB value:

SELECT  to_jsonb(NULL::text);

Output:

to_jsonb---------- null(1 row)

5) Converting a PostgreSQL array into a JSON array

The following example uses theto_jsonb() function to convert an array in PostgreSQL to a JSON array with theJSONB type:

SELECT  to_jsonb(    ARRAY[ 'red', 'green', 'blue' ]  )AS jsonb_array;

Output:

jsonb_array-------------------------- ["red", "green", "blue"](1 row)

6) Using the to_jsonb() function with table data

We’ll use theto_jsonb() function to convert data in thefilm table from thesample database toJSONB values:

SELECT  to_jsonb(title),  to_jsonb(length)FROM  filmORDER BY  title;

Output:

to_jsonb            | to_jsonb-------------------------------+---------- "Academy Dinosaur"            | 86 "Ace Goldfinger"              | 48 "Adaptation Holes"            | 50...

Summary

• Use the PostgreSQLto_jsonb() function to convert an SQL value to aJSONB value.

Last updated onFebruary 25, 2024

On this page

• Introduction to the PostgreSQL to_jsonb() function
• PostgreSQL to_jsonb() function examples
• Summary