30.4 Creating B-Tree Indexes for JSON_VALUE

You can create a B-tree function-based index for SQL/JSON function json_value. You can use the standard syntax for this, explicitly specifying json_value, or you can use dot-notation syntax with an item method. Indexes created in either of these ways can be used with both dot-notation queries and json_value queries.

Example 30-3 creates a function-based index for json_value on field PONumber of the object that is in column data of table j_purchaseorder. The object is passed as the path-expression context item.

The use of ERROR ON ERROR here means that if the data contains a record that has no PONumber field, has more than one PONumber field, or has a PONumber field with a non-number value then index creation fails. And if the index exists then trying to insert such a record fails.

An alternative is to create an index using the dot-notation syntax described in Simple Dot-Notation Access to JSON Data, applying an item method to the targeted data. Example 30-2 illustrates this.

The indexes created in both Example 30-3 and Example 30-2 can be picked up for either a query that uses dot-notation syntax or a query that uses json_value.

If you want to allow indexing of data that might be missing the field targeted by a json_value expression, then use a NULL ON EMPTY clause, together with an ERROR ON ERROR clause. Example 30-4 illustrates this.

Oracle recommends that you create a function-based index for json_value using one of the following forms. In each case the index can be used in both dot-notation and json_value queries that lead to a scalar result of the specified JSON data type.

  • Dot-notation syntax, with an item method applied to the value to be indexed. The indexed values are only scalars of the data type specified by the item method.

  • A json_value expression that specifies a RETURNING data type. It can optionally use ERROR ON ERROR and NULL ON EMPTY. The indexed values are only scalars of the data type specified by the RETURNING clause.

Indexes created in either of these ways can thus be used with both dot-notation queries and json_value queries.

See Also:

CREATE INDEX in Oracle Database SQL Language Reference

Example 30-2 Creating a Function-Based Index for a JSON Field: Dot Notation

Item method number() causes the index to be of numeric type. Always apply an item method to the targeted data when you use dot notation to create a function-based index. 

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

Note:

By default, a function-based index does not include NULL values. If a json_value expression that's used by an index returns NULL, then by default the index is not used when obtaining a matching document. This implies that by default a function-based index isn't used if NULL is used as filter predicate (for example, json_value ... IS NULL) or if json_value is used in an ORDER BY clause.

To index NULL values, and thus enable the use of json_value in an ORDER BY clause you need to add a constant value (any value) to the index creation statement: 

CREATE INDEX po_num_idx1 ON j_purchaseorder po
  (po.data.PONumber.number(), 42);

This does, however, increase the index size.

Example 30-3 Creating a Function-Based Index for a JSON Field: JSON_VALUE

Item method number() causes the index to be of numeric type. Alternatively you can instead use clause RETURNING NUMBER. 

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

Example 30-4 Specifying NULL ON EMPTY for a JSON_VALUE Function-Based Index

Clause RETURNING VARCHAR2(200) causes the index to be a SQL string of maximum length 200 characters. You could use item method string() in the path expression instead, but in that case the default return type of VARCHAR2(4000) is used.

Because of clause NULL ON EMPTY, index po_ref_idx1 can index JSON documents that have no Reference field. 

CREATE UNIQUE INDEX po_ref_idx1 ON j_purchaseorder
  (json_value(data, '$.Reference'
              RETURNING VARCHAR2(200) ERROR ON ERROR
              NULL ON EMPTY));

Related Topics

Parent topic: Indexes for JSON Data