JSON Composition Using SELECT AS JSON

Teradata Vantageā„¢ JSON Data Type

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

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 .

Examples Using TOP n or ORDER BY

If you specify ORDER BY in the SELECT statement, you must explicitly specify the field being ordered as shown in the following query:
SELECT AS JSON pkey, val FROM MyTable ORDER BY pkey ASC;
Similarly, if you specify TOP n in the SELECT statement, you must explicitly specify the field being sorted:
SELECT AS JSON TOP 2 pkey, val FROM MyTable ORDER BY pkey DESC;
The following query fails because comparisons are not allowed for the JSON type.
SELECT AS JSON a, b FROM MyTable ORDER BY 1 ASC;
The following query fails because sorting is not supported for the JSON type.
SELECT AS JSON TOP 10 a, b FROM MyTable ORDER BY 1 DESC;