15.10 - ARRAY_TO_JSON - Teradata Database

Purpose

The ARRAY_TO_JSON function converts a Teradata ARRAY type to a JSON type composed of an ARRAY.

Syntax Elements

TD_SYSFNLIB

The name of the database where the function is located.

ARRAY_expr

An expression that evaluates to an ARRAY data type.

ARRAY_expr specifies the array to be converted to the JSON type.

RETURNS data_type

Specifies that data_type is the return type of the function.

data_type can only be JSON.

integer

A positive integer value that specifies the maximum length in characters of the JSON type.

If you do not specify a maximum length, the default maximum length for the character set is used. If specified, the length is subject to a minimum of two characters and cannot be greater than the maximum of 16776192 LATIN characters or 8388096 UNICODE characters.

CHARACTER SET

The character set for the return value of the function, which can be LATIN or UNICODE.

If you do not specify a character set, the default character set for the user is used.

RETURNS STYLE column_expr

Specifies that the return type of the function is the same as the data type of the specified column. The data type of the column must be JSON.

column_expr can be any valid table or view column reference.

Functional Description

The ARRAY_TO_JSON function only converts Teradata ARRAY types to the JSON type stored as text.

The ARRAY data type is mapped to a JSON-formatted string composed of an array, which can also be a multidimensional array. If the data type of the ARRAY is a numeric predefined type, the array element maps to a numeric type in the JSON instance. For all other types, the value added to the JSON instance is the transformed value of each element of the ARRAY, which is stored in the JSON instance as a string. Note, the JSON array should have the same number of elements as the ARRAY type.

Return Value

The return type of this function is JSON.

You can use the RETURNS clause to specify the maximum length and character set of the JSON type.

If you do not specify a RETURNS clause, the return type defaults to JSON data type with UNICODE character set and a return value length of 64000 bytes, which supports up to 32000 UNICODE characters.

ARRAY_TO_JSON returns NULL if the ARRAY_expr argument is null.

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.

Usage Notes

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.