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

Note:

Unless called out explicitly to be otherwise:

Note:

In a general sense, "updating" includes update, insert, and delete operations. This topic is only about update operations, which modify one or more existing documents or their underlying tables. Insert and delete operations are covered in topics Inserting Documents/Data Into Duality Views and Deleting Documents/Data From Duality Views, respectively.

An update operation on a duality view can update (that is, replace) complete documents, or it can update the values of one or more fields of existing objects. An update to an array-valued field can include the insertion or deletion of array elements.

An update operation cannot add or remove members (field–value pairs) of any object that's explicitly defined by a duality view. For the same reason, an update can't add or remove objects, other than what the view definition provides for.

Any such update would represent a change in the view definition, which specifies the structure and typing of the documents it supports. If you need to make this kind of change then you must redefine the view; you can do that using CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW.

On the other hand, a JSON value defined by an underlying column that's of data type JSON is, by default, unconstrained — any changes to it are allowed, as long as the resulting JSON is well-formed. Values that correspond to a JSON-type column in an underlying table are constrained only by a JSON schema, if any, that applies to that column.

See Also:

JSON Schema in Oracle Database JSON Developer’s Guide

Updating a row of a table that underlies one or more duality views updates all documents (supported by any duality view) that have data corresponding to (that is, taken from) data in that table row. (Other data in the updated documents is unchanged.)

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.

Note:

In general, if you produce SQL character data of a type other than NVARCHAR2, NCLOB, and NCHAR from a JSON string, and if the character set of that target data type is not Unicode-based, then the conversion can undergo a lossy character-set conversion for characters that can't be represented in the character set of that SQL type.

Tip:

Trying to update a document without first reading it from the database can result in several problems, including lost writes and runtime errors due to missing or invalid fields.

When updating, follow these steps:

  1. Fetch the document from the database.

  2. Make changes to a local copy of the document.

  3. Try to save the updated local copy to the database.

  4. If the update attempt (step 3) fails because of a concurrent modification or an ETAG mismatch, then repeat steps 1-3.

See also Using Optimistic Concurrency Control With Duality Views.

Example 5-8 Updating an Entire JSON Document in a Duality View — Using SQL

This example replaces the race document in duality view race_dv whose document identifier (field, _id) has value 201. (See Example 3-5 for the corresponding definition of duality view race_dv.)

The example uses SQL operation UPDATE to do this, setting that row of the single JSON column (DATA) of the view to the new value. It selects and serializes/pretty-prints the document before and after the update operation using SQL/JSON function json_value and Oracle SQL function json_serialize, to show the change. The result of serialization is shown only partially here.

The new, replacement JSON document includes the results of the race, which includes the race date, the podium values (top-three placements), and the result values for each driver. 

SELECT json_serialize(DATA PRETTY)
  FROM race_dv WHERE json_value(DATA, '$._id.numberOnly()') = 201;

UPDATE race_dv
  SET DATA = ('{"_id"      : 201,
                "_metadata" : {"etag" : "2E8DC09543DD25DC7D588FB9734D962B"},
                "name"      : "Bahrain Grand Prix",
                "laps"      : 57,
                "date"      : "2022-03-20T00:00:00",
                "podium"    : {"winner"         : {"name" : "Charles Leclerc",
                                                   "time" : "01:37:33.584"},
                               "firstRunnerUp"  : {"name" : "Carlos Sainz Jr",
                                                   "time" : "01:37:39.182"},
                               "secondRunnerUp" : {"name" : "Lewis Hamilton",
                                                   "time" : "01:37:43.259"}},
                "result"    : [ {"driverRaceMapId" : 3,
                                 "position"        : 1,
                                 "driverId"        : 103,
                                 "name"            : "Charles Leclerc"},
                                {"driverRaceMapId" : 4,
                                 "position"        : 2,
                                 "driverId"        : 104,
                                 "name"            : "Carlos Sainz Jr"},
                                {"driverRaceMapId" : 9,
                                 "position"        : 3,
                                 "driverId"        : 106,
                                 "name"            : "Lewis Hamilton"},
                                {"driverRaceMapId" : 10,
                                 "position"        : 4,
                                 "driverId"        : 105,
                                 "name"            : "George Russell"} ]}')
    WHERE json_value(DATA, '$._id.numberOnly()') = 201;

