In PostgreSQL, the JSON array data type is used to store ordered collections in JSON format. It is similar to standard arrays in programming. PostgreSQL offers several built-in JSON functions to manipulate JSON data efficiently, such as ARRAY_TO_JSON(), JSON_ARRAY_ELEMENTS(), JSON_ARRAY_LENGTH(), and many more.

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

How to Use the ARRAY_TO_JSON() Function in PostgreSQL?

The array_to_json() is a built-in JSON function that accepts a SQL array as an argument and converts it into a JSON array.

Syntax

Use the below-stated syntax to retrieve a converted JSON array from the specified SQL array:

ARRAY_TO_JSON(array ARRAY, pretty BOOLEAN);

Parameters

The stated function accepts a mandatory argument “array” that represents an SQL array. Moreover, it can accept an optional argument “pretty” to beautify the resultant JSON ARRAY.

Return Value

It retrieves a JSON array.

Example 1: Using ARRAY_TO_JSON()

This example demonstrates the usage of the PostgreSQL array_to_json() function to convert the given SQL array into a JSON array:

SELECT ARRAY_TO_JSON('{100, 12, 53, -1, 24}'::int[]) AS converted_json_array;

The specified array has been successfully converted into a JSON array:

Example 2: Using ARRAY_TO_JSON() on Multidimensional Array

This example illustrates the usage of the PostgreSQL array_to_json() function on a multi-dimensional array:

SELECT ARRAY_TO_JSON('{{"John", "Joseph"}, 
 {"Henry", "Anna"}}'::TEXT[]) AS converted_json_array;

The specified array has been successfully converted into a JSON array:

Example 3: Using ARRAY_TO_JSON() With Pretty Parameter

This example uses the true value for the “pretty” parameter to beautify the resultant JSON ARRAY.

SELECT ARRAY_TO_JSON('{{"John", "Joseph"}, 
 {"Henry", "Anna"}}'::TEXT[], pretty) AS converted_json_array;

The output snippet demonstrates that the converted array has been beautified successfully:

That was all about the ARRAY_TO_JSON() function in Postgres.

Conclusion

The array_to_json() is a built-in JSON function that accepts a SQL array as an argument and converts it into a JSON array. The stated function accepts a mandatory argument “array” that represents an SQL array and an optional argument “pretty” to beautify the resultant JSON ARRAY. It retrieves a JSON array. This post has illustrated several use cases of the ARRAY_TO_JSON() function in PostgreSQL.