Introduction to the PostgreSQL jsonb_array_elements_text() function

Thejsonb_array_elements_text() function allows you to expand the elements of a top-levelJSON array into a set of JSON values.

The following shows the basic syntax of thejsonb_array_elements_text() function:

jsonb_array_elements_text(json_array)

In this syntax:

• json_array is a JSON array with theJSONB type, which you want to expand the elements.

Thejsonb_array_elements_text() function will expand the elements in thejson_array into individual text values.

If you pass an object to the function, it’ll issue an error. In case thejson_array isNULL, the function returns an empty result set.

PostgreSQL jsonb_array_elements_text() function examples

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

1) Basic PostgreSQL jsonb_array_elements_text() function examples

The following example uses thejsonb_array_elements_text() function to expand elements of a JSON array:

SELECT jsonb_array_elements_text('["orange","banana","watermelon"]');

Output:

jsonb_array_elements_text--------------------------- orange banana watermelon(3 rows)

The following example uses thejsonb_array_elements_text() function to expand an array of numbers:

SELECT jsonb_array_elements_text('[1,2,3]');

Output:

jsonb_array_elements_text--------------------------- 1 2 3(3 rows)

Note that 1, 2, and 3 are text values, not numbers. To convert them to numbers, you need to have an explicit cast.

2) Using the jsonb_array_elements_text() function with nested arrays example

The following example uses thejsonb_array_elements_text() function to expand elements of an array that contains another array:

SELECT jsonb_array_elements_text('[1,2,3, [4,5], 6]');

Output:

jsonb_array_elements_text--------------------------- 1 2 3 [4, 5] 6(5 rows)

3) Using the jsonb_array_elements_text() function with table data

First,create a table calledemployees:

CREATE TABLE employees (    idSERIAL PRIMARY KEY,    name VARCHAR(100)NOT NULL,    skills JSONB);

Theskills column has the JSONB type, which stores the skills of employees.

Second,insert some rows into theemployees table:

INSERT INTO employees (name, skills)VALUES('John Doe','["Java", "Python", "SQL"]'),('Jane Smith','["C++", "JavaScript", "HTML/CSS"]'),('Alice Johnson','["Python", "Data Analysis", "Machine Learning"]'),('Bob Brown','["Java", "SQL", "Spring Framework"]');

Third, retrieve all skills of employees:

SELECT jsonb_array_elements_text(skills) skillsFROM employees;

Output:

skills------------------ Java Python SQL C++ JavaScript HTML/CSS Python Data Analysis Machine Learning Java SQL Spring Framework(12 rows)

It returns 12 skills as text values.

If you want to get unique skills, you can use theDISTINCT operator:

SELECT DISTINCT jsonb_array_elements_text(skills) skillsFROM employees;

Output:

skills------------------ Data Analysis C++ JavaScript SQL Python Machine Learning Spring Framework HTML/CSS Java(9 rows)

Summary

• Use thejsonb_array_elements_text() function to expand elements of the top-level JSON array into a set of text values.

Last updated onFebruary 24, 2024

On this page

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