5 Using JSON-Relational Duality Views

You can insert (create), update, delete, and query documents or parts of documents supported by a duality view. You can list information about a duality view.

Document-centric applications typically manipulate JSON documents directly, using either SQL/JSON functions or a client API such as Oracle Database API for MongoDB, Simple Oracle Document Access (SODA), or Oracle REST Data Services (ORDS). Database applications and features, such as analytics, reporting, and machine-learning, can manipulate the same data using SQL, PL/SQL, JavaScript, or C (Oracle Call Interface).

SQL and other database code can also act directly on data in the relational tables that underlie a duality view, just as it would act on any other relational data. This includes modification operations. Changes to data in the underlying tables are automatically reflected in the documents provided by the duality view. Example 5-3 illustrates this.

The opposite is also true, so acting on either the documents or the data underlying them affects the other automatically. This reflects the duality between JSON documents and relational data provided by a duality view.

Operations on tables that underlie a document view automatically affect documents supported by the view, as follows:

  • Insertion of a row into the root (top-level) table of a duality view inserts a new document into the view. For example, inserting a row into the driver table inserts a driver document into view driver_dv.

    However, since table driver provides only part of the data in a driver document, only the document fields supported by that table are populated; the other fields in the document are missing or empty.

  • Deletion of a row from the root table deletes the corresponding document from the view.

  • Updating a row in the root table updates the corresponding document.

    As with insertion of a row, only the document fields supported by that table data are updated; the other fields are not changed.

Note:

An update of documents supported by a JSON-relational duality view, or of the table data underlying them, is reported by SQL as having updated some rows of data, even if the content of that data is not changed. This is standard SQL behavior. A successful update operation is always reported as having updated the rows it targets. This also reflects the fact that there can be triggers or row-transformation operators that accompany an update operation and that, themselves, can change the data.

Operations on duality views themselves include creating, dropping (deleting), and listing them, as well as listing other information about them.

  • See Creating Duality Views for examples of creating duality views.

  • You can drop (delete) an existing duality view as you would drop any view, using SQL command DROP VIEW.

    Duality views are independent, though they typically contain documents that have some shared data. For example, you can drop duality view team_dv without that having any effect on duality view driver_dv. Duality views do depend on their underlying tables, however.

    Caution:

    Do not drop a table that underlies a duality view, as that renders the view unusable.

  • You can use static data dictionary views to obtain information about existing duality views. See Obtaining Information About a Duality View.

You can of course replicate the tables underlying a JSON-relational duality view. Alternatively (or additionally), you can use Oracle GoldenGate logical replication to replicate the documents supported by a duality view to other Oracle databases and to non-Oracle databases, including document databases and NoSQL key/value databases.

See:

Note:

Unless called out explicitly to be otherwise:

_________________________________________________________

  • Inserting Documents/Data Into Duality Views
    You can insert a JSON document into a duality view directly, or you can insert data into the tables that underlie a duality view. Examples illustrate these possibilities.
  • Deleting Documents/Data From Duality Views
    You can delete a JSON document from a duality view directly, or you can delete data from the tables that underlie a duality view. Examples illustrate these possibilities.
  • Updating Documents/Data in Duality Views
    You can update a JSON document in a duality view directly, or you can update data in the tables that underlie a duality view. You can update a document by replacing it entirely, or you can update only some of its fields. Examples illustrate these possibilities.
  • Using Optimistic Concurrency Control With Duality Views
    You can use optimistic/lock-free concurrency control with duality views, writing JSON documents or committing their updates only when other sessions haven't modified them concurrently.
  • Using the System Change Number (SCN) of a JSON Document
    A system change number (SCN) is a logical, internal, database time stamp. Metadata field asof records the SCN for the moment a document was retrieved from the database. You can use the SCN to ensure consistency when reading other data.
  • Optimization of Operations on Duality-View Documents
    Operations on documents supported by a duality view — in particular, queries — are automatically rewritten as operations on the underlying table data. This optimization includes taking advantage of indexes. Because the underlying data types are fully known, implicit runtime type conversion can generally be avoided.
  • Obtaining Information About a Duality View
    You can obtain information about a duality view, its underlying tables, their columns, and key-column links, using static data dictionary views. You can also obtain a JSON-schema description of a duality view, which includes a description of the structure and JSON-language types of the JSON documents it supports.

See Also: