How to Use jsonb_build_array() function in PostgreSQL

In PostgreSQL, it is possible to create a JSONB array from a list of variable and heterogeneous parameters. This operation is performed by the jsonb_build_array() function. The jsonb_build_array() function is a JSON/JSONB function in Postgres, which converts a variable list of parameters into the JSONB array.

For the scope of this article, we’ll learn to use the jsonb_build_array() function in different use cases.

How to Use jsonb_build_array() function in PostgreSQL?

The jsonb_build_array() function constructs a JSON array from the provided list of heterogeneous parameters. The basic structure of jsonb_build_array() function is given as:

jsonb_build_array(parameter_list   VARIADIC)

In the above syntax:

● A variable list of parameters needs to be provided to the jsonb_build_array() function.

● The jsonb_build_array() function will convert that provided list into the JSONB array.

The jsonb_build_array() function assesses all the elements in the parameter list and utilizes the to_jsonb() function to convert them to place into the array.

The jsonb_build_array() function is the same as the json_build_array() function that is used to build a JSON array.

Below are the examples of the jsonb_build_array() function that will make our concept more clear.

Example 1: Understanding jsonb_build_array() Function in PostgreSQL

We will pass the list of elements of different data types to see how the jsonb_build_array() function converts them into the JSONB array. Assess the following query.

SELECT jsonb_build_array(125, 'string', true, 37.93, null, now());

The above code illustrates that we have provided a heterogeneous list of parameters to the jsonb_build_array() function. The query returns the following output:

We can notice that the jsonb_build_array() function has returned the array built from the parameter list provided. Moreover, the data type of the returned array is JSONB.

Example 2: Using the jsonb_build_array() Function With Arrays

In this section, we’ll see how this jsonb_build_array() function works when we pass an array into it. Let’s execute the jsonb_build_array() function with a 2-dimensional array using the following query:

SELECT jsonb_build_array(ARRAY[[0,9,8],[7,6,5],[4,3,2]]);

We have provided a 2-dimensional array to the jsonb_build_array() function. The output of the query is.

The array is converted into the JSONB array.

Example 3: Using the jsonb_build_array() Function With Composite Types

For this example, we will use the ROW composite type, and see how this function responds to it.

SELECT jsonb_build_array(
  125, 'string', true, null,ROW(125, 'string', true, null));

First, let’s see the output of this query and then we’ll see how it worked.

We have provided 5 parameters to the jsonb_build_array() function. Each parameter is processed by the to_jsonb() function to give the value and that value is then placed in the JSONB array. Like this:

SELECT 
  to_jsonb(125),
  to_jsonb('string'),
  to_jsonb(true),
  to_jsonb(ROW(125, 'string', true, null));

The output of this query is:

All these values are returned to the jsonb_build_array() function, to place them in the JSONB array.

Conclusion

The jsonb_build_array() function takes the list of heterogeneous parameters and builds the JSONB array from them. The list elements are converted using the to_jsonb() function and then are placed in the JSONB array by the jsonb_build_array() function. This is how this function works.

How to Use jsonb_build_array() function in PostgreSQL