COMMIT;

SELECT json_serialize(DATA PRETTY)
  FROM race_dv WHERE json_value(DATA, '$._id.numberOnly()') = 201;

Example 5-9 Updating an Entire JSON Document in a Duality View — Using REST

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

curl --request PUT \
  --url http://localhost:8080/ords/janus/race_dv/201 \
  --header 'Content-Type: application/json' \
  --data '{"_id"      : 201,
           "_metadata" : {"etag":"2E8DC09543DD25DC7D588FB9734D962B"},
           "name"      : "Bahrain Grand Prix",
           "laps"      : 57,
           "date"      : "2022-03-20T00:00:00",
           "podium"    : {"winner"         : {"name" : "Charles Leclerc",
                          "time"           : "01:37:33.584"},
                          "firstRunnerUp"  : {"name" : "Carlos Sainz Jr",
                                              "time" : "01:37:39.182"},
                          "secondRunnerUp" : {"name" : "Lewis Hamilton",
                                              "time" : "01:37:43.259"}},
           "result"    : [ {"driverRaceMapId" : 3,
                            "position"        : 1,
                            "driverId"        : 103,
                            "name"            : "Charles Leclerc"},
                           {"driverRaceMapId" : 4,
                            "position"        : 2,
                            "driverId"        : 104,
                            "name"            : "Carlos Sainz Jr"},
                           {"driverRaceMapId" : 9,
                            "position"        : 3,
                            "driverId"        : 106,
                            "name"            : "Lewis Hamilton"},
                           {"driverRaceMapId" : 10,
                            "position"        : 4,
                            "driverId"        : 105,
                            "name"            : "George Russell"}} ]}'

Response:

200 OK
{"_id"      : 201,
 "name"      : "Bahrain Grand Prix",
 "laps"      : 57,
 "date"      : "2022-03-20T00:00:00",
 "podium"    : {"winner"         : {"name": "Charles Leclerc",
                                    "time": "01:37:33.584"},
                ...},
 "result"    : [ {"driverRaceMapId" : 3, ...} ],
 ...}

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-10 Updating Part of a JSON Document in a Duality View

This example replaces the value of field name of each race document in duality view race_dv whose field name matches the LIKE pattern Bahr%. It uses SQL operation UPDATE and Oracle SQL function json_transform to do this. The new, replacement document is the same as the one replaced, except for the value of field name.

Operation SET of function json_transform is used to perform the partial-document update.

The example selects and serializes/pretty-prints the documents before and after the update operation using SQL/JSON function json_value and Oracle SQL function json_serialize. The result of serialization is shown only partially here, and in the car-racing example as a whole there is only one document with the matching race name. 


SELECT json_serialize(DATA PRETTY)
  FROM race_dv WHERE json_value(DATA, '$.name') LIKE 'Bahr%';

UPDATE race_dv dv
  SET DATA = json_transform(DATA, SET '$.name' = 'Blue Air Bahrain Grand Prix')
    WHERE dv.DATA.name LIKE 'Bahr%';
 
COMMIT;

SELECT json_serialize(DATA PRETTY)
  FROM race_dv WHERE json_value(DATA, '$.name') LIKE 'Bahr%';

Note that replacement of the value of an existing field applies also to fields, such as field podium of view race_dv, which correspond to an underlying table column of data-type JSON.

Note:

Field etag is not passed as input when doing a partial-document update, so no ETAG-value comparison is performed by the database in such cases. This means that you cannot use optimistic concurrency control for partial-document updates.

Example 5-11 Updating Interrelated JSON Documents — Using SQL

Driver Charles Leclerc belongs to team Ferrari, and driver George Russell belongs to team Mercedes. This example swaps these two drivers between the two teams, by updating the Mercedes and Ferrari team documents.

Because driver information is shared between team documents and driver documents, field teamID of the driver documents for those two drivers automatically gets updated appropriately when the team documents are updated.

Alternatively, if it were allowed then we could update the driver documents for the two drivers, to change the value of teamId. That would simultaneously update the two team documents. However, the definition of view driver_dv disallows making any changes to fields that are supported by table team. Trying to do that raises an error, as shown in Example 5-13. 

