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" ) → jsonbCode language: SQL (Structured Query Language) (sql)

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;Code language: SQL (Structured Query Language) (sql)

Output:

         result
------------------------
 [10, null, "Hi", true]
(1 row)Code language: SQL (Structured Query Language) (sql)

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;Code language: SQL (Structured Query Language) (sql)

Output:

          jsonb_build_array
--------------------------------------
 ["Academy Dinosaur", 86]
 ["Ace Goldfinger", 48]
 ["Adaptation Holes", 50]
 ["Affair Prejudice", 117]
...Code language: SQL (Structured Query Language) (sql)

Summary

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