Introduction to the PostgreSQL jsonb_object() function

Thejsonb_object() function allows you to build a JSON object from atextarray.

Here’s the syntax of thejsonb_object() function:

jsonb_object(text[]) → jsonb

In this syntax,text[] array can be:

• A one-dimensional array that contains an even number of elements. The elements are the alternating key/value pairs.
• A two-dimensional array. Each inner array has exactly two elements representing the key/value pair.

Thejsonb_object() function returns a JSON object constructed from the text array with the type of JSONB.

Thejsonb_object() function has another syntax that takes keys and values pairwise from separate text arrays:

jsonb_object ( keystext[],values text[] ) → jsonb

In this syntax, the keys and values arrays in this syntax have the same number of elements. Thekeys array contains the keys of the JSON object whereas thevalues array contains the corresponding values of thekeys.

PostgreSQL jsonb_object() function examples

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

1) Basic PostgreSQL jsonb_object function examples

The following example uses thejsonb_object() function to create a JSON object from a text array:

SELECT  jsonb_object('{"name","John", "age", 22}');

Output:

jsonb_object------------------------------- {"age": "22", "name": "John"}(1 row)

Alternatively, you can use a two-dimensional arrays to create the JSON object:

SELECT  jsonb_object(    '{{"name","age"},{"John", 22}}'  );

Output:

jsonb_object------------------------------- {"John": "22", "name": "age"}(1 row)

Additionally, you can use two arrays includingkeys andvalues to create the JSON object:

SELECT  jsonb_object(    '{"name","age"}','{"John", 22}'  );

Output:

jsonb_object------------------------------- {"age": "22", "name": "John"}(1 row)

2) Using the jsonb_object() function with table data

We’ll use thefilm table from thesample database.

The following example uses thejsonb_object function to create an object from thetitle andrelease_year from thefilm table:

SELECT  jsonb_object(    array[title],array[release_year]::text[]  )FROM  filmORDER BY  title;

Output:

jsonb_object------------------------------ {"Academy Dinosaur": "2006"} {"Ace Goldfinger": "2006"} {"Adaptation Holes": "2006"} {"Affair Prejudice": "2006"} {"African Egg": "2006"}...

Summary

• Use the PostgreSQLjsonb_object() function to create a JSON object from a text array.

Last updated onFebruary 25, 2024

On this page

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