Introduction to the PostgreSQL jsonb_array_elements() function

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

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

jsonb_array_elements(json_array)

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

Thejsonb_array_elements() function will expand the elements of thejson_array into individual elements.

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

PostgreSQL jsonb_array_elements() function examples

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

1) Basic PostgreSQL jsonb_array_elements() function examples

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

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

Output:

jsonb_array_elements---------------------- 1 2 3(3 rows)

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

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

SELECT jsonb_array_elements('["red","green","blue"]');

Output:

jsonb_array_elements---------------------- "red" "green" "blue"(3 rows)

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

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

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

Output:

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

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

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

SELECT DISTINCT jsonb_array_elements(skills) skillsFROM employees;

Output:

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

Summary

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

Last updated onFebruary 24, 2024

On this page

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