All about duality-view flex columns: rules of the road; when, where, and why to use them; field-name conflicts; gotchas.

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 up to N different flex columns for the table can be designated at those places.

You can designate the same flex column to provide the fields for different places of the same document. Those different places share all of the fields stored in that flex column. Updates to any of the places must concord, by not providing different new fields or different values for the same field.

In a given duality-view definition, you can't use the same JSON column as a flex column in one document place and as a nonflex column in another place. An error is raised if you try to do this.

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, unless unnesting is explicitly specified in the duality-view definition. See Creating Duality Views.)

  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.

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.

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. (Otherwise, to allow evolution of typing and structure for the values of particular JSON fields, you can map nonflex JSON-type columns to those fields.)

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.

Whether a JSON-type column underlying a duality view is a flex column or not, by applying a JSON schema to it you change the logical structure of the data, and thus the structure of the documents supported by the view. You remove some schema flexibility, but you don't change the storage structure (tables).

extras: JSON @flex (conflict: IGNORE)