Queries That Access the Values of Fields That Occur at Most Once in a Given Document

You can tune the performance of such queries in the same ways as for non-JSON data. The choices of which JSON fields to define virtual columns for or which to index, whether to place the table containing your JSON data in the In-Memory Column Store (IM column store), and whether to create materialized views that project some of its fields are analogous to the non-JSON case.

However, in the case of JSON data it is generally more important to apply at least one such performance tuning than it is in the case non-JSON data. Without any such performance aid, it is typically more expensive to access a JSON field than it is to access (non-JSON) column data, because a JSON document must be traversed to locate the data you seek.

Create virtual columns from JSON fields or index JSON fields:

• If your queries use simple and highly selective search criteria, for a single JSON field:

  • Define a virtual column on the field.

    You can often improve performance further by placing the table in the IM column store or creating an index on the virtual column.

  • Create a function-based index on the field using SQL/JSON function json_value.

• If your queries involve more than one field:

  • Define a virtual column on each of the fields.

    You can often improve performance further by placing the table in the IM column store or creating a composite index on the virtual columns.

  • Create a composite function-based index on the fields using multiple invocations of SQL/JSON function json_value, one for each field.

Queries That Access the Values of Fields That Can Occur More Than Once in a Given Document

In particular, this is the case when you access fields that are contained within an array.

There are three techniques you can use to tune the performance of such queries:

• Place the table that contains the JSON data in the IM column store.

• Use a JSON search index.

  This indexes all of the fields in a JSON document along with their values, including fields that occur inside arrays. The index can optimize any path-based search, including those using path expressions that include filters and full-text operators. The index also supports range-based searches on numeric values.

• Use a materialized view of non-JSON columns that are projected from JSON field values using SQL/JSON function json_table.

  You can generate a separate row from each member of a JSON array, using the NESTED PATH clause with json_table.

  A materialized view is typically used for optimizing SQL-based reporting and analytics for JSON content.