16.20 - Storage Formats for the JSON Type - Teradata Vantage NewSQL Engine

Teradata Vantageā„¢ JSON Data Type

Teradata Database
Teradata Vantage NewSQL Engine
March 2019
Programming Reference
JSON data can be stored in the following formats:
  • Text (LATIN or UNICODE)
  • Binary JSON (BSON)
  • Universal Binary JSON (UBJSON)

The default storage format is text in the character set specified (or the default character set for the user).

You can use the optional STORAGE FORMAT clause to specify that the JSON data be stored in one of the binary formats (BSON or UBJSON). This clause can only be used in the following cases:
  • As an attribute of a table column
  • As part of a JSON data type declaration in a CAST expression
  • As an optional parameter to the JSON constructor

You can use HELP COLUMN to display the storage format of a JSON column.

You cannot define a JSON type with both a STORAGE FORMAT clause and a CHARACTER SET clause. For JSON data stored in a binary format, the character set for this data when it is exported as text is UNICODE. Therefore, no CHARACTER SET clause is needed for these formats.

Storage Format Comparison

BSON and UBJSON generally provide better traverse time as compared with text-based JSON. Data stored in either of these formats can be searched using extraction techniques, and you may see an improvement in retrieval time when these formats are used.

The following provides a comparison between the different storage formats as a guideline for selecting the best storage format for your data.

JSON Text Format

  • Requires the least amount of time for insertion
  • Slower retrieval time
  • Potentially requires more space

BSON Format

  • Validation is always done implicitly when converting from text
  • Requires the most time to insert
  • Faster retrieval time (tied with UBJSON)
  • Works well when exchanging data with MongoDB
  • Potential space savings when compared with text format
  • Validation is always done implicitly when converting from text
  • Requires more time to insert than the text format, but less than BSON
  • Faster retrieval time (tied with BSON)
  • Almost always provides space savings, especially when many numeric values are included in the data