-- Update (replace) entire team documents for teams Mercedes and Ferrari,
-- to swap drivers Charles Leclerc and George Russell between the teams.
-- That is, redefine each team to include the new set of drivers. 
UPDATE team_dv dv
  SET DATA = ('{"_id"       : 303,
                "_metadata" : {"etag" : "039A7874ACEE6B6709E06E42E4DC6355"},
                "name"      : "Mercedes",
                "points"    : 40,
                "driver"    : [ {"driverId" : 106,
                                 "name"     : "Lewis Hamilton",
                                 "points"   : 15},
                                {"driverId" : 103,
                                 "name"     : "Charles Leclerc",
                                 "points"   : 25} ]}')
    WHERE dv.DATA.name LIKE 'Mercedes%';

UPDATE team_dv dv
  SET DATA = ('{"_id"       : 302,
                "_metadata" : {"etag" : "DA69DD103E8BAE95A0C09811B7EC9628"},
                "name"      : "Ferrari",
                "points"    : 30,
                "driver"    : [ {"driverId" : 105,
                                 "name"     : "George Russell",
                                 "points"   : 12},
                                {"driverId" : 104,
                                 "name"     : "Carlos Sainz Jr",
                                 "points"   : 18} ]}')
    WHERE dv.DATA.name LIKE 'Ferrari%';

COMMIT;

-- Show that the driver documents reflect the change of team
-- membership made by updating the team documents.
SELECT json_serialize(DATA PRETTY) FROM driver_dv dv
  WHERE dv.DATA.name LIKE 'Charles Leclerc%';

SELECT json_serialize(DATA PRETTY) FROM driver_dv dv
  WHERE dv.DATA.name LIKE 'George Russell%';

Example 5-12 Updating Interrelated JSON Documents — Using REST

This examples uses Oracle REST Data Services (ORDS) to do the same thing as Example 5-11. It updates teams Mercedes and Ferrari by doing PUT operations on team_dv/303 and team_dv/302, respectively. The database user (schema) that owns the example duality views is shown here as user JANUS. 

curl --request PUT \
  --url http://localhost:8080/ords/janus/team_dv/303 \
  --header 'Content-Type: application/json' \
  --data '{"_id"       : 303,
           "_metadata" : {"etag":"438EDE8A9BA06008C4DE9FA67FD856B4"},
           "name"      : "Mercedes",
           "points"    : 40,
           "driver"    : [ {"driverId" : 106,
                            "name"     : "Lewis Hamilton",
                            "points"   : 15},
                           {"driverId" : 103,
                            "name"     : "Charles Leclerc",
                            "points"   : 25} ]}'

You can use GET operations to check that the driver documents reflect the change of team membership made by updating the team documents. The URLs for this are encoded versions of these:

  • http://localhost:8080/ords/janus/driver_dv/?q={"name":{"$eq":"Charles Leclerc"}}

  • http://localhost:8080/ords/janus/driver_dv/?q={"name":{"$eq":"George Russell"}}

curl --request GET \
  --url 'http://localhost:8080/ords/janus/driver_dv/?q=%7B%22name%22%3A%7B%22%24eq%22%3A%22Charles%20Leclerc%22%7D%7D'

Response:

200 OK
{"items" : [ {"_id"    : 103,
              "name"   : "Charles Leclerc",
              "points" : 25,
              "teamId" : 303,
              "team"   : "Mercedes",...} ],
 ...)
curl --request GET \
  --url 'http://localhost:8080/ords/janus/driver_dv/?q=%7B%22name%22%3A%7B%22%24eq%22%3A%22George%20Russell%22%7D%7D'

Response:

200 OK
{"items" : [ {"_id"    : 105,
              "name"   : "George Russell",
              "points" : 12,
              "teamId" : 302,
              "team"   : "Ferrari",...} ],
 ...)

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-13 Attempting a Disallowed Updating Operation Raises an Error — Using SQL

This example tries to update a field for which the duality view disallows updating, raising an error. (Similar behavior occurs when attempting disallowed insert and delete operations.)

The example tries to change the team of driver Charles Leclerc to team Ferrari, using view driver_dv. This violates the definition of this part of that view, which disallows updates to any fields whose underlying table is team: 

