{ JSON_COMPOSE ( param_spec [,...] ) |
( JSON_COMPOSE ( param_spec [,...] ) RETURNS returns_clause )
}
Syntax Elements
- param_spec
param [ (FORMAT 'format_string') ] [ AS name ]
- returns_clause
{ data_type [ (integer) ] [ CHARACTER SET { UNICODE | LATIN } ] |
STYLE column_expr
}
- 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 Vantage.
- 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 Vantage.
- 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 Vantage. Thus, when porting a Vantage 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.