15.10 - column expression - Teradata Database

Teradata Database Teradata JSON

prodname
Teradata Database
vrm_release
15.10
created_date
December 2015
category
Programming Reference
featnum
B035-1150-151K

The following describes the variables used by column expression.

"colexpr":
Required, literal entry.
Must be lowercase.
temp_column_name
Any user-defined name for the temporary column. The temporary column name must be unique or an error is reported.
The names are not case sensitive. For example, col1 and COL1 will fail because they are used in internal queries and are not unique.
You cannot use JSONID and ROWINDEX (uppercase or lowercase) here.
JSONPath expr
A JSONPath expression in JSONPath syntax that requests information about a particular portion of a JSON object. For example, $.name.
"type":
Required, literal entry.
Must be lowercase.
data_type
Non-LOB Teradata predefined type, such as INTEGER or VARCHAR. For a list of supported types, see Supported Data Types.
"fromRoot" : true
Optional. You must use the fromRoot attribute for non-relative paths. Each column expression is assumed to be relative to the row expression ("rowexpr": "JSONPATH expression"), unless specified as not relative. A relative expression starts with one of the names of a child of the row expression, whereas a non-relative expression starts with the root in JSONPath, $.
true is a literal and must be lowercase.
Attempting to set fromRoot to false raises an error.

The user is responsible for mapping elements of the column expression to acceptable data types. No explicit casting is needed; the data is implicitly cast to the desired data type. However, if the data does not correctly cast to the desired data type an error is reported, therefore, care should be taken when determining the elements and data types of the column expression. If the result of the expression is an array or object (instead of a single value), the only acceptable data types are CHAR or VARCHAR of suitable length.

The data type of the temporary columns in the output table of JSON_TABLE must be specified. This is enforced with JSON_SHRED_BATCH and JSON_SHRED_BATCH _U in the column expression. It is necessary for the user to provide this information so that the data may be correctly interpreted and used with the target table(s).