How to Use jsonb_array_elements in PostgreSQL: Syntax and Examples

In PostgreSQL, use jsonb_array_elements to expand a JSONB array into a set of JSONB values, each as a separate row. This function is useful to work with each element of a JSON array individually in SQL queries.

Syntax

The jsonb_array_elements function takes a single jsonb array as input and returns a set of JSONB values, one for each element in the array.

Basic syntax:

• jsonb_array_elements(jsonb_array): Expands the JSONB array into a set of JSONB elements.

sql

SELECT jsonb_array_elements('[{"name": "Alice"}, {"name": "Bob"}]'::jsonb);

Output

("{\"name\": \"Alice\"}")
("{\"name\": \"Bob\"}")

Example

This example shows how to extract each element from a JSONB array stored in a table and select a field from each JSON object.

sql

CREATE TEMP TABLE users(data jsonb);

INSERT INTO users VALUES
  ('[{"name": "Alice", "age": 30}, {"name": "Bob", "age": 25}]');

SELECT elem->>'name' AS name, elem->>'age' AS age
FROM users, jsonb_array_elements(users.data) AS elem;

Output

 name  | age 
-------+-----
 Alice | 30  
 Bob   | 25  

Common Pitfalls

Common mistakes when using jsonb_array_elements include:

• Passing a JSONB value that is not an array, which causes an error.
• Not using a LATERAL join or comma join to expand the array elements properly.
• Confusing jsonb_array_elements with jsonb_array_elements_text, which returns text instead of JSONB.

Example of wrong usage and fix:

sql

SELECT jsonb_array_elements('{"name": "Alice"}'::jsonb);
-- Error: cannot call jsonb_array_elements on a JSONB object

-- Correct usage:
SELECT jsonb_array_elements('[{"name": "Alice"}]'::jsonb);

Output

ERROR:  cannot call jsonb_array_elements on a non-array

 jsonb_array_elements 
---------------------
 {"name": "Alice"}
(1 row)

Quick Reference

Function	Description
jsonb_array_elements(jsonb_array)	Expands JSONB array into set of JSONB elements
jsonb_array_elements_text(jsonb_array)	Expands JSONB array into set of text elements
Use with LATERAL or comma join	To join expanded elements with other table columns
Input must be a JSONB array	Passing non-array JSONB causes error

Key Takeaways

Use jsonb_array_elements to turn a JSONB array into multiple rows, one per element.

Input to jsonb_array_elements must be a JSONB array, not an object or scalar.

Join jsonb_array_elements with tables using a comma or LATERAL join to access elements.

Use jsonb_array_elements_text if you want text output instead of JSONB values.

Common errors happen when input is not an array or join syntax is missing.