PostgreSQL jsonb_array_elements_text() Function

Summary: in this tutorial, you will learn how to use the PostgreSQL jsonb_array_elements_text() function to expand the elements of a top-level JSON array into a set of text values.

Introduction to the PostgreSQL jsonb_array_elements_text() function

The jsonb_array_elements_text() function allows you to expand the elements of a top-level JSON array into a set of JSON values.

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

jsonb_array_elements_text(json_array)Code language: SQL (Structured Query Language) (sql)

In this syntax:

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

The jsonb_array_elements_text() function will expand the elements in the json_array into individual text values.

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

PostgreSQL jsonb_array_elements_text() function examples

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

1) Basic PostgreSQL jsonb_array_elements_text() function examples

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

SELECT jsonb_array_elements_text('["orange","banana","watermelon"]');Code language: SQL (Structured Query Language) (sql)

Output:

 jsonb_array_elements_text
---------------------------
 orange
 banana
 watermelon
(3 rows)Code language: SQL (Structured Query Language) (sql)

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

SELECT jsonb_array_elements_text('[1,2,3]');Code language: SQL (Structured Query Language) (sql)

Output:

 jsonb_array_elements_text
---------------------------
 1
 2
 3
(3 rows)Code language: SQL (Structured Query Language) (sql)

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

Output:

 jsonb_array_elements_text
---------------------------
 1
 2
 3
 [4, 5]
 6
(5 rows)Code language: SQL (Structured Query Language) (sql)

3) Using the jsonb_array_elements_text() function with table data

First, create a table called employees:

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    skills JSONB
);Code language: SQL (Structured Query Language) (sql)

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

Second, insert some rows into the employees 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"]');Code language: SQL (Structured Query Language) (sql)

Third, retrieve all skills of employees:

SELECT jsonb_array_elements_text(skills) skills
FROM employees;Code language: SQL (Structured Query Language) (sql)

Output:

      skills
------------------
 Java
 Python
 SQL
 C++
 JavaScript
 HTML/CSS
 Python
 Data Analysis
 Machine Learning
 Java
 SQL
 Spring Framework
(12 rows)Code language: SQL (Structured Query Language) (sql)

It returns 12 skills as text values.

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

SELECT DISTINCT jsonb_array_elements_text(skills) skills
FROM employees;Code language: SQL (Structured Query Language) (sql)

Output:

      skills
------------------
 Data Analysis
 C++
 JavaScript
 SQL
 Python
 Machine Learning
 Spring Framework
 HTML/CSS
 Java
(9 rows)Code language: SQL (Structured Query Language) (sql)

Summary

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