(SELECT JSON {'_id' : t.team_id,
              'team'   : t.name WITH NOCHECK}
   FROM team t WITH NOINSERT NOUPDATE NODELETE
UPDATE driver_dv dv
  SET DATA = ('{"_id"       : 103,
                "_metadata" : {"etag" : "E3ACA7412C1D8F95D052CD7D6A3E90C9"},
                "name"      : "Charles Leclerc",
                "points"    : 25,
                "teamId"    : 303,
                "team"      : "Ferrari",
                "race"      : [ {"driverRaceMapId" : 3,
                                 "raceId"          : 201,
                                 "name"            : "Bahrain Grand Prix",
                                 "finalPosition"   : 1} ]}')
  WHERE dv.DATA."_id" = 103;

UPDATE driver_dv dv
*
ERROR at line 1:
ORA-40940: Cannot update field 'team' corresponding to column 'NAME' of table
'TEAM' in JSON Relational Duality View 'DRIVER_DV': Missing UPDATE annotation
or NOUPDATE annotation specified.

Note that the error message refers to column NAME of table TEAM.

Example 5-14 Attempting a Disallowed Updating Operation Raises an Error — Using REST

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

curl --request PUT \
  --url http://localhost:8080/ords/janus/driver_dv/103 \
  --header 'Accept: application/json' \
  --header 'Content-Type: application/json' \
  --data '{"_id"       : 103,
           "_metadata" : {"etag":"F7D1270E63DDB44D81DA5C42B1516A00"},
           "name"      : "Charles Leclerc",
           "points"    : 25,
           "teamId"    : 303,
           "team"      : "Ferrari",
           "race"      : [ {"driverRaceMapId" : 3,
                            "raceId"          : 201,
                            "name"            : "Bahrain Grand Prix",
                            "finalPosition"   : 1} ]}'

Response:

HTTP/1.1 412 Precondition Failed
{
       "code": "PredconditionFailed",
    "message": "Predcondition Failed",
       "type": "tag:oracle.com,2020:error/PredconditionFailed",
   "instance": "tag:oracle.com,2020:ecid/LVm-2DOIAFUkHzscNzznRg"
}

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

_________________________________________________________

  • Trigger Considerations When Using Duality Views
    Triggers that modify data in tables underlying duality views can be problematic. Oracle recommends that you avoid using them. If you do use them, here are some things consider, to avoid problems.

Related Topics

Parent topic: Using JSON-Relational Duality Views

5.3.1 Trigger Considerations When Using Duality Views

Triggers that modify data in tables underlying duality views can be problematic. Oracle recommends that you avoid using them. If you do use them, here are some things consider, to avoid problems.

As a general rule, in a trigger body avoid changing the values of identifying columns and columns that contribute to the ETAG value of a duality view.

For any trigger that you create on a table underlying a duality view, Oracle recommends the following. Otherwise, although no error is raised when you create the trigger, an error can be raised when it is fired. There are two problematic cases to consider. ("firing <DML>" here refers to a DML statement that results in the trigger being fired.)

  • Case 1: The trigger body changes the value of an identifier column (such as a primary-key column), using correlation name (pseudorecord) :NEW. For example, a trigger body contains :NEW.zipcode = 94065.

    Do not do this unless the firing <DML> sets the column value to NULL. Primary-key values must never be changed (except from a NULL value).

  • Case 2 (rare): The trigger body changes the value of a column in a different table from the table being updated by the firing <DML>, and that column contributes to the ETAG value of a duality view — any duality view.

    For example:

    • The firing <DML> is UPDATE emp SET zipcode = '94065' WHERE emp_id = '40295';.

    • The trigger body contains the DML statement UPDATE dept SET budget = 10000 WHERE dept_id = '592';.

    • Table dept underlies some duality view, and column dept.budget contributes to the ETAG value of that duality view.

    This is because updating such a column changes the ETAG value of any documents containing a field corresponding to the column. This interferes with concurrency control, which uses such values to guard against concurrent modification. An ETAG change from a trigger is indistinguishable from an ETAG change from another, concurrent session.

See Also:

Parent topic: Updating Documents/Data in Duality Views