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

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]