PostgreSQL provides a JSON array data type which is similar to standard arrays in programming. It helps us store ordered collections in JSON format. JSON arrays are enclosed within square brackets “[ ]” and can have different data types like strings, objects, numbers, etc. Postgres offers several functions to work with the JSON data, and JSON_ARRAY_ELEMENTS() is one of them.

This post illustrates the basic syntax and working of the JSON_ARRAY_ELEMENTS() function in Postgres using practical examples.

How to Use JSON_ARRAY_ELEMENTS() Function in PostgreSQL?

The JSON_ARRAY_ELEMENTS() is a built-in JSON function that accepts a JSON array as an argument and expands its top-level elements into a set of values.

Syntax

Use the below-stated syntax to retrieve an expanded set of specified JSON array elements:

JSON_ARRAY_ELEMENTS(array JSON)

Parameters

The stated function accepts a single argument “array” that must be a JSON array.

Return Value

An expanded set that consists of given JSON array elements.

Return Type

The data type of the expanded set values will be JSON.

Example 1: Using JSON_ARRAY_ELEMENTS() Function

The below code snippet demonstrates the use of the JSON_ARRAY_ELEMENTS() function in Postgres:

SELECT JSON_ARRAY_ELEMENTS('[100, 110, 90, [120, -12], [13, 14]]');

The stated function retrieves an expanded set that consists of the specified array elements:

Example 2: Using JSON_ARRAY_ELEMENTS() Function as a Temp Table

The return type of the JSON_ARRAY_ELEMENTS function is “SETOF” which allows us to utilize it as a temporary table. For this purpose, we can execute the JSON_ARRAY_ELEMENTS() function with the “SELECT *” statement as follows:

SELECT * 
FROM JSON_ARRAY_ELEMENTS('[100, 110, 90, [120, -12], [13, 14]]');

On successful execution of the JSON_ARRAY_ELEMENTS() function, you will get the following output:

This is how the JSON_ARRAY_ELEMENTS() function works in PostgreSQL.

Conclusion

The JSON_ARRAY_ELEMENTS() is a built-in JSON function that accepts a JSON array as an argument and expands its top-level elements into a set of values. The return type of the JSON_ARRAY_ELEMENTS function is “SETOF” which allows us to utilize it as a temporary table. This post has demonstrated the working of the JSON_ARRAY_ELEMENTS() in PostgreSQL using suitable examples.