PostgreSQL offers a JSON array data type, similar to standard arrays in programming, which enables the storage of ordered collections in JSON format. Postgres provides numerous functions to manipulate JSON data, including JSON_ARRAY_ELEMENTS_TEXT(), JSON_ARRAY_LENGTH(), JSON_ARRAY_ELEMENTS(), and many more.

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_TEXT() Function in PostgreSQL?

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

Syntax

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

JSON_ARRAY_ELEMENTS_TEXT(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 in the form of TEXT.

Return Type

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

Example 1: Using JSON_ARRAY_ELEMENTS_TEXT() Function

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

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

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

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

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

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

On successful execution, the JSON_ARRAY_ELEMENTS_TEXT() function will retrieve the following output:

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

Conclusion

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