Deleting Documents/Data From Duality Views

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

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.

Deleting a row from a table that is the root (top-level) table of one or more duality views deletes the documents that correspond to that row from those views.

Example 5-6 Deleting a JSON Document from Duality View RACE_DV — Using SQL

This example deletes the race document with _id^Foot 1 value 202 from the race duality view, race_dv. (This is one of the documents with race name Saudi Arabian GP.)

The corresponding rows are deleted from underlying tables race and driver_race_map (one row from each table).

Nothing is deleted from the driver table, however, because in the race_dv definition table driver is annotated with NODELETE (see Updating Rule 5.) Pretty-printing documents for duality views race_dv and driver_dv shows the effect of the race-document deletion.

SELECT json_serialize(DATA PRETTY) FROM race_dv;
SELECT json_serialize(DATA PRETTY) FROM driver_dv;

DELETE FROM race_dv dv WHERE dv.DATA."_id".numberOnly() = 202;

SELECT json_serialize(DATA PRETTY) FROM race_dv;
SELECT json_serialize(DATA PRETTY) FROM driver_dv;

The queries before and after the deletion show that only this race document was deleted — no driver documents were deleted:

{"_id"      : 202,
 "_metadata" : {"etag" : "7E056A845212BFDE19E0C0D0CD549EA0",
                "asof" : "00000000000C20B1"},
 "name"      : "Saudi Arabian Grand Prix",
 "laps"      : 50,
 "date"      : "2022-03-27T00:00:00",
 "podium"    : {},
 "result"    : []}

Example 5-7 Deleting a JSON Document from Duality View RACE_DV — Using REST

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

curl --request GET \
  --url http://localhost:8080/ords/janus/race_dv/
curl --request GET \
  --url http://localhost:8080/ords/janus/driver_dv/

curl --request DELETE \
  --url http://localhost:8080/ords/janus/race_dv/202

Response from DELETE:

200 OK
{"rowsDeleted" : 1}

Using a GET request on each of the duality views, race_dv and driver_dv, both before and after the deletion shows that only this race document was deleted — no driver documents were deleted:

{"_id"      : 202,
 "_metadata" : {"etag" : "7E056A845212BFDE19E0C0D0CD549EA0",
                "asof" : "00000000000C20B1"},
 "name"      : "Saudi Arabian Grand Prix",
 "laps"      : 50,
 "date"      : "2022-03-27T00:00:00",
 "podium"    : {},
 "result"    : [],
 "links"     : [ {"rel"  : "self",
                  "href" : "http://localhost:8080/ords/janus/race_dv/202"} ]} ],

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.