15.10 - input query - Teradata Database

Teradata Database Teradata JSON

prodname
Teradata Database
vrm_release
15.10
created_date
December 2015
category
Programming Reference
featnum
B035-1150-151K
input query
A string input parameter which specifies a query that results in a group of JSON instances from which the user can perform shredding. Extra columns can result and be referred to in the shred statement.
If this parameter is NULL, an error is reported.

The input query parameter can operate on one or more JSON objects in a source table. The user invoking JSON_SHRED_BATCH must have SELECT privilege on the source table. The input query is mapped to a JSON_TABLE function call. Since JSON_TABLE requires that the first two columns specified be an ID value and a JSON object, respectively, the input query parameter also requires the first two columns to be an ID value and a JSON object.

The following are examples of an input query string.

'SELECT id, empPersonalInfo, site FROM test.json_table'
'SELECT JSONDOCID, JSONDT1, a, b FROM jsonshred.JSON_TABLE3 WHERE JSONID=100'

JSONID (uppercase or lowercase) is a keyword. It is a temporary column name used for the JSON document ID value. JSONID is allowed in the input query and table expression clauses. You cannot use JSONID as a temp_column_name in "colexpr" or "queryexpr".

The execution of JSON_TABLE on multiple JSON objects requires a join between the result of one invocation and the source table. In order to avoid a full table join, we require an ID column to be specified in the input query parameter, so that a join condition can be built off that column.

The data types in the queryexpr (discussed later) must match the actual data type of the columns specified in the input query. No explicit cast will be added, so the data must be implicitly castable to the data type defined in the query expr, if not the exact data type. Any errors encountered will result in a failed shred, and will be reported to the user.

If there is a problem encountered during the execution of JSON_TABLE, the ID column is used in the error message to isolate which row caused the problem.