5.1 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.

Note:

Unless called out explicitly to be otherwise:

Inserting data (a row) into the root table that underlies one or more duality views creates a new document that is supported by each of those views. Only the fields of the view that are provided by that table are present in the document — all other fields are missing.

For example, inserting a row into table race inserts a document into view race_dv (which has table race as its root table), and that document contains race-specific fields; field result is missing, because it's derived from tables driver and driver_race_map, not race.

When inserting a document into a duality view, its field values are automatically converted to the required data types for the corresponding table columns. For example, a JSON field whose value is a supported ISO 8601 date-time format is automatically converted to a value of SQL type DATE, if DATE is the type of the corresponding column. If the type of some field cannot be converted to the required column type then an error is raised.

The value of a field that corresponds to a JSON-type column in an underlying table undergoes no such type conversion. When inserting a textual JSON document you can use the JSON type constructor with keyword EXTENDED, together with extended objects to provide JSON-language scalar values of Oracle-specific types, such as date. For example, you can use a textual field value such as {"$oracleDate" : "2022-03-27"} to produce a JSON-type date value. (You can of course use the same technique to convert textual data to a JSON-type that you insert directly into an underlying table column.)

Tip:

To be confident that a document you insert is similar to, or compatible with, the existing documents supported by a duality view, use the JSON schema that describes those documents as a guide when you construct the document. You can obtain the schema from column JSON_SCHEMA in one of the static dictionary views *_JSON_DUALITY_VIEWS, or by using PL/SQL function DBMS_JSON_SCHEMA.describe. See Obtaining Information About a Duality View.

You can omit any fields you don't really care about or for which you don't know an appropriate value. But to avoid runtime errors it's a good idea to include all fields included in array "required" of the JSON schema.

See Also:

Example 5-1 Inserting JSON Documents into Duality Views, Providing Document-Identifier Fields — Using SQL

This example inserts three documents into view team_dv and three documents into view race_dv. The document-identifer fields, named _id, are provided explicitly here. Field _id corresponds to the identifying columns of the duality-view root table.

The values of field date of the race documents here are ISO 8601 date-time strings. They are automatically converted to SQL DATE values, which are inserted into the underlying race table, because the column of table race that corresponds to field date has data type DATE.

In this example, only rudimentary, placeholder values are provided for fields/columns points (value 0) and podium (value {}). These serve to populate the view and its tables initially, defining the different kinds of races, but without yet recording actual race results.

Because points field/column values for individual drivers are shared between team documents/tables and driver documents/tables, updating them in one place automatically updates them in the other. The fields/columns happen to have the same names for these different views, but that's irrelevant. What matters are the relations among the duality views, not the field/column names.

Like insertions (and deletions), updates can be performed directly on duality views or on their underlying tables (see Example 5-3).

The intention in the car-racing example is for points and podium field values to be updated (replaced) dynamically as the result of car races. That updating is part of the presumed application logic; that is, we assume here that it's done by the application.

However, see Example 7-2 for an example of declaratively defining, as part of a team duality view, the team's points as always being the sum of its drivers' points. This obviates the need for an application to update team points in addition to driver points.

Also assumed as part of the application logic is that a driver's position in a given race contributes to the accumulated points for that driver — the better a driver's position, the more points accumulated. That too is assumed here to be taken care of by application code. 

-- Insert team documents into TEAM_DV, providing field _id.
INSERT INTO team_dv VALUES ('{"_id"   : 301,
                              "name"   : "Red Bull",
                              "points" : 0,
                              "driver" : [ {"driverId" : 101,
                                            "name"     : "Max Verstappen",
                                            "points"   : 0},
                                           {"driverId" : 102,
                                            "name"     : "Sergio Perez",
                                            "points"   : 0} ]}');

INSERT INTO team_dv VALUES ('{"_id"   : 302,
                              "name"   : "Ferrari",
                              "points" : 0,
                              "driver" : [ {"driverId" : 103,
                                            "name"     : "Charles Leclerc",
                                            "points"   : 0},
                                           {"driverId" : 104,
                                            "name"     : "Carlos Sainz Jr",
                                            "points"   : 0} ]}');

