MySQL :: MySQL 9.7 Reference Manual :: 27.7.2.3 Requirements for DML Operations and Table Annotations

Requirements and Restrictions for Insert Annotations

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 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 or DELAYED
INSERT ... ON DUPLICATE KEY UPDATE statement
INSERT ... SELECT statement

Requirements and Restrictions for Update Annotations

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.

Requirements and Restrictions for Delete Annotations

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.

Requirements and Restrictions for Primary Key Columns

During insert operations, it is possible to use generated values for primary key columns and other base table columns that have the AUTO_INCREMENT attribute. When the base table column is part of join condition, keep the following in mind.

Singleton child: Consider the following criteria for JSON documents omitting join condition fields.
- The JSON document can omit the join condition fields if the child join condition column has the AUTO_INCREMENT attribute. In this scenario, it is also possible to specify the value as 0 or NULL, provided that the field maps to a column with the AUTO_INCREMENT attribute and the sql_mode NO_AUTO_VALUE_ON_ZERO is off.
- The JSON document can omit the other side of the join condition only when it is possible to infer it through the join condition. This is only possible if the insertion into the column with the AUTO_INCREMENT attribute has already occurred.
In the following example, only the child join condition column can use the generated values of AUTO_INCREMENT. The parent join condition column cannot use generated values.
```
CREATE TABLE singleton(sc1 INT PRIMARY KEY AUTO_INCREMENT, sc2 INT);
CREATE TABLE parent(pc1 INT PRIMARY KEY, pc2 INT REFERENCES singleton(sc1));

CREATE JSON DUALITY VIEW jdv_singleton AS
SELECT JSON_DUALITY_OBJECT(WITH (INSERT, UPDATE, DELETE)
"_id" : pc1,
"_pc2" : pc2,
"_singleton" : (SELECT JSON_DUALITY_OBJECT(WITH (INSERT, UPDATE)
"_sc1" : sc1,
"_sc2" : sc2
)
FROM singleton
WHERE singleton.sc1 = parent.pc2
)
)
FROM parent;
INSERT INTO jdv_singleton VALUES ('{ "_id":1, "_singleton": {"_sc2": 42}}');
```
Nested child: Consider the following criteria for JSON documents omitting join condition fields.
- The JSON document can omit the join condition fields if the child join condition column has the AUTO_INCREMENT attribute. In this scenario, it is also possible to specify the value as 0 or NULL, provided that the field maps to a column with the AUTO_INCREMENT attribute and the sql_mode NO_AUTO_VALUE_ON_ZERO is off.
- The JSON document can omit the other side of the join condition only when it is possible to infer it through the join condition. This is only possible if the insertion into the column with the AUTO_INCREMENT attribute has already occurred.
In the following example, only the parent join condition column can use the generated values of AUTO_INCREMENT. The child join condition column cannot use generated values.
```
CREATE TABLE singleton(sc1 INT PRIMARY KEY AUTO_INCREMENT, sc2 INT);
CREATE TABLE parent(pc1 INT PRIMARY KEY, pc2 INT REFERENCES singleton(sc1));

CREATE JSON DUALITY VIEW jdv_singleton AS
SELECT JSON_DUALITY_OBJECT(WITH (INSERT, UPDATE, DELETE)
"_id" : pc1,
"_pc2" : pc2,
"_singleton" : (SELECT JSON_DUALITY_OBJECT(WITH (INSERT, UPDATE)
"_sc1" : sc1,
"_sc2" : sc2
)
FROM singleton
WHERE singleton.sc1 = parent.pc2
)
)
FROM parent;
INSERT INTO jdv_singleton VALUES ('{ "_id":1, "_singleton": {"_sc2": 42}}');
```
If the value for one side of a join condition is provided explicitly, this value must be propagated to the other side of the join condition, even if the other side has the AUTO_INCREMENT attribute. This is because inference from an explicitly provided value through the join condition takes precedence over automatically generated values. This applies even if automatic generation is explicitly requested using 0 or NULL.

A field of a JSON document that maps to a primary key column that has the AUTO_INCREMENT attribute cannot have a mix of both explicit and generated values. The JSON document must either provide all automatically generated values, or none of them.

In the following example, since all explicit values were provided, only explicit values are used.

INSERT INTO customers_orders_dv VALUES (
'{  "customer_name": "Alice",
    "_id": 1,
    "orders": [
      {"order_id": 100, "product": "Laptop", "amount": 1299.99},
      {"order_id": 101, "product": "Mouse", "amount": 19.99},
      {"order_id": 100, "product": "Laptop", "amount": 1299.99}
    ]
}');

In the following example, since not all explicit values were provided, only generated values are used.

INSERT INTO customers_orders_dv VALUES (
'{  "customer_name": "Alice",
    "_id": 1,
    "orders": [
      {"order_id": 0, "product": "Laptop", "amount": 1299.99},
      {"product": "Mouse", "amount": 19.99},
      {"order_id": 0, "product": "Laptop", "amount": 1299.99}
    ]
}');

A generated value for a column with the AUTO_INCREMENT attribute can only be inferred through the join condition if insertion into this column happens before insertion into the other side of the join condition.