JSON Data-Guide Fields

A given occurrence of a field in a data guide corresponds to a field that is present in one or more JSON documents of the document set.

JSON Schema Data-Guide Fields (Keywords)

A JSON Schema is a JSON document that contains a JSON object, which can itself contain child objects (subschemas). Fields that are defined by JSON Schema are called JSON Schema keywords. Table 24-2 describes the keywords that can be used in an Oracle JSON data guide. Keywords properties, items, and oneOf are used only in hierarchical and schema data guides (both of which are JSON schemas). Keyword type is used in all three kinds of data guides.

Table 24-2 JSON Schema Data-Guide Fields (Keywords)

Field (Keyword)	Value Description
`properties`	An object whose members represent the properties of a JSON object used in JSON data that is represented by a hierarchical or schema data guide.
`items`	An object whose members represent the elements (items) of an array used in JSON data represented by a hierarchical or schema data guide.
`oneOf`	An array, each of whose items represents one or more occurrences of a JSON field in the JSON data represented by a hierarchical or schema data guide.
`type`	A string naming the type of some JSON data represented by a data guide (of any kind). The possible values are: `array`, `"boolean"`, `"GeoJSON"`, `"null"`, `"number"`, `"object"`, `"string"`, and, for `JSON` type data, `"binary"`, `"date"`, `"daysecondInterval"`, `"double"`, `"float"`, `"timestamp"`, `timestamp with time zone`, and `"yearmonthInterval"`. If option `DBMS_JSON.DETECT_DATETIME` is passed as a flag parameter to function `json_dataguide`, then any string field value in your data that conforms to an Oracle-supported ISO date or time format is recorded as having `type` `"timestamp"` or `timestamp with time zone`, not `"string"`.

Oracle-Specific Data-Guide Fields

In addition to JSON Schema keywords, a JSON data guide can contain Oracle data guide-specific fields. The field names all have the prefix o:. They are described in Table 24-3.

Table 24-3 Oracle-Specific Data-Guide Fields

Field	Value Description
`o:path`	Path through the JSON documents to the JSON field. Used only in a flat data guide. The value is a simple SQL/JSON path expression (no filter expression), possibly with relaxation (implicit array wrapping and unwrapping), and possibly with a wildcard array step. It has no array steps with array indexes or range specifications, and it has no function step. See SQL/JSON Path Expression Syntax.
`o:length`	Maximum length of the JSON field value, in bytes. The value is always a power of two. For example, if the maximum length of all actual field values is 5 then the value of `o:length` is 8, the smallest power of two greater than or equal to 5.
`o:preferred_column_name`	An identifier, case-sensitive and unique to a given data guide, that you prefer as the name to use for a view column or a virtual column that is created using the data guide. This field is absent if the data guide was obtained using SQL function `json_dataguide` with format parameter `DBMS_JSON.FORMAT_FLAT` or without any format parameter (`DBMS_JSON.FORMAT_FLAT` is the default).
`o:frequency`	Percentage of JSON documents that contain the given field. Duplicate occurrences of a field under the same array are ignored. (Available only if statistics were gathered on the document set.) This field is absent if the data guide was obtained using SQL function `json_dataguide`, unless the third parameter specified `DBMS_JSON.gather_stats`. If the data guide was created using PL/SQL function `get_index_dataguide` then all documents in the document set are taken into account. Otherwise, only the documents targeted by the `json_dataguide` query are considered.
`o:num_nulls`	Number of documents whose value for the targeted scalar field is JSON `null`. (Available only if statistics were gathered on the document set.) This field is absent if the data guide was obtained using SQL function `json_dataguide`, unless the third parameter specified `DBMS_JSON.gather_stats`. If the data guide was created using PL/SQL function `get_index_dataguide` then all documents in the document set are taken into account. Otherwise, only the documents targeted by the `json_dataguide` query are considered.
`o:high_value`	Highest value for the targeted scalar field, among all documents examined. (Available only if statistics were gathered on the document set.) This field is absent if the data guide was obtained using SQL function `json_dataguide`, unless the third parameter specified `DBMS_JSON.gather_stats`. If the data guide was created using PL/SQL function `get_index_dataguide` then all documents in the document set are taken into account. Otherwise, only the documents targeted by the `json_dataguide` query are considered.
`o:low_value`	Lowest value for the targeted scalar field, among all documents examined. (Available only if statistics were gathered on the document set.) This field is absent if the data guide was obtained using SQL function `json_dataguide`, unless the third parameter specified `DBMS_JSON.gather_stats`. If the data guide was created using PL/SQL function `get_index_dataguide` then all documents in the document set are taken into account. Otherwise, only the documents targeted by the `json_dataguide` query are considered.
`o:last_analyzed`	Date and time when statistics were last gathered on the document set. (Available only if statistics were gathered on the document set.) This field is absent if the data guide was obtained using SQL function `json_dataguide`, unless the third parameter specified `DBMS_JSON.gather_stats`. If the data guide was created using PL/SQL function `get_index_dataguide` then all documents in the document set are taken into account. Otherwise, only the documents targeted by the `json_dataguide` query are considered.
`o:sample_size`	Total number of JSON documents selected by a query that uses SQL function `json_dataguide` with its the third parameter specifying `DBMS_JSON.gather_stats`. You can use a `SAMPLE` clause in the query to further control the sample size. This field is absent if the data guide was obtained in some other way.

