You can use the SELECT AS JSON statement to compose data from table columns into a JSON document. The SELECT statement returns a single JSON column named "JSON".
The following query generates a JSON document from the values of the pkey and val columns in the table MyTable:
SELECT AS JSON pkey, val FROM MyTable;
The above query is equivalent to the following query which uses the JSON_COMPOSE function to generate the JSON document:
SELECT JSON_COMPOSE(pkey, val) FROM MyTable;
Rules for Using SELECT AS JSON
- The SELECT AS JSON statement composes column values into a text format JSON document. Binary formats such as BSON or UBJSON are not supported.
- You cannot specify SELECT AND CONSUME together with SELECT AS JSON to compose JSON documents. For example, the following query fails:
SELECT AS JSON AND CONSUME TOP 1 a, b FROM MyQueueTable;
- If you specify TOP n or ORDER BY in the SELECT statement, you must explicitly specify the sorted fields because you cannot sort by the JSON column.
For more information about the SELECT AS JSON statement, see "Select Statements" in Teradata Vantage™ - SQL Data Manipulation Language, B035-1146.