JSON_COMPOSE

Teradata Vantageā„¢ JSON Data Type

prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
Programming Reference
featnum
B035-1150-162K

Purpose

JSON_COMPOSE creates a JSON document composed of the input parameters specified. This function provides a complex composition of a JSON document when used in conjunction with the JSON_AGG function.

Syntax



If you specify a RETURNS clause, you must enclose the function call in parenthesis.





param
An input parameter that can be 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.
You cannot use structured UDTs with LOB attributes as input.
FORMAT 'format string'
format string is any allowable format string in Teradata.
For an example using the format string see Example: Use JSON_COMPOSE with Subqueries and GROUP BY.
AS name
name is any allowable name in Teradata.
The string created conforms to the JSON standard escaping scheme. A subset of UNICODE characters are required to be escaped by the '\' character. This is not the case for strings in Teradata. Thus, when porting a Teradata string to a JSON string, proper JSON escape characters are used where necessary. This also applies to the values of the JSON instance and to the JSON_AGG function. If the character set is LATIN, '\' escaped characters must be part of that character set; otherwise a syntax error is reported.
RETURNS data_type
Specifies that data_type is the return type of the function.
data_type must be JSON for this 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.
As an aggregate function, JSON_AGG supports up to 64000 bytes, which is 32000 UNICODE characters or 64000 LATIN characters. The RETURNS clause can specify a larger return value, but the actual data returned by JSON_AGG is 64000 bytes. If the data length is greater than this an error is returned. Note, JSON_COMPOSE can specify larger values than JSON_AGG.
If you do not specify a RETURNS clause, the return type defaults to JSON(32000) CHARACTER SET UNICODE. In other words, the default return type is a JSON data type with UNICODE character set and a return value length of 32000 characters.
CHARACTER SET UNICODE | LATIN
The character set for the data type in the RETURNS data_type clause.
The character set can be LATIN or UNICODE.
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.

Return Value

By default, JSON_COMPOSE returns a LOB based JSON document with character set UNICODE and a maximum length of 32000 UNICODE characters (64000 bytes), unless otherwise specified with the optional RETURNS clause.

A hierarchical relationship is not possible with this function. The resulting JSON document is flat, with each input parameter corresponding to one child of the result. The resulting document will be in this format:
{
   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

The input parameter may be any data type allowed in Teradata. Predefined types are mapped to JSON data types according to this mapping:
  • 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_COMPOSE 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 will be 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 will be given according to current naming conventions of expressions in Teradata.

Usage Notes

JSON_COMPOSE is most useful when used in conjunction with JSON_AGG. JSON_AGG is limited in that it provides groups as identified by the GROUP BY clause, but it does not provide the value that was used to create the group. To obtain this, use JSON_AGG in a subquery that results in a derived table, and reference the result of JSON_AGG as one of the parameters to the JSON_COMPOSE function. To ensure the values being grouped on are included with the proper groups, the columns used in the GROUP BY clause of the subquery with the JSON_AGG function should be used as parameters to the JSON_COMPOSE function along with the result of JSON_AGG. In this way, the values being grouped on will be included alongside the group.