27 Overview of Performance Tuning for JSON

Which performance-tuning approaches you take depend on the needs of your application. Some use cases and recommended solutions are outlined here.

The use cases can be divided into two classes: searching for or accessing data based on values of JSON fields that occur (1) at most once in a given document or (2) possibly more than once.

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 column 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 four techniques you can use to tune the performance of such queries:

  • Use a multivalue function-based index for SQL/JSON condition json_exists.

    This is possible only for JSON data that is stored as JSON data type. Such an index targets scalar JSON values, either individually or within a JSON array.

  • 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.