JSON Data Stored in JSON-Relational Duality Views

6 JSON Data Stored in JSON-Relational Duality Views

Columns of JSON data type stored in tables underlying a duality view can produce JSON values of any kind (scalar, object, array) in the documents supported by the view. This stored JSON data can be schemaless or JSON Schema-based (to enforce particular shapes and types of field values).

Whether to store some of the data underlying a duality view as JSON type and, if so, whether to enforce its structure and typing, are design choices to consider when defining the view.

By storing some JSON data that contributes to the JSON documents supported by (generated by) a duality view, you can choose the granularity and complexity of the building blocks that define the view. Put differently, you can choose the degree of normalization you want for the underlying data. Different choices involve different tradeoffs.

A JSON-relational duality view supports a set of JSON documents based on underlying table data. The JSON documents are automatically generated from this table data as needed.

A document-centric application accesses, updates, and otherwise uses the JSON documents supported by a duality view as if they were stored in its single, JSON-type column — applications see only a column of JSON documents.

At the same time, a relational/table-centric application can access, update, and otherwise use the same underlying table data directly. Duality: changes to either documents or table data are automatically reflected in the other (and this is the case across multiple documents and tables that share data).

Typically, the table data underlying a duality view is completely normalized, and thus the table columns contain only values of scalar SQL data types.

Complete normalization gives you the most flexibility in terms of combining data from multiple tables to support different kinds of duality view (more generally, in terms of combining some table data with other table data, outside of any use for duality views).

And in an important particular use case, it lets you access the data in existing relational tables from a document-centric application, as JSON documents.

On the other hand, the greater the degree of normalization, the more tables you have, which means more decomposition when inserting JSON data and more joining when querying it. If an application typically accesses complex objects as a whole then greater normalization can thus negatively impact performance.

You can also think of the building-block columns in the tables that underlie a duality view as providing the ingredients, and think of the duality-view definition as providing the recipe, for producing JSON documents of a particular kind (structure and typing).

A cooking recipe need not be "from scratch", using only simple, basic ingredients. On the other hand, nor does a recipe need to be as simple as just adding water to a preassembled/prepackaged "mix". There's a range of possibilities for each ingredient, from basic (an egg) to complex (a cake mix).

For example, if a recipe calls for "salad dressing" as one of its ingredients, that can come ready-made from a bottle or you can create it by combining more-basic ingredients such as olive oil and vinegar. And an ingredient for composing the salad-dressing ingredient might itself be complex (prepackaged), such as mayonnaise, mustard, or a prepared mix of spices.

The same is true for the ingredients used to define/implement a duality view and the JSON documents that it supports.

When a table underlying a duality view is completely normalized as SQL scalar values, the recipe ingredients are as simple and basic as possible (scalar values are atomic: indivisible).

But some (or even all) of the table columns can instead store JSON-type data, which can be scalar or complex. Sometimes it makes sense to include whole (small) JSON documents, stored in the duality view, as part of the larger, generated documents. This amounts to using some complex ingredients in your duality-view recipe.

With Oracle Database you can store JSON data (documents) in a column of JSON data type, and you can selectively update any parts of those documents (any fields), or replace whole documents at a time.

This is also true of the data in a stored JSON-type column that's used to define part of a duality view. And like any other column in an underlying table, a JSON-type column can be shared among different duality views, and thus be shared in their different resulting (generated) JSON documents.

By default, a JSON document is free-form: its structure and typing are not defined by, or forced to conform to, any given pattern/schema. In this case, applications can easily change the shape and types of the documents as needed.

On the other hand, you can impose typing and structure on the data in a JSON-type column, using JSON Schema. JSON Schema gives you a full spectrum of control:

From fields whose values are completely undefined to fields whose values are strictly defined.
From scalar JSON values to large, complex JSON objects and arrays.
From simple type definitions to combinations of JSON-language types. For example:
- A value that satisfies anyOf, allOf, or oneOf a set of JSON schemas
- A value that does not satisfy a given JSON schema

