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.
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.
- JSON Entity Reference syntax
- JSONExtractValue or JSONExtractLargeValue methods