15.10 - JSON_TABLE - Teradata Database

Teradata Database Teradata JSON
Product
Teradata Database
Release Number
15.10
Published
December 2015
Language
English (United States)
Last Update
2018-06-05
dita:mapPath
TeradataJSON_15_10.ditamap
dita:ditavalPath
ft:empty

Purpose

JSON_TABLE creates a temporary table based on all, or a subset, of the data in a JSON object.

Syntax



Syntax Elements

json_documents_retrieving_expr
A query expression that must result in at least two columns. The first column is an ID that identifies a JSON document. The ID is returned in the output row to correlate output rows to the input rows, and in case of errors, it is used to identify the input row that caused the error. The second column is the JSON document itself. Other columns can be included, but they must be after the JSON document column. Extra columns are returned in the output row without being modified from the input.
This is an example of the json_documents_retrieving_expr. 
SELECT id, jsonDoc FROM jsonTable;
This is an example json_documents_retrieving_expr query using extra columns. The extra columns are returned by the table operator and are output as they are.
SELECT id, orderJson, orderDate, orderSite FROM orderJsnTable;
If the json_documents_retrieving_expr parameter is NULL, the result of the function is a table with no rows.
row_expr_literal
A LATIN or UNICODE string literal that conforms to JSONPath syntax.
This parameter is the JSONPath query that is evaluated against the JSON document returned in the second column of the json_documents_retrieving_expr.
The row_expr_literal query returns an array of objects. One output row is returned for each object in the array.
If row_expr_literal is NULL an error is reported.
column_expr_literal
A LATIN or UNICODE literal that represents a JSON array. This array contains one or more JSON objects, each of which defines a column to be produced by the JSON_TABLE operator.
If this parameter is NULL an error is reported.
AS
Optional keyword introducing correlation_name.
correlation_name
An alias for the table that is referenced by json_documents_retrieving_expr.
ANSI SQL refers to table aliases as correlation names. They are also referred to as range variables.
column_name
An optional list of one or more column names.

Functional Description

JSON_TABLE takes a JSON instance and creates a temporary table based on all, or a subset, of the data in the instance. The JSON instance is retrieved from a table with an SQL statement. JSONPath syntax is used to define the portions of the JSON instance to retrieve.

Return Value

The JSON_TABLE table operator produces output rows which conform to the row format defined by the column_expr_literal. That literal describes the columns in the output row and their data types.

The rows returned by JSON_TABLE have the following columns:
  • The first column returned contains the JSON document ID obtained from the first column in the json_documents_retrieving_expression.
  • The next N columns returned are generated based on the colexpr parameter, where N is the number of objects in the JSON array represented by the column_expression_literal.
  • If json_documents_retrieving_expr returns more than two columns, all the extra columns from the third column onward are added to the output row without being modified.
The column_expr_literal parameter requires a mapping of the columns in the row_expr_literal to the columns of the output table of this function. Each column in the column_expr_literal is defined by a JSON instance that must conform to one of the following structures.
Ordinal Column
An Ordinal Column contains an integer sequence number. The sequence number is not guaranteed to be unique in itself, but the combination of the id column, the first column of the output row, and the ordinal column is unique.
{ "ordinal" : true}
Normal Column Relative to Row
The value of this column is determined by executing a JSONPath query. The query is evaluated with an object returned by the ROWEXPR JSONPath query as the root object.
{“jsonpath” : “<jsonpath query string>”,
  “type” :  “<data type of output column>” }
Normal Column Relative to Root
The value of this column is determined by executing a JSONPath query. The query is evaluated with the object returned by json_documents_retrieving_expression, the second column, as the root object. This type of column is identified by setting "fromRoot" to true.
{“jsonpath” : “<jsonpath query string>”,
  “type” :  “<data type of output column>”,
  “fromRoot”:true }

JSON_TABLE does not support UDTs and LOB types in the output so the JSON data type cannot be the type for the output columns. The data type of the columns of the output table may be any non-LOB predefined Teradata type. The supported data types for JSON_TABLE output are listed next.

Supported Data Types

The following lists the supported data types for JSON_TABLE output. The square brackets indicate optional values in the type specification.
CHAR(n)
VARCHAR(n)
BYTE(n)
VARBYTE(n)
BYTEINT
SMALLINT
INTEGER
FLOAT/REAL
DECIMAL/NUMERIC [(n,[m])]
NUMBER
DATE
TIME [(fractional_seconds_precision)]
TIME [(fractional_seconds_precision)] WITH TIME ZONE
TIMESTAMP [(fractional_seconds_precision)]
TIMESTAMP [(fractional_seconds_precision)] WITH TIME ZONE
INTERVAL YEAR [(precision)]
INTERVAL YEAR [(precision)] TO MONTH
INTERVAL MONTH [(precision)]
INTERVAL DAY[(precision)]
INTERVAL DAY[(precision)] TO HOUR
INTERVAL DAY[(precision)] TO MINUTE
INTERVAL DAY [(precision)] TO SECOND
INTERVAL HOUR [(precision)]
INTERVAL HOUR [(precision)] TO MINUTE
INTERVAL HOUR [(precision)] TO SECOND
INTERVAL MINUTE [(precision)]
INTERVAL MINUTE [(precision)] TO SECOND
INTERVAL SECOND [(fractional_seconds_precision)]

Rules and Restrictions

This function resides in TD_SYSFNLIB. The ID column of the query result can be any number type or character type excluding CLOB. It is the responsibility of the user to make the ID column unique. If it is not unique JSON_TABLE does not fail, but it is difficult to tell which rows came from which JSON document in the resulting output.

The result is subject to the maximum row size, and the query must not exceed the maximum allowable size for a query.

When a JSON value is shredded to populate a CHAR, VARCHAR, or VARBYTE column, if the size of the value is larger than the size of the target column, the value is truncated to fit the column.