As an example at one end of the type spectrum, a tiny JSON schema can be applied to a JSON-type column to require its data to be of a particular JSON-language scalar type.

A corresponding Oracle JSON-language scalar type exists for each SQL scalar type that can be used in a duality-view definition. Consequently, JSON-schema typing can be just as fine-grained as SQL typing.

For example, if applied to a JSON-type column as a check constraint, this JSON schema allows only values that are JSON strings: {"type": "string"}. The effect is similar to that of using a column of SQL type VARCHAR2.

This JSON schema allows only values that are JSON dates (an Oracle JSON-language scalar type): {"extendedType": "date"}.^Foot 1 The effect is similar to that of using a column of SQL type DATE.

And this JSON schema allows only values that are JSON strings or numbers: {"type": [ "string", "number" ]}. SQL has no type that corresponds to this.

Note:

Using, in a duality-view definition, a JSON-type column that's constrained by a JSON schema to hold only data of a particular JSON scalar type (date, string, etc.) that corresponds to a SQL scalar type has the same effect on the JSON documents supported by the view as using a column of the corresponding SQL scalar type (DATE, VARCHAR2, etc.).

However, code that acts directly on such stored JSON-type data won't necessarily recognize and take into account this correspondence. The SQL type of the data is, after all, JSON, not DATE, VARCHAR2, etc. To extract a JSON scalar value as a value of a SQL scalar data type, code needs to use SQL/JSON function json_value. See SQL/JSON Function JSON_VALUE in Oracle Database JSON Developer’s Guide.

Let's summarize some of the tradeoffs between using basic ingredients (SQL scalar columns) and possibly complex ingredients (JSON-type columns) in a table underlying a duality view:

Flexibility of combination. For the finest-grain combination, use completely normalized tables, whose columns are all SQL scalars.
Flexibility of document type and structure. For maximum flexibility of JSON field values at any given time, and thus also for changes over time (evolution), use JSON-type columns with no JSON-schema constraints.
Granularity of field definition. The finest granularity requires a column for each JSON field, regardless of where the field is located in documents supported by the duality view. (The field value could nevertheless be a JSON object or array, if the column is JSON-type.)

If it makes sense for your application to share some complex JSON data among different kinds of documents, and if you expect to have no need for combining only parts of that complex data with other documents or, as SQL scalars, with relational data, then consider using JSON data type for the columns underlying that complex data.

In other words, in such a use case consider sharing JSON documents, instead of sharing the scalar values that constitute them. In still other words, consider using more complex ingredients in your duality-view recipe.

Note that the granularity of column data — how complex the data in it can be — also determines the granularity of updating operations and ETAG-checking (for optimistic concurrency control). The smallest unit for such operations is an individual column underlying a duality view; it's impossible to annotate individual fields inside a JSON-type column.

Update operations can selectively apply to particular fields contained in the data of a given JSON-type column, but control of which update operations can be used with a given view is defined at the level of an underlying column or whole table — nothing smaller. So if you need finer grain updating or ETAG-checking then you need to break out the relevant parts of the JSON data into their own JSON-type columns.

Flex Columns: Duality-View Schema Flexibility and Evolution
A flex column in a table underlying a JSON-relational duality view lets you add and redefine fields of the document object produced by that table. This provides a certain kind of schema flexibility to a duality view, and to the documents it supports.

Related Topics

6.1 Flex Columns: Duality-View Schema Flexibility and Evolution

A flex column in a table underlying a JSON-relational duality view lets you add and redefine fields of the document object produced by that table. This provides a certain kind of schema flexibility to a duality view, and to the documents it supports.

Any tables underlying a duality view can have any number of JSON-type columns. At most one JSON column per table can be designated as a flex column at each position where that table is used in the view definition. If a given table is used only at one place in a view definition (a typical case) then only one flex column for the table can be used. If the same table is used in N different places in a view definition, then N different flex columns for the table can be designated at those places.^Foot 2

In any table, a JSON column generally provides for flexible data: by default, its typing and structure are not constrained/specified in any way (for example, by a JSON schema).

