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

JSON Data Type

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
September 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
gzn1554761068186.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1150
lifecycle
previous
Product Category
Teradata Vantageā„¢
[TD_SYSFNLIB.]JSON_TABLE(
  ON (json_documents_retrieving_expr)
  USING 
      ROWEXPR (row_expr_literal) 
      COLEXPR (column_expr_literal)
  [AS] correlation_name [(column_name [,...])]
)

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.
The column containing the JSON document can be of any of these data types: CHAR, VARCHAR, CLOB, BYTE, VARBYTE, or BLOB.
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 JSONPath query expression that returns an array of objects from the JSON document returned by json_documents_retrieving_expr. JSON_TABLE returns one row for each object in the array.
row_expr_literal cannot be NULL.
column_expr_literal
A JSONPath query expression that identifies individual object in the JSON array returned by row_expr_literal. Each object will be represented by a column in the returned table.
column_expr_literal cannot be NULL.
The syntax of column_expr_literal includes a JSONPath query and special name:value pairs that help JSON_TABLE convert the objects into column values in the returned table.
These name:value pairs are case-sensitive.
  • "jsonpath" : JSONPath_query_expression

    The query that extracts the value of this column from the JSON document resulting from the row_expr_literal expression.

    Alternatively, the value of the column can also be extracted from the root of the original JSON document if you use the fromRoot : true name-value pair described below.

  • "type" : data_type_of_output_table_column

    Specifies the Vantage data type that will be assigned to the output column. It must be specified for every column of shredded JSON data in the returned table.

    The data type of the columns of the output table may be any non-LOB predefined Vantage type. JSON_TABLE does not support UDTs and LOB types in the output, so the Vantage JSON data type itself cannot be a type assigned to any of the JSON data output columns. For a list of the supported data types, see Supported Output Data Types below.

  • "ordinal" : true

    An optional attribute that indicates that the column in the returned table will be an ordinal column. Each row in 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.

  • "fromRoot" : true

    An optional attribute that indicates the JSONPath query is evaluated with the object returned by json_documents_retrieving_expression, as the root object. Otherwise the JSONPath query is evaluated using the object returned by row_expr_literal as the root object.

    This allows you to include data from the original JSON document in the output table in addition to data from the array returned by row_expr_literal.

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.