INSERT INTO team_dv VALUES ('{"_id"   : 303,
                              "name"   : "Mercedes",
                              "points" : 0,
                              "driver" : [ {"driverId" : 105,
                                            "name"     : "George Russell",
                                            "points"   : 0},
                                           {"driverId" : 106,
                                            "name"     : "Lewis Hamilton",
                                            "points"   : 0} ]}');

-- Insert race documents into RACE_DV, providing field _id.
INSERT INTO race_dv VALUES ('{"_id"   : 201,
                              "name"   : "Bahrain Grand Prix",
                              "laps"   : 57,
                              "date"   : "2022-03-20T00:00:00",
                              "podium" : {}}');

INSERT INTO race_dv VALUES ('{"_id"   : 202,
                              "name"   : "Saudi Arabian Grand Prix",
                              "laps"   : 50,
                              "date"   : "2022-03-27T00:00:00",
                              "podium" : {}}');

INSERT INTO race_dv VALUES ('{"_id"   : 203,
                              "name"   : "Australian Grand Prix",
                              "laps"   : 58,
                              "date"   : "2022-04-09T00:00:00",
                              "podium" : {}}');

Example 5-2 Inserting JSON Documents into Duality Views, Providing Document-Identifier Fields — Using REST

This example uses Oracle REST Data Services (ORDS) to do the same thing as Example 5-1. For brevity it inserts only one document into duality view team_dv and one document into race view race_dv. The database user (schema) that owns the example duality views is shown here as user JANUS.

Insert a document into view team_dv: 

curl --request POST \
  --url http://localhost:8080/ords/janus/team_dv/ \
  --header 'Content-Type: application/json' \
  --data '{"_id"   : 302,
           "name"   : "Ferrari",
           "points" : 0,
           "driver" : [ {"driverId" : 103,
                         "name"     : "Charles Leclerc",
                         "points"   : 0},
                        {"driverId" : 104,
                         "name"     : "Carlos Sainz Jr",
                         "points"   : 0} ]}'

Response:

201 Created

{"_id"      : 302,
 "_metadata" : {"etag" : "DD9401D853765859714A6B8176BFC564",
                "asof" : "0000000000000000"},
 "name"      : "Ferrari",
 "points"    : 0,
 "driver"    : [ {"driverId" : 103,
                  "name"     : "Charles Leclerc",
                  "points"   : 0},
                 {"driverId" : 104,
                  "name"     : "Carlos Sainz Jr",
                  "points"   : 0}],
 "links"     : [ {"rel"  : "self",
                  "href" : "http://localhost:8080/ords/janus/team_dv/302"},
                 {"rel"  : "describedby",
                  "href" :
                   "http://localhost:8080/ords/janus/metadata-catalog/team_dv/item"},
                 {"rel"  : "collection",
                  "href" : "http://localhost:8080/ords/janus/team_dv/"} ]}

Insert a document into view race_dv: 

curl --request POST \
  --url http://localhost:8080/ords/janus/race_dv/ \
  --header 'Content-Type: application/json' \
  --data '{"_id"   : 201,
           "name"   : "Bahrain Grand Prix",
           "laps"   : 57,
           "date"   : "2022-03-20T00:00:00",
           "podium" : {}}'

Response:

201 Created
{"_id"      : 201,
 "_metadata" : {"etag" : "2E8DC09543DD25DC7D588FB9734D962B",
                "asof" : "0000000000000000"},
 "name"      : "Bahrain Grand Prix",
 "laps"      : 57,
 "date"      : "2022-03-20T00:00:00",
 "podium"    : {},
 "result"    : [],
 "links"     : [ {"rel"  : "self",
                  "href" : "http://localhost:8080/ords/janus/race_dv/201"},
                 {"rel"  : "describedby",
                  "href" :
                   "http://localhost:8080/ords/janus/metadata-catalog/race_dv/item"},
                 {"rel"  : "collection",
                  "href" : "http://localhost:8080/ords/janus/race_dv/"} ]}

Note:

For best performance, configure Oracle REST Data Services (ORDS) to enable the metadata cache with a timeout of one second:

cache.metadata.enabled = true
cache.metadata.timeout = 1

See Configuring REST-Enabled SQL Service Settings in Oracle REST Data Services Installation and Configuration Guide.

See Also:

