JSON Data Type

JSON Data Type | Maximum Length | Teradata Vantage - Maximum Length of a JSON Instance - Advanced SQL Engine - Teradata Database

Product

Advanced SQL Engine

Teradata Database

Release Number

17.10

Published

July 2021

Language

English (United States)

Last Update

2021-07-27

dita:mapPath

uwa1591040057999.ditamap

dita:ditavalPath

uwa1591040057999.ditaval

dita:id

B035-1150

lifecycle

Product Category

Teradata Vantage™

The maximum length of a JSON type is variable, meaning that the JSON type has some default maximum length, but that length can be adjusted in places where the type is used in a manner analogous to the VARCHAR data type. Therefore, the length can never exceed the absolute maximum length, but the maximum length defined for a particular instance of the JSON type may be shorter than the absolute maximum length.

The absolute maximum length is the largest possible length for a JSON type. This limit is 16776192 bytes.

Absolute Maximum Length for JSON Text Data

The maximum length of the JSON text depends on the character set being used as follows:

For UNICODE, the maximum length is 8388096 characters.
For LATIN, the maximum length is 16776192 characters.

8388096 UNICODE characters or 16776192 LATIN characters are equivalent to 16776192 bytes, which is the absolute maximum length for the JSON type.

If no maximum length is specified, the default maximum length for the character set is chosen. If specified, the length is subject to a minimum of two characters and cannot be greater than the absolute maximum for the character set. If the character set is not specified, the character set of the user is used.

Absolute Maximum Length for JSON Binary Data

The maximum length of a JSON type using a binary storage format is 16776192 bytes.

There is no guarantee that the length of the binary data will be less than or equal to the length of the same data represented in text. Therefore it is possible that JSON data which would fit in a JSON column with maximum length x stored as text will not fit in a JSON column with maximum length x stored as a binary format, and vice versa. The length selected must be carefully chosen to ensure that it will accommodate the data.

Determining the Maximum Length for a Binary-formatted JSON Column

The length specified for a JSON column that uses a binary format must be large enough to fit the length of the data in its binary format. However, when retrieving the data, the data is converted to its text equivalent. For the binary formats, strings are stored in UTF8, and the data is converted to UNICODE when it is retrieved.

If the length is not large enough for the text representation of the data, you will be able to store the data in the binary format, but you will not be able to retrieve it as text format. An error is reported if you try to select the data without casting to a larger size. Therefore, determining an appropriate length for the column requires some planning. The following example shows a way to determine this length.

Using one of the supported load utilities, you can load some binary JSON data into an intermediate table with a CLOB column.
```
CREATE TABLE clobTable(id INTEGER, c CLOB);

/*load many rows of data*/
```
Use a query such as the following to determine the length needed for a JSON column which uses a binary format:
```
SELECT MAX(NEW JSON(c).StorageSize('BSON')) FROM clobTable;
```

This is one way to determine the smallest possible size for a JSON column. If space is not an issue and there is no need to receive the performance benefits of a smaller JSON, then Teradata recommends to use the maximum possible size for the JSON column.

Exceeding the Maximum Length

If you exceed the maximum length, you will get the following error:

*** JSON value is too large to store in the defined JSON type.

For example, if you try to insert data that is larger than the maximum length defined for a JSON column, or pass data that is larger than what is supported by a JSON parameter, you will get this error.

For a JSON column that uses a binary format, you can load binary data that fits within the defined length of the column, but this data may exceed the maximum allowed size for the output (text) format. In this case, the data may be stored in a binary format, but it cannot be retrieved as text format. You will get this error when trying to access this data as text. If you encounter this problem, you can do one of the following:

Cast the data to a JSON type with a larger maximum length.
Cast the data to a predefined type (VARCHAR/VARBYTE/CLOB/ BLOB).
Select the data using the AsJSONText or AsBSON methods.

Maximum Length and the Storage of JSON Data

JSON data is stored inline and in LOB subtables depending on the size of the data.

If the maximum length specified is less than or equal to 64000 bytes (64000 LATIN characters or 32000 UNICODE characters), then the data in all rows of the table are stored inline.
If the maximum length specified exceeds 64000 bytes, then rows with less than 4K bytes of data are stored inline, and rows with more than 4K bytes of data are stored in a LOB subtable. A table with a column defined in this manner will have a few rows stored inline and a few rows in a LOB subtable, depending on data size.