Tables underlying a duality view produce fields in the view's supported documents. The columns in a nonroot table can produce fields (1) in an object specific to that table (nesting) or (2) in the object that's specific to the parent table (unnesting).

A column underlying a duality view is usually of a SQL scalar type (e.g. VARCHAR2, NUMBER), and the field mapped to it is of a corresponding JSON scalar type (e.g. string, number).

There are two ways to place the document fields produced by the columns of a nonroot table:^Foot 2

• Put the fields in their own object, which is the value of a field in the parent object. The view definition specifies this nest field for the table. For example, in Example 3-2 field teamInfo is the nest field for table team.

  In this case, we speak indifferently of the table, its columns, and the fields corresponding to those columns as being nested into the parent object as the value of that nest field.

• Add the fields directly to the parent object. In this case no nest field is named in the view definition. In place of a nest field, the definition has keyword UNNEST. This is shown in Example 3-3 and Example 3-7.

Nesting (absence of keyword UNNEST) is the default behavior.

A (nonflex) JSON-type column in an underlying table is handled the same way as a scalar-type column.

If the table is nested then the field mapped to a JSON-type column is included in the object that's the value of the nest field of the parent object.

For example, if the nest field of the parent-object is info, and the JSON-type column has value 25 and is mapped to field area, then the value of field info is an object that includes field area:

{..., "info" : {..., "area":25, ...}}

If the table is unnested then field area is placed in the parent object, and there is no nest field info:

{..., "area":25, ...}

Nothing changes, if the value of a JSON-type column is an object. For example, if the area field mapped to the column has value {"width":2, "length":3}, then nesting produces this:

{..., "info" : {..., "area":{"width":2, "length":3}, ...}}

And unnesting produces this:

{..., "area":{"width":2, "length":3}, ...}

Now let's consider flex columns (see Schema Flexibility with JSON Columns in Duality Views). A flex column is a JSON-type column whose value is always an object, and whose purpose is to be a container for new, unrecognized incoming fields.

That is, the fields a flex column contains aren't defined at the outset as part of the duality view; they're created on the fly as applications insert and update documents supported by the view.

Because its value is always an object, a flex column's data is always unnested, as if the column were a table with a single column. The flex column is not itself mapped to any nest field, and the fields in its stored object are placed directly in the parent object.

We refer to this unnesting of a flex column as merging its fields into the parent object.

For example, suppose flex column stuff contains this object:

{"width":2, "length":3}

Each of that object's fields is merged into the parent object:

{..., "width":2, "length":3, ...}

This happens regardless of whether the table containing the flex column is itself nested or unnested; that is, the merging of a flex column is independent of whether scalar columns in the same table are nested or unnested.