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:

• Replicating Business Objects with Oracle JSON Relation Duality and GoldenGate Data Streams and Handling Special Data Types - JSON for complete information about Oracle GoldenGate logical replication of duality views

• ALTER JSON RELATIONAL DUALITY VIEW and CREATE JSON RELATIONAL DUALITY VIEW in Oracle AI Database SQL Language Reference for the SQL syntax to enable and disable logical replication of duality views

Note:

Unless called out explicitly to be otherwise:

• The examples here do not depend on each other in any way. In particular, there is no implied sequencing among them.

• Examples here that make use of duality views use the views defined in Creating Duality Views that are defined using UNNEST: Example 3-1, Example 3-3, and Example 3-5.

• Examples here that make use of tables use the tables defined in Car-Racing Example, Tables.

---

See Also:

• DROP VIEW in Oracle AI Database SQL Language Reference

• Product page Simple Oracle Document Access (SODA) and book Oracle AI Database Introduction to Simple Oracle Document Access (SODA).

• Product page Oracle Database API for MongoDB and book Oracle AI Database API for MongoDB.

• Product page Oracle REST Data Services (ORDS) and book Oracle REST Data Services Developer’s Guide