Creating Indexes for JSON_VALUE

You can create an index for the JSON_VALUE function. You can use the standard syntax, explicitly specifying JSON_VALUE, or you can use dot-notation syntax with an item method. Indexes created either way can be used by both dot-notation or JSON_VALUE queries.

It is recommended that you create function-based indexes for JSON_VALUE using one of the following forms:

  • Dot-notation syntax with an item method applied to the indexed value. The indexes values are only scalars of the data type specified by the item method. See Example 4-1.

  • A JSON_VALUE expression with a RETURNING clause applied to the indexed value. Optionally, use the ERROR ON ERROR and NULL ON EMPTY clauses. The indexes values are only scalars of the data type specified by RETURNING clause. See Example 4-2.

    Using the ERROR ON ERROR clause ensures that the index creation fails if the data contains a JSON document where the target field is not present or it has a value that cannot be returned in the data type specified by the RETURNING clause. If the index exists trying to insert such records fails.

    Using the NULL ON EMPTY clause together with ERROR ON ERROR enables indexing data that is missing the targeted field. See Example 4-3.

Indexes created in either of these forms can be used with both dot-notation queries and JSON_VALUE queries.

See also:

CREATE INDEX in Oracle TimesTen In-Memory Database SQL Reference

Example 4-1 Creating a Function-Based Index for a JSON Field (Dot-Notation)

This example creates a unique index for the PONumber field. The example uses the number() item method to make the index of numeric type. 

CREATE UNIQUE INDEX po_num_idx1 ON j_purchaseorder po 
  (po.po_document.PONumber.number());

Example 4-2 Creating a Function-Based Index for a JSON Field (JSON_VALUE)

This example creates a unique index for the PONumber field. The example uses the number() item method to make the index of numeric type. Alternatively, you can use the RETURNING NUMBER clause instead. 

CREATE UNIQUE INDEX po_num_idx2 ON j_purchaseorder 
  (JSON_VALUE(po_document, '$.PONumber.number()' ERROR ON ERROR));

Example 4-3 Creating a Function-Based Index for a JSON Field (JSON_VALUE) With NULL ON EMPTY

This example creates a unique index for the Reference field. The example uses the RETURNING VARCHAR2(200) clause to make the index a SQL string with a maximum length of 200 characters. Alternatively, you can use the string() item method instead, but then the default return type is used: VARCAHAR(4000).

The example also uses the NULL ON EMPTY clause (in conjunction with ERROR ON ERROR). This ensures that JSON documents without the Reference field can be indexed by this index. 

CREATE UNIQUE INDEX po_ref_idx1 ON j_purchaseorder 
  (JSON_VALUE(po_document, '$.Reference' RETURNING VARCHAR(200) 
              ERROR ON ERROR
              NULL ON EMPTY));