A duality-view flex column stores (in an underlying table) JSON objects whose fields aren't predefined: they're not mapped individually to specific underlying columns. Unrecognized fields of an object in a document you insert or update are automatically added to the flex column for that object's underlying table.

You can thus add fields to the document object produced by a duality view with a flex column underlying that object, without redefining the duality view. This provides another kind of schema flexibility to a duality view, and to the documents it supports. If a given underlying table has no column identified in the view as flex, then new fields are not automatically added to the object produced by that table. Add flex columns where you want this particular kind of flexibility.

Note that it's technically incorrect to speak of a flex column of a table. A flex column is a duality-view column that's designated as flex — flex for the view.

Consider table person, with three relational columns and a JSON-type column, extras, whose values must be JSON objects.

CREATE TABLE person (
  pid    NUMBER PRIMARY KEY,
  first  VARCHAR2(20),
  last   VARCHAR2(20),
  extras JSON (OBJECT));

Duality view person_merge_dv maps each of the columns of table person except column extras to a document field. It declares column extras as a flex column. Here is the view definition, in GraphQL and SQL:^Foot 1

GraphQL:

CREATE JSON DUALITY VIEW person_merge_dv AS 
  person @update @insert @delete
    {_id       : pid,
     firstName : first,
     lastName  : last,
     extras @flex};

SQL:

CREATE JSON RELATIONAL DUALITY VIEW person_merge_dv AS
  SELECT JSON {'_id'       : p.pid,
               'firstName' : p.first,
               'lastName'  : p.last,
               p.extras AS FLEX COLUMN}
    FROM person p WITH UPDATE INSERT DELETE;

When inserting a document into view person_merge_dv, any fields unrecognized for the object produced by table person (in this case field nickName) are added to flex column person.extras. The object produced by table person is the top-level object of the document; field nickName is added to that object.

INSERT INTO person_merge_dv VALUES ('{"_id"       : 2,
                                      "firstName" : "John",
                                      "nickName"  : "Anon Y",
                                      "lastName"  : "Doe"}');

Selecting the inserted document shows that the fields stored in the flex column's object, as well as the fields explicitly mapped to other columns, are present in the same object. Field nickName has been merged from the object in the flex column into the object produced by the flex column's table, person.

SELECT pdv.data FROM person_merge_dv pdv
           WHERE pdv.data."_id" = 2;

{"_id":2,"firstName":"John","lastName":"Doe","nickName":"Anon Y"}

Querying table person shows that field nickName was included in the JSON object that is stored in flex column extras. (We assume here that table person is empty before the document insertion into view person_merge_dv — but see below.)

SELECT p.* FROM person p;

PID FIRST  LAST EXTRAS
--- -----  ---- ------
  2 John   Doe  {"nickName":"Anon Y"}

Note that if column person.extras is shared with another duality view then changes to its content are reflected in both views. This may or may not be what you want; just be aware of it.

For example, table person is defined here the same as in Embedding Values from JSON Columns into Documents. Given that Embedding Values from JSON Columns into Documents inserts object {"middleName":"X", "nickName":"Anon X"} into the same column, person.extras, that insertion plus the above insertion into duality view person_merge_dv result in both objects being present in the table:

SELECT p.* FROM person p;

PID FIRST  LAST EXTRAS
--- -----  ---- ------
  1 Jane   Doe  {"middleName":"X","nickName":"Anon X"}

  2 John   Doe  {"nickName":"Anon Y"}

Both duality views use the data for Jane Doe and John Doe, but they use the objects in column extras differently. View person_embed_dv embeds them as the values of field moreInfo; view person_merge_dv merges their fields at the top level.

SELECT pdv.data FROM person_embed_dv pdv;

{"_id":1,"firstName":"Jane","lastName":"Doe",
 "moreInfo":{"middleName":"X","nickName":"Anon X"}}
{"_id":2,"firstName":"John","lastName":"Doe",
 "moreInfo":{"nickName":"Anon Y"}}

SELECT pdv.data FROM person_merge_dv pdv;

{"_id":1,"firstName":"Jane","lastName":"Doe",
 "middleName":"X","nickName":"Anon X"}
{"_id":2,"firstName":"John","lastName":"Doe",
 "nickName":"Anon Y"}

Different views can present the same information in different forms. This is as true of duality views as it is of non-duality views.