JSON_PUBLISH

Teradata Vantageā„¢ JSON Data Type

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

Purpose

JSON_PUBLISH is a table operator that is used to compose a JSON data type instance or instances from a variety of data sources, basically anything that can be referenced in an SQL statement. It can publish data into any supported JSON format, including the binary storage formats. JSON_PUBLISH also has the ability to aggregate data larger than 64K into a JSON document.

Syntax

JSON_PUBLISH uses the standard table operator syntax. For details, see the table operator syntax described as part of the SELECT statement in Teradata Vantageā„¢ SQL Data Manipulation Language.

Optional Custom Clauses

Two optional custom clauses are available to provide users with the flexibility needed to compose JSON data type instances in the exact format they desire:
DO_AGGREGATE
Causes the output instance(s) to be composed of one row of input data. This clause accepts either 'Y' or 'N' (not case specific) as follows:
  • 'Y' specifies that the result is aggregated. This is the default.
  • 'N' specifies that the result is not aggregated.

Excluding this clause causes JSON_PUBLISH to aggregate all data corresponding to a particular group (as defined in the optional GROUP BY clause in the SELECT statement of the ON clause) into one JSON data type instance. Note that the absence of this custom clause and of a GROUP BY clause causes all input rows to be grouped together in one resulting instance. If the result of aggregating the data into a JSON data type instance results in a size overflow based on the maximum size specified, an error accompanied by an appropriate error string is reported.

WRITE_ARRAY
Causes the output instance(s) to not be JSON arrays at the top level. Therefore, the default is to return a JSON array composed of one or more JSON documents which represent the published data. The only value accepted by this clause is the character 'N' (not case specific).

If you do not group the published data into a JSON array, but failure to do so would violate JSON syntax, an error accompanied by an appropriate error string is reported.

Return Value

JSON_PUBLISH returns one output column called data, which is the resulting JSON document. The return type of this table operator is always a maximum size JSON data type.

JSON_PUBLISH can compose data into a JSON data type of any character set or storage format using the RETURNS clause of the table operators. The default is to return maximum size JSON CHARACTER SET LATIN instances.