15.10 - Importing and Exporting JSON Data - Teradata Database

Teradata Database Teradata JSON

prodname
Teradata Database
vrm_release
15.10
created_date
December 2015
category
Programming Reference
featnum
B035-1150-151K

Regardless of storage format, the standard format for JSON data to be imported to or exported from Teradata Database is CLOB (character) based.

Data can be imported to and exported from the database in BSON format by using constructor/instance methods or cast functionality. Any import/export utilities that can use constructor/instance methods of the JSON type or cast functionality may be used to import or export BSON data.

Data cannot be imported to or exported from the database in the UBJSON format.

Importing and Exporting JSON Data in BSON Format

To import JSON data in BSON format, use the CAST expression or the NEW JSON constructor method with BYTE, VARBYTE, or BLOB data.

To export JSON data in BSON format, use one of the following:
  • CAST expression to cast JSON data in any format to BYTE/VARBYTE/BLOB
  • AsBSON method on JSON data in any format

Both of these operations force the data to be converted to BSON.

For BSON, numeric types are always serialized in little-endian format. Data that is imported to or exported from the database in BSON format is expected to be in little-endian format. Teradata Database guarantees that when it exports BSON data, numeric types are serialized in little-endian format.

If data is imported to the database in BSON format, any numeric data must be in little-endian format. Failure to serialize numeric types in this manner results in corrupted data because the database cannot detect the improperly serialized numeric data.

If data is imported to the database in JSON text format, the numeric types are represented by their character equivalents. For example, the number 1 is represented as the character 'U+0031' in UNICODE. Therefore, Teradata Database ensures that when converting numeric types represented in text format to their BSON equivalents, they are serialized in little-endian format.

When JSON data that was stored in BSON format is exported as text, Teradata Database also ensures that the little-endian serialized numeric values are properly converted to their text equivalents.

Conversion of Numeric Types in Text Format To and From UBJSON Format

For UBJSON, numeric types are always serialized in little-endian format in Teradata Database.

If data is imported to the database in JSON text format, the numeric types are represented by their character equivalents. For example, the number 1 is represented as the character 'U+0031' in UNICODE. Therefore, Teradata ensures that when converting numeric types represented in text format to their UBJSON equivalents, they are serialized in little-endian format.

When JSON data that was stored in UBJSON format is exported as text, Teradata Database also ensures that the little-endian serialized numeric values are properly converted to their text equivalents.

String Encodings

Both BSON and UBJSON encode strings in the UTF8 character set. However, UTF8 is not supported as a character set inside Teradata Database. If data is imported to the database in JSON text format and is stored in one of the binary storage formats, conversion is performed as described in Storage Format Conversions.

All character string data is converted from the source data (either in LATIN or UNICODE) to its UTF8 equivalent. Any errors encountered result in a failed operation. No errors are ignored.

Similarly, when data which was stored as BSON or UBJSON is exported as JSON text, all strings encoded as UTF8 are converted to the appropriate character set (LATIN or UNICODE), and any errors encountered result in a failed operation.

If data stored in one of the binary storage formats cannot be exported as JSON text due to a character translation error, you can export it as BSON using one of the instance methods or casts of the JSON type.