Creating a Join Index With a JSON Type

Teradata Vantageā„¢ JSON Data Type

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

When the inline length is equal to the maximum length, the JSON type is treated as a nonLOB type. In this case, the nonLOB JSON type can be part of a join index. However, it cannot be part of the primary index of the join index.

For example:

CREATE TABLE jsonTable (id INTEGER,
   /* nonLOB */        jsn1 JSON(1000) CHARACTER SET LATIN,
   /* LOB */            jsn2 JSON(1M) INLINE LENGTH 30000 CHARACTER SET LATIN);

The following statement successfully creates a join index:

CREATE JOIN INDEX jsonJI AS SELECT id, jsn1 FROM jsonTable;

The following statement fails because LOB types cannot be part of an index:

CREATE JOIN INDEX jsonJI AS SELECT id, jsn2 FROM jsonTable;
*** Failure 5771 Index not supported by UDT 'TD_JSONLATIN_LOB'. Indexes are not supported for LOB UDTs.

You can also create a join index that contains extracted portions of a JSON instance. You can do this with nonLOB and LOB JSON types.

Use the following to extract portions of the JSON data:
  • JSON Entity Reference syntax
  • JSONExtractValue or JSONExtractLargeValue methods