Teradata JSON
This feature provides new functionality to support the storage and processing of JSON
(JavaScript Object Notation) data in Teradata Database:
A JSON data type, stored as a JSON document or as JSON records in relational format
Methods, functions, and procedures for processing, shredding, and publishing JSON
data
JSON documents up to 16MB in size
JSONPath support provides simple traversal and regular expressions with wildcards
to filter and navigate complex JSON documents
Benefits
JSON shredding functionality allows you to extract values from JSON documents and
store the extracted data in relational format.
JSON publishing functionality allows you to publish the results of SQL queries in
JSON format.
Existing Teradata join indexing structure is supported for extracted portions of the
JSON data type to provide superior performance.
Teradata provides functionality to convert an ST_Geometry object into a GeoJSON value
and a GeoJSON value into an ST_Geometry object.
Statistics can be collected on extracted portions of the JSON data type.
Teradata JSON supports schema-less or dynamic schema with the ability to add a new
attribute without changing the schema. Data with new attributes are immediately available
for querying. Rows without the new column can be filtered out.
Considerations
To use Teradata SQL Assistant with the JSON feature, you must use the 15.0 version
of SQL Assistant.
Only UNICODE and LATIN character sets are available for the JSON type.
The JSON type accepts all available session character sets. The data is translated
to either the UNICODE or LATIN character set, depending on the definition of the JSON
type instance where the data is being used. Any characters used must be translatable
to either UNICODE or LATIN.
Any character set that requires translation is subject to the size restrictions of
the UNICODE or LATIN character sets.
JSON cannot be used in conjunction with FastLoad/MultiLoad/FastExport due to restrictions
on all LOB types. However, if the JSON size is less than 64K, it can be cast to a
predefined type and FastExport can be used. You can load the table with a VARCHAR
column and then do an insert-select to the JSON table.
A JSON type cannot be used in a GROUP BY, ORDER BY, PARTITION BY, WHERE, ON, SET,
DISTINCT, HAVING, QUALIFY, IN, CUBE, GROUPING SETS, or ROLLUP clause.
JSON data types may be used as the attribute of an ANSI Structured UDT, but not as
the base type of an ANSI Distinct UDT or as the element type of a Teradata ARRAY type.
There is no automatic mechanism provided to convert XML to JSON.
For More Information
See Teradata JSON.