Creating Multivalue Indexes for JSON

Creating Multivalue Indexes for JSON_EXISTS

For data stored as JSON data type, you can use multivalue indexes for the JSON_EXISTS condition. Such indexes target scalar JSON values, either individually or within a JSON array. The main use of a multivalue index is to index scalar values within arrays. This includes scalar array elements and scalar field values of object array elements. A multivalue index can index a single scalar value, but for queries that target a single value, JSON_VALUE indexes provide better performance.

In a query, you use JSON_EXISTS in the WHERE clause of a SELECT statement. JSON_EXISTS returns true if the targeted data matches the SQL/JSON path expression (or simple dot-notation syntax) in the query. The path expression can include a filter expression, matching then requires that the targeted data satisfies the filter.

To create a multivalue index, include the MULTIVALUE keyword in CREATE INDEX plus either the syntax of the JSON_TABLE function or simple dot-notation to specify the path to the indexed data. You cannot use a NESTED clause instead of JSON_TABLE, an error is returned otherwise. See Using a NESTED Clause Instead of JSON_TABLE.

You can create a composite multivalue index to index more than one virtual column (JSON field). A composite index behaves like a set of indexes. In a query, you use JSON_TABLE to project JSON field values as virtual columns or scalar SQL values. Similarly, in an index, the field values specified in JSON_TABLE are indexed as a composite index.

For a query to pick up a multivalue index, the index must specify the SQL type of the indexed data, and the SQL type of the query result must match the type specified by the index.

When using simple dot notation syntax to create a non-composite multivalue index, you must include a data-type conversion item method (excluding binary() and dateWithTime()) to indicate the SQL data type (see SQL/JSON Path Expression Item Methods). If the index uses an "only" item method, such as numberOnly(), then only queries that use the same item method can pick up the index. In contrast (indexes that use non-"only" item methods or no method), any query that targets a scalar value that can be converted to the type indicated in the item method can pick up the index. For example, a multivalue index that uses the numberOnly() item method can only be picked up for a query that also uses numberOnly(). However, an index that uses number(), or that uses no item method, can be picked up for a query that matches any scalar (such as the "3.14" string) that can be converted to a number.

When using JSON_TABLE syntax to create a multivalue index, the virtual column type of JSON_TABLE specifies the SQL type to use. Queries that target data that can be converted to the type indicated in the virtual column can pick up the index. However, just as with non-composite indexes, you can use an "only" item method in the column path expression to further constrain the specified type of the column. For example, if the column type is specified as NUMBER, then queries with matching data (such as the "3.14" string) that can be converted to a number can pick up the index. If the column path expression uses the numberOnly() item method, then only queries that also use numberOnly() can pick up the index.

You can create more than one multivalue index for a given target. For example, you can create an index for a given field while using the number() item method and another index for the same field while using the string() item method.

When using JSON_TABLE syntax to create a composite multivalue index, you cannot specify sibling nested arrays in the JSON_TABLE expression. You can specify multiple arrays, but they cannot have the same parent field. An error is returned otherwise.

