4 Work with Indexes for JSON Data in TimesTen

TimesTen supports indexing scalar values in a JSON type column using function-based indexes. The following restrictions apply:

  • Each index can have one or more fields from only one JSON type column. No such restriction exists for non JSON type columns.

  • The index cannot be used as primary key.

Function-based indexing is appropriate for queries that target particular functions, which in this context means particular SQL/JSON path expressions. This indexing is not very helpful for ad hoc structural queries. Define a function-based index if you know that you will often query a particular path expression.

  • For indexes that target a single scalar JSON value, use function-based indexes for the JSON_VALUE function.

    An index created using JSON_VALUE with the ERROR ON ERROR clause can be used for a query involving the JSON_TABLE function. This index acts as a constraint on the indexed path to ensure that only one (non null) scalar JSON value is projected for each item in the JSON data.

  • For indexes that target scalar values as elements of a JSON array, use multivalue function-based indexes for the JSON_EXISTS function.

    Although, a multivalue index—an index created using JSON_TABLE—can index a single scalar value, if you expect a path expression that target such value then it is more performance efficient to use a JSON_VALUE index.

Path expressions that contain filter expressions can be used in queries that pick up a function-based index, but a path expression that you use to define a function-based index cannot contain filter expressions.

TimesTen creates a materialized view and an index on the materialized view for each JSON index. The materialized view can have multiple function-based indexes for JSON_VALUE functions, but it can only have one function-based index for JSON_TABLE functions.

Note:

The JSON_QUERY function or JSON_EQUAL or JSON_EXISTS conditions are not supported for function-based index creation.

Topics: