TD_JSONSHRED Table Operator Syntax | Teradata Vantage - TD_JSONSHRED Syntax - Advanced SQL Engine - Teradata Database

[TD_SYSFNLIB.]TD_JSONSHRED(
  ON (json_document_retrieving_expr)
  USING 
      ROWEXPR(row_expr_literal) 
      COLEXPR(column_expr_literal [,...])
      RETURNTYPES(return_type [,...])
      [NOCASE(nocase_value)]
      [TRUNCATE(truncate_value)]
) [AS] correlation_name [(column_name [,...])]

Syntax Elements

json_documents_retrieving_expr

A query expression that returns at least two columns:

• The first column serves as an identifier for the row and a way to correlate the rows in the returned table to the data in a particular input JSON document. It also serves as the primary index for the returned table, and can be any non-LOB data type. Best practice is to have it be a unique value for each input row, which corresponds to each individual JSON document to be processed by the function.

  This column is returned unchanged (passed through) by TD_JSONSHRED as the first column in the result set.

• The last column contains the JSON document to be shredded.

  It can have data type JSON, CLOB, or VARCHAR. CLOB allows for a JSON document input as large as 2GB.

The json_documents_retrieving_expr can return additional columns between the first id column and the last JSON document column. These additional columns are passed through by the function, and returned by TD_JSONSHRED unaltered from their input values. They appear in the returned table before the columns of shredded JSON data.

If json_documents_retrieving_expr returns no results or NULL, TD_JSONSHRED returns a table with no rows.

row_expr_literal

The identifier of the JSON object that contains the data to be shredded. TD_JSONSHRED shreds the contents of the identified object into rows of the returned (output) table. If multiple shredded objects have the same identifier, their contents are returned in different rows of returned table.

An empty string signifies that TD_JSONSHRED should shred the entire contents of the input JSON documents.

row_expr_literal does not support full JSONPath notation to locate and identify JSON objects within the hierarchical structure of the input JSON document. Instead, you can use a simplified dot notation.

Unlike JSONPath, this simplified dot notation does not use a $ to represent an outer-level object, and it does not support wildcards or expressions. It identifies objects and outer-level arrays within the JSON document. Simple dot notation can identify nested JSON objects, but it cannot identify specific objects within an array.

column_expr_literal

The identifier of a JSON object within the shredded data. The value of the object becomes the value of a column in the returned table. By default, the object identifier is used as the column name. If multiple objects in the shredded data have the same identifier, their values become the column values in multiple rows of the output table.

An empty string means all objects in the shredded data are returned in a single column.

column_expr_literal does not support full JSONPath notation to locate and identify JSON objects within the hierarchical structure of the input JSON document. Instead, you can use a simplified dot notation.

Unlike JSONPath, this simplified dot notation does not use a $ to represent an outer-level object, and it does not support wildcards or expressions. It identifies objects and outer-level arrays within the JSON document. Simple dot notation can identify nested JSON objects, but it cannot identify specific objects within an array.

return_type

The data type assigned to a column of shredded JSON data in the returned derived table.

• The number of return types must correspond to the number of columns specified for COLEXPR. (This does not include any pass-through columns of non-JSON data in the original input table.)
• The output type must be enclosed in single quotation marks (apostrophes).
• Valid output data types are DECIMAL/NUMERIC, BYTEINT, SMALLINT, INTEGER, BIGINT, REAL, FLOAT, DOUBLE PRECISION, DATE, VARCHAR, and CLOB.
  The maximum string size for shredded JSON data is 16 MB.
• You can include a format clause for each type.
• If you require output of a different type, you can output any column as a VARCHAR, and cast it to another data type.
• The maximum length specified for a VARCHAR type column must accommodate the data shredded to that column.
• For VARCHAR and CLOB data, the default output character set will match the input character set.

nocase_value

Determines whether string matching for COLEXPR and ROWEXPR is case sensitive. The value can be 0 or 1:

• 0 means string matching is case sensitive. This is the default.
• 1 means string matching is not case sensitive.

truncate_value

Determines how TD_JSONSHRED handles shredded data that exceeds the corresponding RETURNTYPE specification. The value can be 0 or 1:

• 0 means TD_JSONSHRED fails with an error. The error indicates the problematic column.
• 1 means TD_JSONSHRED returns data that is truncated to match the RETURNTYPE specification. This is the default.

correlation_name

A name used as an alias for the derived table returned by TD_JSONSHRED.

column_name

Name for a column in the derived table. If you do not specify a column name, TD_JSONSHRED uses the JSON object names. The number of column names must match the number of columns in the derived table.

If more than one output column comes from JSON objects with identical names, you must use the column_name parameter to assign different names to the output columns.