Motivation

The Mysqlx.CRUD.Update doesn't support updating an existing full document, only parts of it.

The intention was that any update (special case full document) could also overwrite the _id field and lead implicit change of identity (and possible reshuffeling of data in sharding).

Current Behaviour

The 'Mysqlx.CRUD.UpdateOperation' supports ITEM_REMOVE, ITEM_SET, ITEM_REPLACE, ARRAY_INSERT, ARRAY_APPEND commands:

message UpdateOperation {
  enum UpdateType {
    ...
    ITEM_SET = 3;       // sets the new value on the identified path
    ITEM_REPLACE = 4;   // replaces a value if the path exists
    ITEM_MERGE = 5;     // source and value must be documents
    ...
  }
  required Mysqlx.Expr.ColumnIdentifier source = 1;
  required UpdateType operation = 2;
  optional Mysqlx.Expr.Expr value = 3;
  ...
}

The operations require:

• non-empty document-path
• value

Expected Behaviour

Allow specifying a empty document-path to operate on the whole document.

Any operation done on existing document must preserved _id field.

Functional requirements

1. The Update commands (ITEM_REMOVE, ITEM_SET, ITEM_REPLACE, ARRAY_INSERT, ARRAY_APPEND) must continue working as is for any non-empty document paths
2. The Update commands (ITEM_REMOVE, ITEM_SET, ITEM_REPLACE, ARRAY_INSERT, ARRAY_APPEND) must allow an empty document path to be specified, which would replace the full document with the provided one
3. If resulting value is a non-document, an error must be generated
4. If resulting document contains an id value different from the original, it must be ignored (original id must be restored).

This is an incremental change on the existing operations of the Mysqlx.CRUD.Update command.

The command only applies to JSON, in both Collections and Tables (on JSON columns).

Previously, ITEM_ITEM_REMOVE, ITEM_SET, ITEM_REPLACE, ARRAY_INSERT, ARRAY_APPEND commands were forbidden on the top level document to avoid changes to the _id field. This WL changes that behaviour so that in Collections, this case is handled by explicitly overwriting the _id field of the document with the value of the original _id column, thus preserving the value of the original _id field. This precaution is not needed for tables.

ITEM_MERGE operations on user provided value, not on document_path. Any attempt of setting the document_path must return an error.

Current behaviour Mysqlx.Crud.Update

Input parameters:

• document_path1...
• value1...
• operations

Algorithm:

if (is_path_empty(document_path1...))
  return ERROR
else
  UPDATE TABLE coll SET doc=operations(doc, document_path1..., value1...);

New behaviour of Mysqlx.Crud.Update

Input parameters:

• operations
• document_path1...
• value1...

Algorithm:

if (has_merge_non_empty_path(operations, document_path1...))
  return ERROR
UPDATE TABLE tbl SET doc=JSON_SET(
  operations(doc, document_path1..., value1...),
  '$._id', doc->"$._id");

Error and Warnings

Example

DevAPI example of two operations made on 'coll' collection:

coll.modify("true")
  .set(document_path1, value1)
  .unset(document_path2).execute()

Generated SQL:

UPDATE TABLE coll SET doc=JSON_SET(
    JSON_REMOVE(
        JSON_SET(doc, document_path1, value1),
        document_path2),
    '$._id',  doc->'$._id'));