JSON Data Type | Create Join Index with JSON | Teradata Vantage - Creating a Join Index With a JSON Type - Advanced SQL Engine - Teradata Database

When the inline length is equal to the maximum length, the JSON type is treated as a non-LOB type. In this case, the non-LOB 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,
   /* non-LOB */        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 non-LOB and LOB JSON types.