15.10 - Using the JSON Type with UDFs - Teradata Database

Teradata Database Teradata JSON

prodname
Teradata Database
vrm_release
15.10
created_date
December 2015
category
Programming Reference
featnum
B035-1150-151K
You can create a UDF containing one or more parameters and return type that are a JSON data type. The JSON type is supported for the following types of UDFs:
  • Scalar, aggregate, and table functions written in C or C++
    The JSON type is not supported for C/C++ table operators.
  • Scalar, aggregate, table functions, and table operators written in Java.
  • SQL UDFs

For SQL UDFs, the RETURN clause can be an SQL statement that evaluates to the JSON data type.

The JSON data type is mapped to a java.sql.Clob type in a Java routine. The JSON data type is converted into a string format and passed as a CLOB to the Java UDF or external stored procedure, which must be defined using java.sql.Clob as the parameter for the JSON type.

If a binary JSON column value is passed to a Java UDF or external stored procedure defined with a JSON type parameter, the binary JSON value is converted to a CLOB type and mapped to java.sql.Clob.

Using JSON Type with Java Table Operators

A Java table operator input stream can contain a JSON type column. The JSON column is passed as a CLOB value to the table operator. Similarly, for a JSON output column, the JSON column value is sent as a CLOB value. If the table operator output is used for an insert into a table containing JSON types, then implicit casting from the CLOB column to JSON will occur.

You can get or set metadata about a JSON column for input and output tables in the table operator contract function. The JSON_DT type code is used to identify a JSON input or output column. For example, the contract function can specify a JSON output column using the JSON_DT type code; however, the JSON column value will still be sent as a CLOB value.

Whenever the JSON_DT type is found, the contract function should get or set the column definitions similar to CLOB columns because the column value is still passed as CLOB to the table operator in transformed form.