When using JSON_TABLE syntax to create a multivalue index, you must use the ERROR ON ERROR, NULL ON EMPTY, and NULL ON MISMATCH error-handling clauses. Otherwise, an error is returned. When using simple dot-notation syntax, the behavior of these clauses is provided implicitly. A mismatch type error between the type of a scalar JSON value and the corresponding scalar SQL data type of the virtual column in JSON_TABLE can be because of type incompatibility (see ON MISMATCH Clause for SQL/JSON Query Functions in Oracle Database JSON Developer's Guide) or because the SQL data type is too constraining (too small to store the data). The first kind of mismatch returns a SQL NULL. The second kind returns an error. For example, type incompatibility is tolerated when creating an index with NUMBER for JSON string data, but an error is returned when creating an index with VARCHAR(2) for data that has JSON string values of more than two characters.

When using JSON_TABLE syntax to create a multivalue index, you can use a FOR ORDINALITY clause to enable use of the index for queries that target specific array positions (see COLUMNS Clause of JSON_TABLE). At most, one entry in a COLUMNS clause can be a column name followed by FOR ORDINALITY, which specifies a column of generated row numbers (SQL NUMBER), starting with one. Otherwise, an error is returned when creating the index. Additionally, the FOR ORDINALITY column must be the last column of JSON_TABLE (this does not apply for queries, only indexes). Consider the following:

In order for a multivalue index using JSON_TABLE to be picked up for a given query, the query must apply a filter expression to the JSON field corresponding to the first virtual column of the JSON_TABLE expression.
In order for a query that targets array elements by their position to pick up a multivalue index for array positions, the index column for those array elements must be the one immediately before the FOR ORDINALITY column.

See also:

CREATE INDEX in Oracle TimesTen In-Memory Database SQL Reference

Example 4-12 Table for Multivalue Index Examples

The parts_tab table with the jparts column (JSON type ) is used in the multivalue index examples below. The JSON data includes the subparts field whose value is an array with scalar elements.

CREATE TABLE parts_tab (id NUMBER, jparts JSON);

INSERT INTO parts_tab VALUES
  (1, '{"parts" : [{"partno"   : 3, "subparts" : [510, 580, 520]},
                   {"partno"   : 4, "subparts" : 730}]}');

INSERT INTO parts_tab VALUES
  (2, '{"parts" : [{"partno"   : 7, "subparts" : [410, 420, 410]},
                   {"partno"   : 4, "subparts" : [710, 730, 730]}]}');

Example 4-13 Creating a Multivalue Index for JSON_EXISTS

This example creates a multivalue index that indexes the value of the subparts field. A table alias (t in this example) is required when using simple dot-notation syntax.

If the subparts value targeted by a query is an array, then the index can be picked up for any array elements that are numbers. If the value is a scalar, then the index can be picked up if the scalar is a number.

Given the data in Example 4-12, the subparts field in each of the objects of the parts array in the first row is indexed:

The field in the first object because its array value has elements that are numbers: 510, 580, and 520.
The field in the second object because its value is a number: 730.

If the number() item method was used in the index definition instead, then non-number scalar values (such as the "730" string) that can be converted to numbers would also be indexed.

CREATE MULTIVALUE INDEX mvi ON parts_tab t
  (t.jparts.parts.subparts.numberOnly());

Example 4-14 Creating a Composite Multivalue Index for JSON_EXISTS

This example creates a composite multivalue index that targets both the partno and subparts fields. The composite index acts likes a set of two indexes that target those to fields.

The query use JSON_TABLE syntax with a JSON path expression for the row pattern, $.parts[*]. As required for all multivalue indexes using JSON_TABLE, the error handling is specified as ERROR ON ERROR NULL ON EMPTY NULL ON MISMATCH.

The partNum column specifies SQL NUMBER(10) as data type. For the index to be picked up by a query that targets the partno field, the values on that field must be compatible with that data type. Mismatch type errors return SQL NULL, such as non-numerical string values. However, an error is returned if the SQL data type storage is too constraining, and the index is not created. An example of this would be a numerical string value with more than 10 characters.

CREATE MULTIVALUE INDEX cmvi_1 ON parts_tab
  (JSON_TABLE(jparts, '$.parts[*]'
     ERROR ON ERROR NULL ON EMPTY NULL ON MISMATCH
     COLUMNS (partNum NUMBER(10) PATH '$.partno',
       NESTED PATH '$.subparts[*]'
         COLUMNS (subpartNum NUMBER(20) PATH '$'))));

Example 4-15 Creating a Composite Multivalue Index That Can Target Array Positions

This example creates a composite multivalue index similar to Example 4-14, except that it also specifies a seq virtual column for ordinality. That means that values in the subpartNum column can be accessed by their (one-based) positions in the subparts array. The SQL data type of a FOR ORDINALITY column is always NUMBER.

CREATE MULTIVALUE INDEX cmvi_2 ON parts_tab t 
  (json_table(jparts, '$.parts[*]' 
  ERROR ON ERROR NULL ON EMPTY NULL ON MISMATCH
  COLUMNS (partNum NUMBER(10) PATH '$.partno',   
    NESTED subparts[*]     
      COLUMNS (subpartNum NUMBER(20) PATH '$',
               seq FOR ORDINALITY))));