Importing and Exporting JSON Data

Teradata Vantageā„¢ JSON Data Type

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

Regardless of storage format, the default format for JSON data to be imported to or exported from Teradata Database is CLOB (character) based. You can also use the predefined transform groups to convert objects to BLOB, VARCHAR, and VARBYTE. Transforms to and from BLOB or VARBYTE are in BSON format. For more information about the transform groups, see Support for Multiple Transform Groups.

Data can also 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 one of the following:
  • A transform group that transforms BLOB or VARBYTE to JSON data
  • CAST BYTE, VARBYTE, or BLOB expression to JSON data
  • NEW JSON constructor method with BYTE, VARBYTE, or BLOB data
To export JSON data in BSON format, use one of the following:
  • A transform group that transforms JSON data in any format to BLOB or VARBYTE
  • CAST expression to cast JSON data in any format to BYTE, VARBYTE, or BLOB
  • AsBSON method on JSON data in any format

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.