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 a value used to compose the JSON object is a Vantage NULL, that value is returned in the JSON instance as a JSON null.
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.
Input character data can contain escape characters that have not been correctly escaped to conform to JSON syntax. When encountering these unescaped characters, the function encodes the input to conforms to proper JSON syntax. However, if after escaping, the truncated data causes incorrect JSON syntax, a syntax error is returned.
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 to function properly. Otherwise, validation of the JSON instance fails and the function returns an error message. However, the function accepts unescaped characters and does not return an error.
The data type in the JSON value is determined according to the preceding mapping, 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 the column, or if that is not present, the default format for the 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 Vantage.