The particularity of a JSON column that's designated as a flex column for a duality view is this:

The column value must be a JSON object or SQL NULL.

This means that it must be declared as type JSON (OBJECT), not just JSON. Otherwise, an error is raised when you try to use that column in a duality-view definition.

(This restriction doesn't apply to a nonflex JSON-type column; its value can be any JSON value: scalar, array, or object.)
On read, the object stored in a flex column is unnested: its fields are unpacked into the resulting document object.

That is, the stored object is not included as such, as the value of some field in the object produced by the flex column's table. Instead, each of the stored object's fields is included in that document object.

(Any value — object, array, or scalar — in a nonflex JSON-type column is just included as is; an object is not unnested.)

For example, if the object in a given row of the flex column for table tab1 has fields foo and bar then, in the duality-view document that corresponds to that row, the object produced from tab1 also contains those fields, foo and bar.
On write, the fields from the document object are packed back into the stored object, and any fields not supported by other columns are automatically added to the flex column. That is, an unrecognized field "overflows" into the object in the JSON flex column.

For example, if a new field toto is added to a document object corresponding to a table that has a flex column, then on insertion of the document if field toto isn't already supported by the table then field toto is added to the flex-column's object.

Note:

To require a nonflex JSON-type column to hold only object values (or SQL NULL) you can define it using the modified data type JSON (OBJECT), or you can use a JSON-Schema VALIDATE check constraint of {"type":"object"}. See Validating JSON Data with a JSON Schema in Oracle Database JSON Developer’s Guide.

More generally, you can require a nonflex JSON-type column to hold only scalar, object, or array JSON values, or any combination of those. And you can restrict scalar values to be of a specific type, such as a string or a date. For example, if the column type is JSON (OBJECT, SCALAR DATE) then it allows only values that are objects or dates.

A column designated as flex for a duality view is such (is flex) only for the view. For the table that it belongs to, it's just an ordinary JSON-type column, except that the value in each row must be a single JSON object or SQL NULL.

Different duality views can thus define different flex columns (that is, with different names) for the same table, each view's flex column suiting that view's own purposes, providing fields for the documents that only it supports.

Note:

If for some reason you actually want two or more duality views to share a flex column, then just give the flex column the same name when defining each view.

However, this is generally not what you want.

Unlike nonflex columns, which are dedicated to individual fields that are specified explicitly in a view's definition, a flex column holds the data for multiple fields that are unknown to the view definition. A flex column is essentially a free pass for unrecognized incoming fields at certain locations in a document (that's its purpose: provide flexibility).

