PostgreSQL jsonb_array_elements() Function

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

Introduction to the PostgreSQL jsonb_array_elements() function

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

Here’s the basic syntax of the jsonb_array_elements() function:

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

In this syntax, you specify a JSON array with a JSONB type that you want to expand its elements.

The jsonb_array_elements() function will expand the elements of the json_array into individual elements.

If you pass a non-array to the function, it’ll issue an error. If the json_array is NULL, the function returns an empty result set.

PostgreSQL jsonb_array_elements() function examples

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

1) Basic PostgreSQL jsonb_array_elements() function examples

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

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

Output:

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

Note that the numbers 1, 2, 3 are the JSON values.

The following example uses the jsonb_array_elements() function to expand an array of strings:

SELECT jsonb_array_elements('["red","green","blue"]');Code language: SQL (Structured Query Language) (sql)

Output:

 jsonb_array_elements
----------------------
 "red"
 "green"
 "blue"
(3 rows)Code language: SQL (Structured Query Language) (sql)

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

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

SELECT jsonb_array_elements('[1,2,3, [4,5], 6]');Code language: SQL (Structured Query Language) (sql)

Output:

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

3) Using the jsonb_array_elements() 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(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.

It’s possible to use the DISTINCT to get unique skills of all employees:

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

Output:

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

Summary

• Use the jsonb_array_elements() function to expand elements of the top-level JSON array.