PostgreSQL jsonb_object() Function

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

Introduction to the PostgreSQL jsonb_object() function

The jsonb_object() function allows you to build a JSON object from a text array.

Here’s the syntax of the jsonb_object() function:

jsonb_object(text[]) → jsonbCode language: SQL (Structured Query Language) (sql)

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.

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

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

jsonb_object ( keys text[], values text[] ) → jsonbCode language: SQL (Structured Query Language) (sql)

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

PostgreSQL jsonb_object() function examples

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

1) Basic PostgreSQL jsonb_object function examples

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

SELECT 
  jsonb_object('{"name","John", "age", 22}');Code language: SQL (Structured Query Language) (sql)

Output:

         jsonb_object
-------------------------------
 {"age": "22", "name": "John"}
(1 row)Code language: SQL (Structured Query Language) (sql)

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

SELECT 
  jsonb_object(
    '{{"name","age"},{"John", 22}}'
  );Code language: SQL (Structured Query Language) (sql)

Output:

         jsonb_object
-------------------------------
 {"John": "22", "name": "age"}
(1 row)Code language: SQL (Structured Query Language) (sql)

Additionally, you can use two arrays including keys and values to create the JSON object:

SELECT 
  jsonb_object(
    '{"name","age"}', '{"John", 22}'
  );Code language: SQL (Structured Query Language) (sql)

Output:

         jsonb_object
-------------------------------
 {"age": "22", "name": "John"}
(1 row)Code language: SQL (Structured Query Language) (sql)

2) Using the jsonb_object() function with table data

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

The following example uses the jsonb_object function to create an object from the title and release_year from the film table:

SELECT
  jsonb_object(
    array[title], array[release_year]::text[]
  ) 
FROM
  film 
ORDER BY 
  title;Code language: SQL (Structured Query Language) (sql)

Output:

         jsonb_object
------------------------------
 {"Academy Dinosaur": "2006"}
 {"Ace Goldfinger": "2006"}
 {"Adaptation Holes": "2006"}
 {"Affair Prejudice": "2006"}
 {"African Egg": "2006"}
...Code language: SQL (Structured Query Language) (sql)

Summary

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