JSON Duality Views

You can use Oracle Database API for MongoDB with documents supported by a JSON-relational duality view. Such documents are automatically generated, based on underlying table data.

JSON-relational duality views are supported only in Oracle AI Database Release 26ai or later.

A JSON-relational duality view exposes data stored in relational database tables as JSON documents. The documents are materialized on demand, not stored as such. Duality views give data both a conceptual and an operational duality: it’s organized both relationally and hierarchically. You can base different duality views on data stored in one or more of the same tables, providing different JSON hierarchies over the same, shared data.

This means that applications can access (create, query, modify) the same data as a collection of JSON documents or as a set of related database tables and columns, and both approaches can be employed at the same time.

You can manipulate the documents realized by duality views in the ways you’re used to, using your usual drivers, frameworks, tools, and development methods. In particular, applications can use any programming languages.

An application uses a document collection that’s supported by a duality view as if the documents were stored in a table column of JSON data type. You use the duality-view name as collection-name argument in MongoDB API calls.

Note: If the duality view name wasn’t quoted when the view was created then be sure to pass the name as uppercase in MongoDB API calls. For example if my_dv was used when the view was created then pass "MY_DV" as the collection name. If "my_dv" was used when the view was created then pass "my_dv".

As one important use case, a MongoDB API application can easily make use of any existing database data — just create one or more duality views over that data, to support JSON collections.

An important aspect of the JSON-relational duality is that it lets different kinds of JSON document share common data (as well as share the same data in relational tables). How you define a duality view determines what data gets shared, and how (who can perform what kinds of updating operations on which document parts).

Creating JSON Duality Views for Use With the MongoDB API

You cannot create a JSON-relational view using the MongoDB API. You can use SQL statement CREATE JSON RELATIONAL DUALITY VIEW to do that.

All duality views are compatible with the MongoDB API. They always have field _id as their document identifier. The value of field _id specifies the document fields whose values are the primary-key columns of the root table that underlies the duality view.

• If there is only one primary-key column, then you use that column as the value of field _id when you define the duality view. For example: _id : race_id, as in Example 1-1.

• If there are multiple primary-key columns, then you use an object as the value of field _id when you define the view. The members of the object specify document fields whose values are the primary-key columns. For example, suppose you have a car-racing duality view with two primary-key columns, race_id and race_year, which together uniquely identify a root-table row, but neither of which does so alone. This _id field in the duality view definition maps document fields raceId and year to primary-key columns race_id and race_year, respectively:

  _id : **{ **raceId** : **race_id**, **year** : **race_year** } **

  If there is only one primary-key column, you can nevertheless use an object value for _id, if you like. Doing so lets you provide a meaningful field name. For example, here the single primary-key column, race_id, provides the value of field raceId as well as the value of field _id:

  _id : **{**raceId** : **race_id**}**

The value(s) provided by field _id for the primary key column(s) it maps to must of course be insertable into those columns, which means that their data types must be compatible with the column types. For example, if field _id maps to a single primary-key column that is of SQL type NUMBER, then the _id value of a document you insert must be numeric. Otherwise, an error is raised for the insertion attempt.

If you don’t explicitly include an _id field in a document that you insert, then it is added automatically, with an ObjectId value. (You can also explicitly use an ObjectId value in an _id field.) An ObjectId value can only be used for a field that the duality view maps to a column of SQL type RAW.

Example 1-1 Creating JSON Duality View RACE_DV Using GraphQL

This example creates a duality view, race_dv, that supports car-racing race documents.

CREATE JSON RELATIONAL DUALITY VIEW **race_dv** AS
  **race** **@insert** **@update** **@delete**
    {**_id** : **race_id**
     name   : name
     laps   : laps **@noupdate**
     date   : race_date
     podium : podium **@nocheck**
     result : **driver_race_map** **@insert** **@update** **@delete**
       **[** {driverRaceMapId : driver_race_map_id
          position        : position
          driver **@noinsert** **@update** **@nodelete**
                 **@unnest** {driverId : driver_id,
                          name     : name}} **]**};

This definition is the same as the one in Creating Duality View RACE_DV Using GraphQL in JSON-Relational Duality Developer’s Guide. See that documentation for similar duality view creations for driver and race documents. The SQL code in this example embeds Oracle GraphQL code. Alternatively you can use only SQL code for the definition, as in Creating Duality View RACE_DV, With Unnested Driver Information Using SQL.

This duality view supports JSON documents where the race objects look like this — they contain a result field whose value is an array of objects that specify the drivers and their resulting positions in the given race:

{"**_id**" : 201,
"name" : "Bahrain Grand Prix",
"laps" : 57,
"date" : "2022-03-20T00:00:00",
"podium" : {...},
"**result**" : **[** {"driverRaceMapId" : 3,
              "position" : 1,
              "driverId" : 103,
              "name" : "Charles Leclerc"},... **]**}

The value of document identifier field _id is taken from the single primary-key column, race_id of the root table, race. For example, the document identified by the _id field whose value is 201 is generated from the row of data that has 201 in primary-key column race_id of the root table (race) underlying the duality view.

Generation of the documents supported by the view automatically joins data from columns driver_race_map_id, position and driver_id from table driver_race_map, and column name from table driver.

The annotations (GraphQL directives) @insert, @update, and @delete are used to specify that applications can insert, update, and delete documents supported by the view, respectively, but that they can only perform update operations on the driver field of the documents (a driver cannot be inserted or deleted when you modify a race document) and you cannot update the laps field (you cannot change the number of laps when you update a race document).

The @nocheck annotation applied to column podium specifies that updating field podium in a race document does not contribute to checking the state/version of the document (its ETAG value).