15.10 - Maximum Length of a JSON Instance - Teradata Database

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

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.

Exceeding the Maximum Length

*** 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.

Maximum Length and the Storage of JSON Data

Examples: Creating Tables with JSON Columns Specifying Maximum Length or Character Set

The following examples demonstrate the maximum length and character set specifications.

CREATE TABLE json_table(id INTEGER, json_j1 JSON);

Result: This creates a table with LATIN character set with a maximum length of that character set, 16776192 LATIN characters. When a character set is not specified for the JSON type, the default character set for the user is used. The result for this example assumes the user had LATIN as their default character set.

CREATE TABLE json_table(id INTEGER, json_j1 JSON CHARACTER SET UNICODE);

Result: This creates a table with UNICODE character set and a maximum length of that character set, 8388096 UNICODE characters.

CREATE TABLE json_table(id INTEGER, json_j1 JSON(100000));

Result: This creates a table with a maximum length of 100000 LATIN characters. Note, the result for this example assumes the user had LATIN as their default character set.

CREATE TABLE json_table(id INTEGER, json_j1 JSON(100000) CHARACTER SET UNICODE);

Result: This creates a table with a maximum length of 100000 UNICODE characters.

CREATE TABLE json_table(id INTEGER, json_j1 JSON(64000), json_j2 JSON(12000));

Result: This fails because the maximum possible amount of data stored in the row could grow to approximately 76000 bytes. This exceeds the maximum row size, as described in item 1 earlier.

CREATE TABLE json_table(id INTEGER, json_j1 JSON(64001), json_j2 JSON(12000));

Result: This succeeds because the maximum possible amount of data stored in the row is ~16000 bytes which is within the maximum row size. This is because the json_j1 column has the storage scheme described in item 2 earlier, in which a maximum of 4K bytes will be stored in the row.

Example: Creating a Table with JSON Columns Specifying a Storage Format

The following table defines five columns that are the JSON type, each with a different maximum length. Column json2 stores JSON data using the BSON storage format. Column json4 stores JSON data using the UBJSON storage format.

CREATE TABLE employee (
    id    INTEGER, 
    json1 JSON(20), 
    json2 JSON(25) STORAGE FORMAT BSON, 
    json3 JSON(30) CHARACTER SET UNICODE, 
    json4 JSON(1000) STORAGE FORMAT UBJSON, 
    json5 JSON(5000));

Examples: Creating a Function with JSON Type Parameters

The following shows functions with an input or return parameter that is a JSON type.

CREATE FUNCTION json1 
    (p1 JSON(1000)) 
     RETURNS VARCHAR(100) 
     NO SQL 
     PARAMETER STYLE SQL 
     CALLED ON NULL INPUT 
     DETERMINISTIC 
     LANGUAGE C 
     EXTERNAL NAME 'CS!json1!json1.c!F!json1';

void json1 (	
     JSON_HANDLE    *json_handle, 
     VARCHAR_LATIN  *result,
     int            *indicator_ary,
     int            *indicator_result,
     char           sqlstate[6],
     SQL_TEXT       extname[129],
     SQL_TEXT       specific_name[129],
     SQL_TEXT       error_message[257])	
{
  /* body function */
}

CREATE FUNCTION json2 
    (p1 VARCHAR(100))  
    RETURNS JSON(100) NO SQL 
    PARAMETER STYLE SQL 
    CALLED ON NULL INPUT 
    DETERMINISTIC 
    LANGUAGE C 
    EXTERNAL NAME 'CS!json2!json2.c!F!json2';

void json2 (
     VARCHAR_LATIN  *result,	
     JSON_HANDLE    *json_handle, 
     int            *indicator_ary,
     int            *indicator_result,
     char           sqlstate[6],
     SQL_TEXT       extname[129],
     SQL_TEXT       specific_name[129],
     SQL_TEXT       error_message[257])	
{
  /* body function */
}

Example: Error: JSON Value Is Too Large to Store in the Defined JSON Type

In this example, an error is returned when data being inserted into a JSON column is larger than the maximum length defined.

The smallJSONTable table in this example has a JSON column with a maximum length of 10 LATIN characters.

CREATE TABLE smallJSONTable(id INTEGER, j JSON(10));

The following INSERT statement succeeds because the data inserted into the JSON column is less than 10 characters.

INSERT INTO smallJSONTable(1, '{"a":1}');

*** Insert completed. One row added.
*** Total elapsed time was 1 second.

The following INSERT statement fails because '{"a":12345}' is greater than the maximum length of 10 characters.

INSERT INTO smallJSONTable(1, '{"a":12345}');

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

Example: Error Accessing BSON Data as Text

In this example, JSON data is inserted into the bsonCol column which is defined with a maximum length of 45 bytes and a storage format of BSON. The inserted data is 34 bytes in BSON. However, this data is 94 bytes in UNICODE text. Therefore, when the query tries to access the data as UNICODE text, an error is returned because the text exceeds the maximum length of 45 bytes.

CREATE TABLE bsonTable(id INTEGER, bsonCol JSON(45) STORAGE FORMAT BSON);

/* insert '{"username":null,"password":null,"member":true}' stored as BSON, 
which is 34 bytes in BSON and 94 bytes in UNICODE text */

INSERT INTO bsonTable(1, 
NEW JSON('220000000A757365726E616D65000A70617373776F726400086D656D626572000100'xb, 
BSON));

SELECT bsonCol FROM bsonTable;

Result:

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

In this case, the data may be cast to a predefined type (VARCHAR/VARBYTE/CLOB/BLOB), selected out using the AsJSONText or AsBSON methods, or cast to a larger version of the JSON type in UNICODE text format.

Example: Exceeding the Maximum Length for Binary-formatted JSON Columns

In this example, the ubjsonTable table has a JSON column defined with a maximum length of 50 bytes and a storage format of UBJSON.

CREATE TABLE ubjsonTable(id INTEGER, ubjsonCol JSON(50) STORAGE FORMAT UBJSON);

The following data is inserted into ubjsonTable:

INSERT ubjsonTable(1,'[100,100,100,100,100,100,100,100,100,100,100,100]');

The following query uses the StorageSize method to get the size in bytes needed to store the above data in UNICODE text.

SELECT ubjsonCol.StorageSize('UNICODE_TEXT') FROM ubjsonTable;

Result:

ubjsonCol.STORAGESIZE('UNICODE_TEXT')
-------------------------------------
		                         98

The following query uses the StorageSize method to get the size in bytes currently used to store the above data in UBJSON format.

SELECT ubjsonCol.StorageSize('UBJSON') FROM ubjsonTable;

Result:

ubjsonCol.STORAGESIZE('UBJSON')
-------------------------------
		                    40

The inserted data in UBJSON format is 40 bytes, and this fits into the ubjsonCol column which is defined with a maximum length of 50 bytes.

The following query fails because it tries to retrieve the inserted data as UNICODE text. The inserted data is 98 bytes in UNICODE text as shown in the previous query. This exceeds the maximum length of the ubjsonCol column which is 50 bytes.

SELECT ubjsonCol FROM ubjsonTable;

Result:

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

In order to retrieve the inserted data in UNICODE text format, you must cast the data to a larger size as shown in the following query:

SELECT CAST(ubjsonCol as JSON(100) CHARACTER SET UNICODE) FROM ubjsonTable;

Result:

ubjsonCol
-------------------------------------------------
[100,100,100,100,100,100,100,100,100,100,100,100]