Review the following requirements and restrictions for table annotations and DML operations on JSON duality views.
The root object and sub-objects of a document must have the
INSERT
annotation. Any referenced
sub-objects must exist.
If a sub-object is updated as part of an insert operation,
this sub-object must have the UPDATE
annotation. Exception: If a given
sub-object already exists and is referenced in the object
being inserted or updated, no annotation check is performed.
Attempting to insert NULL
or an empty
object is rejected with an error.
Insert operations must not result in any constraint
violations. This includes NULL
, primary
key, unique Key, check, and foreign key constraints.
Values for primary key columns must be specified. It is
possible in some cases to deduce a primary key column value
from a JOIN
. If primary key
values are not supplied, and cannot be deduced from a join
condition, the insert is rejected with an error.
Values for columns other than primary keys may be omitted. In
such cases, either the column's default value, if
applicable, or NULL
is stored in those
columns.
When values columns used in the join condition of objects and sub-objects are specified, the values of the columns used in the join condition must be same.
If the value for a column is not specified and it is part of a
sub-object's join condition, the value from the other
operand is used in its place. In the
INSERT
statement shown in this
example, the value of column t2.f3
is not
specified. t2.f3
is used in the join
condition for ChildNode
, specifying the
value as t1.f1
. In this case,
t2.f3
is copied from
t1.f1
.
CREATE TABLE t1 (f1 INT PRIMARY KEY, f2 INT);
CREATE TABLE t2 (f3 INT PRIMARY KEY REFERENCES t1(f1), f4 INT);
INSERT INTO t1 VALUES (1, 2);
INSERT INTO t2 VALUES (1, 200);
CREATE OR REPLACE JSON DUALITY VIEW dv1
AS
SELECT JSON_DUALITY_OBJECT(
WITH(INSERT, UPDATE, DELETE)
"_id" : f3,
"f4" : f4,
"ChildNode" , (SELECT JSON_DUALITY_OBJECT
(WITH(INSERT, UPDATE)
"f1" : f1,
"f2" : f2
)
FROM t1 WHERE t1.f1 = t2.f3)
) FROM t2;
INSERT INTO dv1 VALUES('{ "f4" : 400, "ChildNode" : { "f1" : 3, "f2" : 4 } }');
Since, according to the view definition for
dv1
, the columns used in the join condition
should match, the value for t1.f1
is copied
from t2.f3
. If no value is specified for
either column used in the join condition, the insert operation
is rejected with an error.
Similarly, if a column used in a join condition is not projected in the JSON duality view, the value for the column which is not projected is copied from other column used in the join condition.
In some cases, for an object, not specifying a complete sub-object is allowed. This is the case if either of the following conditions is true:
Rows matching the join condition already exist in the sub-object's table
Skipping the insertion of this sub-object does not violate any table constraints.
When the root object being inserted references only existing sub-objects, then only the root object is inserted.
When the root object being inserted references only some of all existing sub-objects, then only the root object is inserted. Sub-objects which are not specified are not deleted.
When the root object being inserted references existing sub-objects and modifies some columns not part of the table's primary key, the root object is inserted, and any sub-objects are updated.
If an object or sub-object is defined on the same table at any level of the JSON duality view's definition, values for the columns must be the same; if they are not, the operation is rejected with an error.
Inserts of multiple objects are not allowed on JSON duality views.
The following types of INSERT
statements
are not allowed on JSON duality views:
Statements using
HIGH_PRIORITY
orDELAYED
INSERT ... ON DUPLICATE KEY UPDATE
statementINSERT ... SELECT
statement
Update operations on the root object and its sub-objects
require the UPDATE
annotation, and are
rejected with an error without it. Referenced sub-objects must
exist.
If a sub-object is inserted as part of an update operation,
then the object must have the INSERT
annotation. Otherwise, the operation is rejected with an error
with the following exceptions:
If a sub-object already exists and is referenced in the object being updated, or if it would be replaced with another existing sub-object in the table, no check for annotations is performed.
If a sub-object is modified and the
UPDATE
annotation is not specified, only the existence of the sub-object is checked. An error is not reported for a missing annotation.
If a sub-object is a descendant of an element that is deleted,
then the sub-object must have a DELETE
annotation.
Updating JSON objects to an empty object or
NULL
is not allowed. Updates of primary key
column values of the root object and sub-objects are not
allowed.
Any update operation resulting in a constraint violation is
rejected with an error. Such constraints include
NULL
, primary key, unique key, check, and
foreign key constraints.
For update operations, all projected column values must be specified. Any missing sub-objects or elements in sub-objects are deleted.
If an object and its sub-object columns are not modified, base tables are updated.
If an object is modified but sub-object columns are not modified, then only the object's base table is updated.
If both object and sub-object columns are modified, then the base tables for both objects is updated.
If a new sub-object is inserted by the update, then a new row is inserted in the sub-object's table.
If an existing sub-object is missing (or deleted), the row for this sub-object is deleted.
If the deletion of an object caused by an update results in a table constraint violation, the update is rejected with an error.
Replacement of a sub-object with an existing sub-object in the base table is supported.
If multiple sub-objects are projected from the same table, the same value must be specified for all such sub-objects.
The etag
supplied for an update operation
must match the etag
generated for the same
object.
If a root object must be deleted, then an object of a document
must have a DELETE
annotation.
A singleton sub-object must not be deleted.
Nested sub-objects must not be deleted if the
DELETE
annotation is not
specified.
If sub-object has a DELETE
annotation, then all elements of the nested sub-object must be
deleted.
A delete operation is rejected with an error if referential constraint fails.
Singleton sub-objects are not deleted.