Inline Write Augmentation
Write augmentation provides generated fields when a document that's supported by a duality view is inserted or updated. A duality view can define this automatic generation in line; that is, as part of the duality-view definition/creation code (DDL).
Inline write augmentation automatically sets the value of a field in a duality view, and thus the value of its corresponding column, when an incoming document is written. The field can optionally be hidden. Inline write augmentation can ensure that a field that's missing from an incoming document, or that has an improper/incomplete value there, is provided with a proper value.
Inline write augmentation is similar to Inline Read Augmentation, Like inline read-generated fields, inline write-generated fields can have values that are based on the values of other fields (generated or not).
The following are true of a field generated during a write operation:
-
The field must always be mapped to a column. (It can't have more than one mapping to the same column.) The column value corresponds to the generated field value — it persists the value after generating it.
-
It can have any annotation except
CHECK(it doesn't contribute to the calculation of the value of metadata fieldetag). -
You can only define the field generation using a SQL/JSON path expression. You cannot use a SQL expression or a SQL query. (Inline read augmentation allows all three possibilities.)
There are two possibilities for a field that can be generated during a write operation, depending on what you want to happen if an incoming document already contains that field: (1) raise an error or (2) use the incoming field value (no generation):
-
Always generate the field, unconditionally.
The field should never be present in an incoming document; raise an error if it is.
See Example 8-4 and Example 8-7.
-
Provide a default value for the field.
Generate the field only if missing from an incoming document, giving it, otherwise, use the field as presented in the document.
See Example 8-8 and Example 8-9.
You can combine inline read augmentation and inline write augmentation in the same duality view (for different fields). See Example 8-10.
The unconditional behavior is used by default; the second is used if you
specify the field value as being only a default value (which you do using
keywords IF MISSING in SQL or on:"missing" in
GraphQL).
Example 8-4 Inline Write Augmentation: Unconditional, Constant Value
This example always generates field allowances,
constantly providing it with the value 1000.
CREATE TABLE waiter(empno NUMBER PRIMARY KEY,
name VARCHAR2(100),
wage NUMBER,
tips NUMBER,
allowances NUMBER,
cc_last4 VARCHAR2(4),
shift_preference JSON,
shift_prio VARCHAR2(100));SQL:
CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW waiter_dv_gen_constant AS
SELECT JSON {'_id' : empno,
'name' : name,
'wage' : wage,
'tips' : tips,
'allowances' : allowances GENERATED ON WRITE USING PATH '1000'}
FROM waiter e WITH (UPDATE, INSERT, DELETE);GraphQL:
GraphQL directive @generated uses
"write" here as the value of its argument on,
to always generate field allowances. (Without any
on argument, the directive would be an inline read
augmentation.)
CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW waiter_dv AS
waiter @insert @delete @update
{_id : empno,
name : name,
wage : wage,
tips : tips,
allowances : allowances @generated (path : "1000", on : "write")};INSERT INTO waiter_dv_gen_constant
VALUES ('{"_id":1, "name":"Jane Doe", "wage":1000, "tips":100}');
SELECT data FROM waiter_dv_gen_constant;Result:
DATA
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
{"_id":1,"_metadata":{"etag":"352A2222565B00BDDAD937FACB64C645","asof":"0000000000000000"},"name":"Jane Doe","wage":1000,"tips":100,"allowances":1000}
SELECT empno, name, wage, tips, allowances FROM waiter;
Result:
EMPNO NAME WAGE TIPS ALLOWANCES
---------- -------------------- ---------- ---------- ----------
1 Jane Doe 1000 100 1000
Trying to insert a document that already has a field
allowances is an error, because the field is defined to always
be generated:
INSERT INTO waiter_dv_gen_constant
VALUES ('{"_id":2, "name":"Mark Lane", "wage":2000, "tips":200,
"allowances":200}');INSERT INTO waiter_dv_gen_constant
*
ERROR at line 1:
ORA-42727: Cannot insert into JSON-relational duality view 'WAITER_DV_GEN_CONSTANT': The input JSON document is invalid.
JZN-00695: Cannot insert or update this field 'allowances'.
Example 8-5 Inline Write Augmentation: Conditional Logic with Case()
Inline write augmentation often needs to adapt the generated value based on the
incoming payload. When the allowance amount should reflect an
employee's wage tier, embed a case() expression
in the generated path so the view enforces the business rule on every insert or
update.
SQL:
CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW waiter_dv AS
SELECT JSON {'_id' : empno,
'name' : name,
'wage' : wage,
'tips' : tips,
'allowances' : allowances GENERATED ON WRITE USING PATH 'case(@.wage>50,100,50)'}
FROM waiter e WITH (UPDATE, INSERT, DELETE);GraphQL:
CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW waiter_dv AS
waiter @insert @delete @update
{_id : empno,
name : name,
wage : wage,
tips : tips,
allowances : allowances @generated (path : "case(@.wage>50,100,50)", on : "write")};INSERT INTO waiter_dv
VALUES ('{"_id":10, "name":"Jane Doe", "wage":1000, "tips":100}');
INSERT INTO waiter_dv
VALUES ('{"_id":20, "name":"John Smith", "wage":50, "tips":100}');
SELECT data FROM waiter_dv;The SQL and GraphQL definitions both generate the allowances field
by evaluating case(@.wage>50,100,50) during the write. The inserts
supply different wages, and the subsequent SELECT shows that Jane
Doe receives an allowance of 100 because her wage exceeds 50,
while John Smith falls back to the 50 default. The logic lives entirely in the path
expression, so every future write automatically applies the same rule.
Result:
DATA
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
{"_id":10,"_metadata":{"etag":"D8FBEECD9E52067D3CC61E210F19F1D2","asof":"0000000000000000"},"name":"Jane Doe","wage":1000,"tips":100,"allowances":100}
{"_id":20,"_metadata":{"etag":"CCE78A5E5AF6A800F53D03AEC8EB8CAE","asof":"0000000000000000"},"name":"John Smith","wage":50,"tips":100,"allowances":50}
Example 8-6 Inline Write Augmentation: Conditional Logic with
decode()
You can also branch on discrete values instead of ranges. When allowance depends on
tip amounts, use decode() in the generated path so the view maps
tip values to allowance values without post-processing.
SQL:
CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW waiter_dv AS
SELECT JSON {'_id' : empno,
'name' : name,
'wage' : wage,
'tips' : tips,
'allowances' : allowances GENERATED ON WRITE USING PATH 'decode(@.tips,0,0,100)'}
FROM waiter e WITH (UPDATE, INSERT, DELETE);GraphQL:
CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW waiter_dv AS
waiter @insert @delete @update
{_id : empno,
name : name,
wage : wage,
tips : tips,
allowances : allowances @generated (path : "decode(@.tips,0,0,100)", on : "write")};INSERT INTO waiter_dv
VALUES ('{"_id":11, "name":"Jane Doe", "wage":1000, "tips":0}');
INSERT INTO waiter_dv
VALUES ('{"_id":21, "name":"John Smith", "wage":50, "tips":200}');
SELECT data FROM waiter_dv;The generated path decode(@.tips,0,0,100) assigns an
allowance of 0 when tips is zero and 100
for any other value. The inserts demonstrate both branches, and the
SELECT output confirms that the allowance column mirrors the
rules encoded in the path expression.
Result:
DATA
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
{"_id":11,"_metadata":{"etag":"A172F24F3082DBA45A6EB5A49BE65521","asof":"0000000000000000"},"name":"Jane Doe","wage":1000,"tips":0,"allowances":0}
{"_id":21,"_metadata":{"etag":"0F5A01FBD2EE63A6CC06AE0F5BF0D886","asof":"0000000000000000"},"name":"John Smith","wage":50,"tips":200,"allowances":100}
Example 8-7 Inline Write Augmentation: Unconditional, Derived Value
This example always generates field shiftPrio,
providing it with a value derived from document field shiftPref
(that array's first element).
The duality view maps field shiftPref to column
shift_preference. Field shiftPrio is generated
from the value of field shiftPref, an array, by taking the first
array element.
SQL:
CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW waiter_dv_gen_derived AS
SELECT JSON {'_id' : empno,
'name' : name,
'wage' : wage,
'tips' : tips,
'shiftPref' : shift_preference,
'shiftPrio' :
shift_prio GENERATED ON WRITE USING
PATH '$.shiftPref[0]'}
FROM waiter e WITH (UPDATE, INSERT, DELETE);GraphQL:
CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW waiter_dv_gen_derived AS
waiter @insert @delete @update
{_id : empno,
name : name,
wage : wage,
tips : tips,
shiftPref : shift_preference,
shiftPrio :
shift_prio @generated (path : "$.shiftPref[0]", on : "write")};INSERT INTO waiter_dv_gen_derived
VALUES ('{"_id":100, "name":"Jane Doe", "wage":1000, "tips":100,
"shiftPref":[ "A","B","C" ]}');
select * from waiter_dv_gen_derived;
Result:
DATA
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
{"_id":100,"_metadata":{"etag":"B31F9BB133859D1AA0E0907B03C3736B","asof":"0000000000000000"},"name":"Jane Doe","wage":1000,"tips":100,"shiftPref":["A","B","C"],"shiftPrio":"A"}
Trying to insert a document that already has field shiftPrio should
fail, because the field is defined to always be generated:
INSERT INTO waiter_dv_gen_derived
VALUES ('{"_id":17, "name":"Jane Doe", "wage":1000, "tips":100,
"shiftPref":[ "A","B","C" ], "shiftPrio":"B"}');Result:
INSERT INTO waiter_dv_gen_derived
*
ERROR at line 1:
ORA-42727: Cannot insert into JSON-relational duality view 'WAITER_DV_GEN_DERIVED': The input JSON document is invalid.
JZN-00695: Cannot insert or update this field 'shiftPrio'.
Example 8-8 Inline Write Augmentation: Conditional (Default), Constant Value
This example provides field allowances with a default
value of 1000. That is, the field is generated only if it is
missing from an incoming document. This example is the same as Example 8-4, except that it uses SQL keywords IF MISSING or
GraphQL @generated argument on:"missing".
SQL:
CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW waiter_dv_gen_cond AS
SELECT JSON {'_id' : empno,
'name' : name,
'wage' : wage,
'tips' : tips,
'allowances' :
allowances GENERATED ON WRITE IF MISSING USING PATH '1000'}
FROM waiter e WITH (UPDATE, INSERT, DELETE);GraphQL:
GraphQL directive @generated uses
"missing" here as the value of argument on, to
generate field allowances only when it is absent from the incoming
document.
CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW waiter_dv_gen_cond AS
waiter @insert @delete @update
{_id : empno,
name : name,
wage : wage,
tips : tips,
allowances :
allowances @generated (path : "1000", on : "missing")};INSERT INTO waiter_dv_gen_cond
VALUES ('{"_id":18, "name":"Jane Doe", "wage":1000, "tips":100}');
INSERT INTO waiter_dv_gen_cond
VALUES ('{"_id":222, "name":"Mark Lane", "wage":2000, "tips":200,
"allowances":200}');
SELECT data FROM waiter_dv_gen_cond;Result:
DATA
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
{"_id":18,"_metadata":{"etag":"DED76F8AF14A75F4DCEA458E742AE526","asof":"0000000000000000"},"name":"Jane Doe","wage":1000,"tips":100,"allowances":1000}
{"_id":222,"_metadata":{"etag":"4FE3C4C41EC17A1C9CFCDEF12265D1AC","asof":"0000000000000000"},"name":"Mark Lane","wage":2000,"tips":200,"allowances":200}
Example 8-9 Inline Write Augmentation: Conditional (Default), Derived Value
This example provides field shiftPrio with a default
value derived from document field shiftPref (that array's first
element). That is, the field is generated only if it is missing from an incoming
document. This example is the same as Example 8-7, except that it uses SQL keywords IF MISSING or
GraphQL @generated argument on:"missing".
SQL:
CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW waiter_dv_gen_derived AS
SELECT JSON {'_id' : empno,
'name' : name,
'wage' : wage,
'tips' : tips,
'shiftPref' : shift_preference,
'shiftPrio' :
shift_prio GENERATED ON WRITE IF MISSING USING
PATH '$.shiftPref[0]'}
FROM waiter e WITH (UPDATE, INSERT, DELETE);GraphQL:
CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW waiter_dv_gen_derived AS
waiter @insert @delete @update
{_id : empno,
name : name,
wage : wage,
tips : tips,
shiftPref : shift_preference,
shiftPrio :
shift_prio @generated (path : "$.shiftPref[0]", on : "missing")};INSERT INTO waiter_dv_gen_derived
VALUES ('{"_id":66, "name":"Jane Doe", "wage":1000, "tips":100,
"shiftPref":[ "A","B","C" ]}');
INSERT INTO waiter_dv_gen_derived
VALUES ('{"_id":72, "name":"Jane Doe", "wage":1000, "tips":100,
"shiftPref":[ "A","B","C" ], "shiftPrio":"B"}');
SELECT data FROM waiter_dv_gen_derived;
Result:
DATA
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
{"_id":66,"_metadata":{"etag":"8E02514B3D9F05C96897EF393145FDF5","asof":"0000000000000000"},"name":"Jane Doe","wage":1000,"tips":100,"shiftPref":["A","B","C"],"shiftPrio":"A"}
{"_id":72,"_metadata":{"etag":"EF8A26016AECCFA9EE9541E0BFFF684D","asof":"0000000000000000"},"name":"Jane Doe","wage":1000,"tips":100,"shiftPref":["A","B","C"],"shiftPrio":"B"}
Example 8-10 Inline Augmentation: Read and Write Combined
This example combines inline read and write augmentation in the same duality view (for different fields).
The documents supported by duality view waiter_ccard_dv
include field creditCard, whose value is a masked credit-card
number, such as "****-****-****-5678", with only the last four
digits revealed. And only those last four digits are stored in the database (in
column cc_last4 of underlying table waiter).
Write (Insert/Update): When a document is written to view
waiter_ccard_dv, the last four digits of its
creditCard field are used to populate duality-view field
ccLast4, which is mapped to column cc_last4 of
table waiter. This derived field (and column) value is provided by
inline write augmentation. Field ccLast4 itself is
hidden, meaning that it never appears in any document.
Read (Select): When a document is read from the duality
view, document field creditCard is generated with a value of
"****-****-****-" followed by the last four digits of the card
number, which are taken from hidden field ccLast4.
This derived field value is provided by inline read augmentation
using the SQL expression '****-****-****-' || cc_last4, which
concatenates the literal string "****-****-****-" with the
last-four-digits string value from column cc_last4.
SQL:
CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW waiter_ccard_dv AS
SELECT JSON {'_id' : empno,
'name' : name,
'wage' : wage,
'ccLast4' :
cc_last4 GENERATED ON WRITE USING
PATH '$.creditCard.substr(15)' HIDDEN,
'creditCard' :
GENERATED ON READ USING '****-****-****-' || cc_last4}
FROM waiter WITH (INSERT, UPDATE, DELETE);CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW waiter_ccard_dv AS
waiter @insert @delete @update
{_id : empno,
name : name,
wage : wage,
ccLast4 :
cc_last4 @generated(path: "$.creditCard.substr(15)", on: "write") @hidden,
creditCard @generated(sql : "'****-****-****-' || cc_last4")};INSERT INTO waiter_ccard_dv
VALUES ('{"_id":99, "name":"Jane Doe", "wage":1000,
"creditCard":"4111-1111-1111-5678"}');
select data from waiter_ccard_dv;
Result:
DATA
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
{"_id":99,"_metadata":{"etag":"E4AC3A0D3F53C41928C53B124BC08989","asof":"0000000000000000"},"name":"Jane Doe","wage":1000,"creditCard":"****-****-****-5678"}
cc_last4:SELECT empno, name, cc_last4 from waiter; EMPNO NAME CC_LAST4
---------- ---------- ----------
99 Jane Doe 5678