On write, an unrecognized field is stored in a flex column (of the table relevant to the field's location in the document). If two views with the same underlying table share a flex column there, then incoming fields unrecognized by either view get stored in that column, and on read those fields are exposed in the documents for both views.

Because a flex column's object is unnested on read, adding its fields to those produced by the other columns in the table, and because a JSON column is by default schemaless, changes to flex-column data can change the structure of the resulting document object, as well as the types of some of its fields.

In effect, the typing and structure of a duality view's supported documents can change/evolve at any level, by providing a flex column for the table supporting the JSON object at that level.

You can change the typing and structure of a duality view's documents by modifying flex-column data directly, through the column's table. More importantly, you can do so simply by inserting or updating documents with fields that don't correspond to underlying relational columns. Any such fields are automatically added to the corresponding flex columns. Applications are thus free to create documents with any fields they like, in any objects whose underlying tables have a flex column.

However, be aware that unnesting the object from a flex column can lead to name conflicts between its fields and those derived from the other columns of the same table. Such conflicts cannot arise for JSON columns that don't serve as flex columns.

For this reason, if you don't need to unnest a stored JSON object — if it's sufficient to just include the whole object as the value of a field — then don't designate its column as flex. Use a flex column where you need to be able to add fields to a document object that's otherwise supported by relational columns.

The value of any row of a flex column must be a JSON object or the SQL value NULL.

SQL NULL and an empty object ({}) behave the same, except that they typically represent different contributions to the document ETAG value. (You can annotate a flex column with NOCHECK to remove its data from ETAG calculation. You can also use column annotation [NO]UPDATE, [NO]CHECK on a flex column.)

In a duality-view definition you designate a JSON-type column as being a flex column for the view by following the column name in the view definition with keywords AS FLEX in SQL or with annotation @flex in GraphQL.

For example, in this GraphQL definition of duality view dv1, column t1_json_col of table table1 is designated as a flex column. The fields of its object value are included in the resulting document as siblings of field1 and field2. (JSON objects have undefined field order, so the order in which a table's columns are specified in a duality-view definition doesn't matter.)

CREATE JSON RELATIONAL DUALITY VIEW dv1 AS
  table1 @insert @update @delete
    {_id       : id_col,
     t1_field1 : col_1,
     t1_json_col @flex,
     t1_field2 : col_2};

When a table underlies multiple duality views, those views can of course use some or all of the same columns from the table. A given column from such a shared table can be designated as flex, or not, for any number of those views.

The fact that a column is used in a duality view as a flex column means that if any change is made directly to the column value by updating its table then the column value must still be a JSON object (or SQL NULL).

It also means that if the same column is used in a table that underlies another duality view, and it's not designated as a flex column for that view, then for that view the JSON fields produced by the column are not unpacked in the resulting documents; in that view the JSON object with those fields is included as such. In other words, designation as a flex column is view-specific.

You can tell whether a given table underlying a duality view has a flex column by checking BOOLEAN column HAS_FLEX_COL in static dictionary views *_JSON_DUALITY_VIEW_TABS. You can tell whether a given column in an underlying table is a flex column by checking BOOLEAN column IS_FLEX_COL in static dictionary views *_JSON_DUALITY_VIEW_TAB_COLS. See ALL_JSON_DUALITY_VIEW_TABS and ALL_JSON_DUALITY_VIEW_TAB_COLS in Oracle Database Reference.

The data in both flex and nonflex JSON columns in a table underlying a duality view can be schemaless, and it is so by default.

But you can apply JSON schemas to any JSON-type columns used anywhere in a duality-view definition, to remove their flexibility ("lock" them). You can also impose a JSON schema on the documents generated/supported by a duality view.

Because the fields of an object in a flex column are unpacked into the resulting document, if you apply a JSON schema to a flex column the effect is similar to having added a separate column for each of that object's fields to the flex column's table using DML.

In effect, by applying a JSON schema you change the logical structure of the data, and thus the structure of the documents supported by the view. You remove schema flexibility, but you don't change the storage structure (tables).

GraphQL	SQL	Behavior
`(conflict: KEEP_NESTED)`	`KEEP [NESTED] ON [NAME] CONFLICT` (Keywords `NESTED` and `NAME` are optional.)	Any field names in conflict are read from nonflex columns. Field `_nameConflicts` (a reserved name) is added, with value an object whose members are the conflicting names and their values, taken from the flex column. This is the default behavior. For example, if for a given document nonflex field `quantity` has value `100`, and the flex-column data has field `quantity` with value `"314"`, then nonflex field `quantity` would keep its value `100`, and field `_nameConflicts` would be created or modified to include the member `"quantity":314`.
`(conflict: ARRAY)`	`ARRAY ON [NAME] CONFLICT` (Keyword `NAME` is optional.)	Any field names in conflict are read from nonflex columns. The value of each name that has a conflict is changed in its nonflex column to be an array whose elements are the values: one from the nonflex column and one from the flex-column data, in that order. For example, if for a given document nonflex field `quantity` has value `100`, and the flex-column data has field `quantity` with value `"314"`, then nonflex field `quantity` would have its value changed to the array `[100,314]`.
`(conflict: IGNORE)`	`IGNORE ON [NAME] CONFLICT` (Keyword `NAME` is optional.)	Any field names in conflict are read from nonflex columns. The same names are ignored from the flex column.
`(conflict: ERROR)`	`ERROR ON [NAME] CONFLICT` (Keyword `NAME` is optional.)	An error is raised.