Teradata Support for JSON

Teradata Vantage™ JSON Data Type

brand
Software
Teradata Vantage
prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
Programming Reference
featnum
B035-1150-162K

JSON (Javascript Object Notation) is a data interchange format, often used in web applications to transmit data. JSON has been widely adopted by web application developers because compared to XML it is easier to read and write for humans and easier to parse and generate for machines. JSON documents can be stored and processed in Teradata Database.

Teradata Database can store JSON records as a JSON document or store JSON records in relational format. Teradata Database provides the following support for JSON data:
  • Ability to store JSON data in text and binary (BSON, UBJSON) storage formats.
  • Methods, functions, and stored procedures that operate on the JSON data type, such as parsing and validation.
  • Shredding functionality that allows you to extract values from JSON documents up to 16MB in size and store the extracted data in relational format.
  • Publishing functionality that allows you to publish the results of SQL queries in JSON format.
  • Schema-less or dynamic schema with the ability to add a new attribute without changing the schema. Data with new attributes is immediately available for querying. Rows without the new column can be filtered out.
  • Use existing join indexing structures on extracted portions of the JSON data type.
  • Apply advanced analytics to JSON data.
  • Functionality to convert an ST_Geometry object into a GeoJSON value and a GeoJSON value into an ST_Geometry object.
  • Allows JSON data of varying maximum length and JSON data can be internally compressed.
  • Collect statistics on extracted portions of the JSON data type.
  • Use standard SQL to query JSON data.
  • JSONPath provides simple traversal and regular expressions with wildcards to filter and navigate complex JSON documents.

Requirements

To enable JSON support in Teradata Database, the DBS Control field EnableJSON must be set to TRUE. This is the default setting. For more information, see Teradata Vantage™ - Database Utilities , B035-1102 .

Client Support for JSON

The following table describes the support provided by the Teradata Database client products for the JSON data type.

Client Product JSON Support Provided
CLI Full native DBS support.
ODBC

The ODBC specification does not have a unique data type code for JSON. Therefore, the ODBC driver maps the JSON data type to SQL_LONGVARCHAR or SQL_WLONGVARCHAR, which are the ODBC CLOB data types. The metadata clearly differentiates between a Teradata CLOB data type mapped to SQL_LONGVARCHAR and a Teradata JSON data type mapped to SQL_LONGVARCHAR.

The ODBC driver supports LOB Input, Output and InputOutput parameters. Therefore, it can load JSON data. Also the Catalog (Data Dictionary) functions support JSON.

JDBC
  • Teradata JDBC Driver 15.00.00.11 and later support the JSON data type.
  • The Teradata JDBC Driver offers Teradata-specific functionality for an application to use the PreparedStatement or CallableStatement setObject method to bind a Struct value to a question-mark parameter marker as a JSON data type. An application can also insert VARCHAR or CLOB values into JSON destination columns.
  • When an application uses the Teradata-specific functionality of specifying a JSON value as a Struct value, the Struct value must contain one of the following attributes: String, Reader, Clob, or null. If the Struct contains a Reader attribute, the Struct must also contain a second attribute that is an Integer type specifying the number of characters in the stream.
  • JSON values are retrieved from the Teradata Database as CLOB values. An application can use result set metadata or parameter metadata to distinguish a CLOB value from a JSON value.
.NET Data Provider
  • JSON data type is externalized as a CLOB. Applications can use TdClob, TdDataReader.GetChars, or TdDataReader.GetString to retrieve a JSON value.
  • Applications can send a JSON value as String or TextReader to the Teradata Database.
  • Schema Collections (Data Dictionary) also support the JSON data type.
Teradata Parallel Transporter (Teradata PT) JSON columns are treated exactly like CLOB columns and subject to the same limitations. JSON columns cannot exceed 16 MB (16,776,192 LATIN characters or 8,388,096 UNICODE characters). Teradata PT accommodates the JSON keyword in object schema but internally converts it to CLOB. Import and export are both fully supported.
BTEQ

The JSON keyword cannot be used in the USING data statement; therefore, JSON values must be referred to as either CLOB or VARCHAR. For VARCHAR, the JSON value cannot exceed 64 KB (64000 LATIN characters or 32000 UNICODE characters).

Currently, BTEQ does not support deferred mode LOB transfer for server to client. Only non-deferred mode JSON transfer is supported for server to client, and the maximum size of an output row is limited to 64 KB.

Standalone Utilities No support.
For more information about the Teradata Database client products, see the following documents:
  • Teradata® Call-Level Interface Version 2 Reference for Mainframe-Attached Systems, B035-2417
  • Teradata® Call-Level Interface Version 2 Reference for Workstation-Attached Systems, B035-2418
  • ODBC Driver for Teradata® User Guide, B035-2509
  • Teradata JDBC Driver Reference, available at https://developer.teradata.com/connectivity/reference/jdbc-driver
  • Teradata® Parallel Transporter Reference, B035-2436
  • Teradata® Parallel Transporter User Guide, B035-2445
  • Basic Teradata® Query Reference, B035-2414

Terminology

A JSON document or JSON message is any string that conforms to the JSON format. When discussing JSON values in the SQL context, JSON documents are referred to as an instance of the JSON data type or simply as a JSON instance.

A JSON document structured as an object is encapsulated in {}. A JSON document structured as an array is encapsulated in []. In the context of SQL, they are both JSON data type instances. For details, see JSON String Syntax

When we discuss the serialized form (such as the example below), we call it a JSON document. To describe the structure of the JSON document, we say "a JSON document structured as an array or object" or simply JSON array and JSON object. The following is an example of a JSON document.
{
    "name": "Product",
    "properties": {
        "id": {
            "type": "number",
            "description": "Product identifier",
            "required": true
        },
        "name": {
            "type": "string",
            "description": "Name of the product",
            "required": true
        },
        "price": {
            "type": "number",
            "minimum": 0,
            "required": true
        },
        "tags": {
            "type": "array",
            "items": {
                "type": "string"
            }
        },
        "stock": {
            "type": "object",
            "properties": {
                "warehouse": {
                    "type": "number"
                },
                "retail": {
                    "type": "number"
                }
            }
        }
    }
}