PostgreSQL offers several built-in JSON functions to manipulate JSON data efficiently, such as JSON_ARRAY_ELEMENTS_TEXT(), JSON_ARRAY_ELEMENTS(), JSON_ARRAY_LENGTH(), and many more. All these functions serve a unique purpose and have different use cases.

This write-up illustrates a detailed comparison of the JSON_ARRAY_ELEMENTS_TEXT() and the JSON_ARRAY_ELEMENTS() functions in Postgres using the following content:

- What Does JSON_ARRAY_ELEMENTS() do in Postgres?
- What Does JSON_ARRAY_ELEMENTS_TEXT() do in Postgres?
- PostgreSQL JSON_ARRAY_ELEMENTS VS JSON_ARRAY_ELEMENTS_TEXT

What Does JSON_ARRAY_ELEMENTS() Do in Postgres?

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. Use the below-stated syntax to retrieve an expanded set of specified JSON array elements:

JSON_ARRAY_ELEMENTS(array JSON);

What Does JSON_ARRAY_ELEMENTS_TEXT() Do in Postgres?

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. Use the below-stated syntax to retrieve an expanded set of text values from the specified JSON array:

JSON_ARRAY_ELEMENTS_TEXT(array JSON);

PostgreSQL JSON_ARRAY_ELEMENTS VS JSON_ARRAY_ELEMENTS_TEXT

Both these functions are used to expand the top-level elements of the given JSON array into a set of values. The only distinction between these functions is that the JSON_ARRAY_ELEMENTS() function retrieves the expanded values in JSON type while the JSON_ARRAY_ELEMENTS_TEXT() function retrieves the same output in TEXT type.

Here is a side-by-side practical demonstration of these methods:

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

The below snippet demonstrates that both functions generate the same output, with the only distinction being their return type:

That’s all about the difference between the JSON_ARRAY_ELEMENTS() and the JSON_ARRAY_ELEMENTS_TEXT() functions in PostgreSQL.

Conclusion

In PostgreSQL, the JSON_ARRAY_ELEMENTS() and the JSON_ARRAY_ELEMENTS_TEXT() functions are used to expand the top-level elements of the given JSON array into a set of values. The only distinction between these functions is that the JSON_ARRAY_ELEMENTS() function retrieves the expanded values in JSON type while the JSON_ARRAY_ELEMENTS_TEXT() function retrieves the same output in TEXT type. This write has presented the difference between JSON_ARRAY_ELEMENTS and the JSON_ARRAY_ELEMENTS_TEXT functions using a practical example.