Skip to main content

PostgreSQL jsonb_build_array() Function

Summary: in this tutorial, you will learn how to use the PostgreSQL jsonb_build_array() function to create a JSON array.

Introduction to the PostgreSQL jsonb_build_array() function​

The jsonb_build_array() function allows you to construct a JSONB array from a variadic list of arguments.

Here’s the basic syntax of the jsonb_build_array() function:

jsonb_build_array ( VARIADIC "any" ) → jsonb

In this syntax, you provide a list of arguments that you want to convert to elements of a JSON array.

The jsonb_build_array() will convert each argument using the to_jsonb() function.

PostgreSQL jsonb_build_array() function examples​

Let’s take some examples of using the jsonb_build_array() function.

1) Basic PostgreSQL jsonb_build_array() function example​

The following example uses the jsonb_build_array() function to create a JSON array from a list of values:

SELECT jsonb_build_array(10, null, 'Hi', true) result;

Output:

         result
------------------------
 [10, null, "Hi", true]
(1 row)

2) Using PostgreSQL jsonb_build_array() function with table data​

We’ll use the film table from the sample database:

The following example uses the jsonb_build_array() function to convert the title and length of each film into elements of a JSON array:

SELECT
  jsonb_build_array(title, length)
FROM
  film
ORDER BY
  title;

Output:

          jsonb_build_array
--------------------------------------
 ["Academy Dinosaur", 86]
 ["Ace Goldfinger", 48]
 ["Adaptation Holes", 50]
 ["Affair Prejudice", 117]
...

Summary​

  • Use the PostgreSQL jsonb_build_array() function to create a JSONB array from a variadic list of arguments.