Operations on documents supported by a duality view — in particular, queries — are automatically rewritten as operations on the underlying table data. This optimization includes taking advantage of indexes. Because the underlying data types are fully known, implicit runtime type conversion can generally be avoided.

Querying a duality view — that is, querying its supported JSON documents — is similar to querying a table or view that has a single column, named DATA, of JSON data type. (You can also query a duality view's hidden columns, ETAG and RESID — see Creating Duality Views.)

For queries that use values from JSON documents in a filter predicate (using SQL/JSON condition json_exists) or in the SELECT list (using SQL/JSON function json_value), the construction of intermediate JSON objects (for JSON-type column DATA) from underlying relational data is costly and unnecessary. When possible, such queries are optimized (automatically rewritten) to directly access the data stored in the underlying columns.

This avoidance of document construction greatly improves performance. The querying effectively takes place on table data, not JSON documents. Documents are constructed only when actually needed for the query result.

Some queries cannot be rewritten, however, for reasons including these:

• A query path expression contains a descendant path step (..), which descends recursively into the objects or arrays that match the step immediately preceding it (or into the context item if there is no preceding step).

• A filter expression in a query applies to only some array elements, not to all ([*]). For example, [3] applies to only the fourth array element; [last] applies only to the last element.

• A query path expression includes a negated filter expression. See Negation in Path Expressions in Oracle Database JSON Developer’s Guide.

For duality-view queries using SQL/JSON functions json_value, json_query, and json_exists, if you set parameter JSON_EXPRESSION_CHECK to ON then if a query cannot be automatically rewritten an error is raised that provides the reason for this.

JSON_EXPRESSION_CHECK can also be useful to point out simple typographical mistakes. It detects and reports JSON field name mismatches in SQL/JSON path expressions or dot-notation syntax.

You can set parameter JSON_EXPRESSION_CHECK using (1) the database initialization file (init.ora), (2) an ALTER SESSION or ALTER SYSTEM statement, or (3) a SQL query hint (/*+ opt_param('json_expression_check', 'on') */, to turn it on). See JSON_EXPRESSION_CHECK in Oracle Database Reference.

In some cases your code might explicitly call for type conversion, and in that case rewrite optimization might not be optimal, incurring some unnecessary runtime overhead. This can be the case for SQL/JSON function json_value, for example. By default, its SQL return type is VARCHAR2. If the value is intended to be used for an underlying table column of type NUMBER, for example, then unnecessary runtime type conversion can occur.

For this reason, for best performance Oracle recommends as a general guideline that you use a RETURNING clause or a type-conversion SQL/JSON item method, to indicate that a document field value doesn't require runtime type conversion. Specify the same type for it as that used in the corresponding underlying column.

For example, field _id in a race document corresponds to column race_id in the underlying race table, and that column has SQL type NUMBER. When using json_value to select or test field _id you therefore want to ensure that it returns a NUMBER value.

The second of the following two queries generally outperforms the first, because the first returns VARCHAR2 values from json_value, which are then transformed at run time, to NUMBER and DATE values. The second uses type-conversion SQL/JSON item method numberOnly() and a RETURNING DATE clause, to indicate to the query compiler that the SQL types to be used are NUMBER and DATE. (Using a type-conversion item method is equivalent to using the corresponding RETURNING type.)

SELECT json_value(DATA, '$.laps'),
       json_value(DATA, '$.date')
  FROM race_dv
  WHERE json_value(DATA, '$._id') = 201;

SELECT json_value(DATA, '$.laps.numberOnly()'),
       json_value(DATA, '$.date' RETURNING DATE)
  FROM race_dv
  WHERE json_value(DATA, '$._id.numberOnly()') = 201;

The same general guideline applies to the use of the simple dot-notation syntax. Automatic optimization typically takes place when dot-notation syntax is used in a WHERE clause: the data targeted by the dot-notation expression is type-cast to the type of the value with which the targeted data is being compared. But in some cases it's not possible to infer the relevant type at query-compilation time — for example when the value to compare is taken from a SQL/JSON variable (e.g. $a) whose type is not known until run time. Add the relevant item method to make the expected typing clear at query-compile time.

The second of the following two queries follows the guideline. It generally outperforms the first one, because the SELECT and ORDER BY clauses use item methods numberOnly() and dateTimeOnly() to specify the appropriate data types.^Foot 1

SELECT t.DATA.laps, t.DATA."date"
  FROM race_dv t
  WHERE t.DATA."_id" = 201
  ORDER BY t.DATA."date";

SELECT t.DATA.laps.numberOnly(), t.DATA."date".dateTimeOnly()
  FROM race_dv t
  WHERE t.DATA."_id".numberOnly() = 201
  ORDER BY t.DATA."date".dateTimeOnly();