Support for JSON-Relational Duality View in Oracle REST Data Services Developer's Guide

Example 5-3 Inserting JSON Data into Tables

This example shows an alternative to inserting JSON documents into duality views. It inserts JSON data into tables team and race.

The inserted data corresponds to only part of the associated documents — the part that's specific to the view type. Each table has columns only for data that's not covered by another table (the tables are normalized).

Because the table data is normalized, the table-row insertions are reflected everywhere that data is used, including the documents supported by the views.

Here too, as in Example 5-1, the points of a team and the podium of a race are given rudimentary (initial) values. 

INSERT INTO team VALUES (301, 'Red Bull', 0);
INSERT INTO team VALUES (302, 'Ferrari',  0);
   
INSERT INTO race
  VALUES (201, 'Bahrain Grand Prix',       57, DATE '2022-03-20', '{}');
INSERT INTO race
  VALUES (202, 'Saudi Arabian Grand Prix', 50, DATE '2022-03-27', '{}');
INSERT INTO race
  VALUES (203, 'Australian Grand Prix',    58, DATE '2022-04-09', '{}');

Example 5-4 Inserting a JSON Document into a Duality View Without Providing Document-Identifier Fields — Using SQL

This example inserts a driver document into duality view driver_dv, without providing the document-identifier field (_id). The value of this field is automatically generated (because the underlying identifying column (a primary-key column in this case) is defined using INTEGER GENERATED BY DEFAULT ON NULL AS IDENTITY). The example then prints that generated field value. 


-- Insert a driver document into DRIVER_DV, without providing a
--  document-identifier field (_id).  The field is provided
--  automatically, with a generated, unique numeric value.
-- SQL/JSON function json_value is used to return the value into bind
--  variable DRIVERID.
VAR driverid NUMBER;
INSERT INTO driver_dv dv VALUES ('{"name"   : "Liam Lawson",
                                   "points" : 0,
                                   "teamId" : 301,
                                   "team" : "Red Bull",
                                   "race"   : []}')
  RETURNING json_value(DATA, '$._id') INTO :driverid;

SELECT json_serialize(data PRETTY) FROM driver_dv d
  WHERE d.DATA.name = 'Liam Lawson';


{"_id"      : 7,
 "_metadata" : {"etag" : "F9D9815DFF27879F61386CFD1622B065",
                "asof" : "00000000000C20CE"},
 "name"      : "Liam Lawson",
 "points"    : 0,
 "teamId"    : 301,
 "team"      : "Red Bull",
 "race"      : []}

Example 5-5 Inserting a JSON Document into a Duality View Without Providing Document-Identifier Fields — Using REST

This example uses Oracle REST Data Services (ORDS) to do the same thing as Example 5-4. The database user (schema) that owns the example duality views is shown here as user JANUS. 

curl --request POST \
  --url http://localhost:8080/ords/janus/driver_dv/ \
  --header 'Content-Type: application/json' \
  --data '{"name"   : "Liam Lawson",
           "points" : 0,
           "teamId" : 301,
           "team"   : "Red Bull",
           "race"   : []}'

Response:

201 Created
{"_id"      : 7,
 "_metadata" : {"etag" : "F9EDDA58103C3A601CA3E0F49E1949C6",
                "asof" : "00000000000C20CE"},
 "name"      : "Liam Lawson",
 "points"    : 0,
 "teamId"    : 301,
 "team"      : "Red Bull",
 "race"      : [],
 "links"     :
  [ {"rel"  : "self",
     "href" : "http://localhost:8080/ords/janus/driver_dv/23"},
    {"rel"  : "describedby",
     "href" : "http://localhost:8080/ords/janus/metadata-catalog/driver_dv/item"},
    {"rel"  : "collection",
     "href" : "http://localhost:8080/ords/janus/driver_dv/"} ]}

Note:

For best performance, configure Oracle REST Data Services (ORDS) to enable the metadata cache with a timeout of one second:

cache.metadata.enabled = true
cache.metadata.timeout = 1

See Configuring REST-Enabled SQL Service Settings in Oracle REST Data Services Installation and Configuration Guide.

Related Topics

See Also:

Support for JSON-Relational Duality View in Oracle REST Data Services Developer's Guide

Parent topic: Using JSON-Relational Duality Views