ARRAY_TO_JSON can be particularly powerful when used in conjunction with the ARRAY_AGG function, which allows columns of a table to be aggregated into an ARRAY object. You can then use ARRAY_TO_JSON to convert the aggregated ARRAY into a JSON array.
Rules and Restrictions
If the ARRAY type is based on a user-defined type (UDT), you must provide a transform that outputs data in valid JSON syntax in order to use the ARRAY_TO_JSON function. Otherwise, validation of the JSON array will fail and the function returns an error.