Purpose
The JSON_AGG function returns a JSON document composed of aggregated values from each input parameter. The input parameters can be a column reference or an expression. Each input parameter results in a name/value pair in the returned JSON document.
Syntax
Syntax Elements
- param
- An input parameter that can be any supported data type, a column reference, constant, or expression that evaluates to some value. A variable number of these parameters are accepted and each input parameter results in a name/value pair in the returned JSON document.
- FORMAT 'format string'
- format string is any allowable format string in Teradata.
- AS name
- name is any allowable name in Teradata.
- RETURNS data_type
- Specifies that data_type is the return type of the function.
- integer
- A positive integer value that specifies the maximum length in characters of the JSON type. If specified, the length is subject to a minimum of two characters and cannot be greater than the absolute maximum allowed for the function. Shorter lengths may be specified.
- CHARACTER SET UNICODE | LATIN
- The character set for the data type in the RETURNS data_type clause.
- 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.
Return Value
By default, JSON_AGG returns a JSON document in character set UNICODE and a maximum length of 32000 UNICODE characters (64000 bytes), unless otherwise specified with the optional RETURNS clause.
{ name1 : data1, name2 : data2, ..., nameN : dataN, }
If one of the values used to compose the JSON document is a Teradata NULL, it is returned in the JSON instance as a JSON null.
Rules and Restrictions
- Numeric data types in Teradata map to a number in JSON.
- Nulls in Teradata map to nulls in JSON.
- All other data types in Teradata map to a string in JSON.
You cannot use structured UDTs with LOB attributes as input.
JSON_AGG writes out the values of all attributes of a structured UDT in a comma-separated list enclosed in parenthesis. For example: (att1Val, att2Val, ..., attNVal). If this cannot be done in 64000 bytes, an SQL error is reported and the query fails.
Input character data can contain escape characters that have not been correctly escaped to conform to JSON syntax. When the function encounters these unescaped characters, it will encode the input so that it conforms to proper JSON syntax. However, a syntax error will still be returned if after escaping, the truncated data results in incorrect JSON syntax.
All non-predefined types, such as all UDTs, use their transformed value to populate the resulting JSON document. The user must provide a transform that outputs data in valid JSON syntax in order to function properly. Otherwise, validation of the JSON instance will fail and the function returns an error message. However, as stated above, the function will accept unescaped characters and will not return an error in this case.
The data type in the JSON value is determined according to the mapping above, based on the predefined data type of the result of the transform. All non-LOB predefined types are formatted according to the optional FORMAT clause specified for any particular column, or if that is not present, the default format for the particular data type.
The param name can be specified using the optional "AS" clause for each parameter. If the optional portion is NULL, the names of the parameters that make up the resulting JSON document are given according to current naming conventions of expressions in Teradata.
Usage Notes
The GROUP BY clause can be used in the SELECT statement which invokes the JSON_AGG function. Existing rules for the GROUP BY clause and aggregate functions apply to JSON_AGG. When this is used, the resulting JSON document is structured as an array with objects as its elements that represent members of the resulting group. Each group is in a different output row.
If one of the values used to compose the JSON object is a Teradata NULL, it is returned in the JSON instance as a JSON null.