15.10 - Teradata Support for JSON - Teradata Database

Teradata Database Teradata JSON

Product
Teradata Database
Release Number
15.10
Release Date
December 2015
Content Type
Programming Reference
Publication ID
B035-1150-151K
Language
English (United States)

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 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
  • VARCHAR values or CLOB values can be inserted into JSON destination columns.
  • JSON values cannot be directly retrieved from the ResultSet of a query. An SQLException is thrown with Error 1245 "Unable to obtain data value because the Teradata Database indicated that the data type is ambiguous".
  • The JSON columns in the select list of a query must be CAST to VARCHAR or CLOB.
.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 (TPT) 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). TPT 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 books:

  • 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 User Guide, B035-2403
  • 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"
                }
            }
        }
    }
}