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.
Rules and Restrictions
- Numeric data types in Vantage map to a number in JSON.
- Nulls in Vantage map to nulls in JSON.
- All other data types in Vantage 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 Vantage.