For all data-guide formats, if a given field has the same type in all documents of the document set then that is the type reported for the field in the data guide.

If a given field has values of different types across the document set, then the types for that field are reported differently by the different data-guide formats, as follows.

A schema data guide reports each of the types used for a given field exactly. If there is more than one type for a field then the field is reported using keyword oneOf, whose value is an array of objects that specify the different types.

A hierarchical data guide reports field types similarly, except that if a field has different scalar values across the document set then the single scalar type "string" is reported for it — just as if all of its scalar values, across all documents, were strings.

A flat data guide reports the nonscalar types of a field using separate objects with different type values ("object", "array"). Just as for a hierarchical data guide, if a field has different scalar values across the document set then the single scalar type "string" is reported for it.

The types of array elements are handled similarly to the types of fields.

For flat and hierarchical data guides, different types of scalar array elements are not reported individually. Instead, a single subschema is recorded for all scalar element values: if the scalar elements, across all documents, have the same type then that type is used. Otherwise, type "string" is used.

For flat and hierarchical data guides, if across the document set an array has both a scalar element and a nonscalar element, or it has both an object element and an array element, then both the nonscalar type(s) and a scalar type are reported. The scalar type reported is as specified above (different scalar element types are reported as type "string").

For array elements, the o:path value (present only in a flat data guide) is the o:path value for the array, followed by an array with a wildcard ([*]), which indicates all array elements.

When present, the default value of field o:preferred_column_name depends on whether the data guide was obtained using SQL function json_dataguide (with format DBMS_JSON.FORMAT_HIERARCHICAL) or using PL/SQL function DBMS_JSON.get_index_dataguide:

get_index_dataguide — Same as the corresponding JSON field name, prefixed with the JSON column name followed by $, and with any non-ASCII characters removed. If the resulting field name already exists in the same data guide then it is suffixed with a new sequence number, to make it unique.

The JSON column-name part is uppercase unless that column was defined using escaped lowercase letters (for example, 'PO_Column' instead of po_column).

For example, the default value for field User for data in JSON column data is DATA$User.
json_dataguide (hierarchical format) — Same as the corresponding JSON field name.

You can, however, control column naming when you create a view or a virtual column based on the data guide, by specifying the following parameters to DBMS_JSON procedures create_view, get_view_sql, and add_virtual_columns:
- colNamePrefix =>prefix — Prefix the column names specified by o:preferred_column_name with prefix.
- mixedCaseColumns => FALSE — Make column names be case-insensitive. (They are case-sensitive by default.)
- resolveNameConflicts => TRUE (default) — Resolve any name conflicts: if the resulting field name already exists in the same data guide then it is suffixed with a new sequence number, to make it unique (same behavior that get_index_dataguide provides).

You can use PL/SQL procedure DBMS_JSON.rename_column to set the value of o:preferred_column_name for a given field and type. This procedure has no effect if data-guide information is not persisted as part of a JSON search index.

Field o:preferred_column_name is used to name a new, virtual column in the table that contains the JSON column, or it is used to name a column in a new view that also contains the other columns of the table. In either case, a name specified by field o:preferred_column_name must be unique with respect to the other column names of the table. In addition, the name must be unique across all JSON fields of any type in the document set. When you use DBMS_JSON.get_index_dataguide, the default name is guaranteed to be unique in these ways.

If the name you specify with DBMS_JSON.rename_column causes a name conflict then the specified name is ignored and a system-generated name is used instead.

24.4 JSON Data-Guide Fields