MySQL :: X DevAPI User Guide :: 4.3.3 Collection.modify()

X DevAPI User Guide / ... / Collection.modify()

4.3.3 Collection.modify()

The modify(SearchConditionStr) function is for modifying documents in a collection, similar to an UPDATE statement for an SQL database. It takes a search condition string (SearchConditionStr) as a parameter to specify the documents that are to be modified—a detailed discussion on the SearchConditionStr can be found in Section 4.3.2, “Collection.find()”.

If one or more documents are matched by the search condition string, they are modified by any of these methods that are chained after the modify() method. They can be chained one after another and for multiple times:

Notes

The _id of a document cannot be modified or removed by the methods below.
For any methods below that take a DocPath expression as one of its arguments, the following rules apply:
- Within the DocPath expression, any field names containing a space or a special character must be quoted; for example, set("name.'last name'", "Smith"), unset("name.'last%name'")
- The DocPath expression cannot contain a wildcard token (either * or **).
- The DocPath expression cannot be null or empty.

set("DocPath", ExprOrLiteral): Set the elements matched by the Document Path (DocPath) expression with the value represented by the Expression or Literal (ExprOrLiteral) expression.

The DocPath expression is a JSON path expression identifying one or more JSON elements in the documents found by the modify() function. See discussions on the JSON path in Section 4.3.2, “Collection.find()”. If the element specified by DocPath does not exist, it is added to the document as a new element.

ExprOrLiteral specifies the value to be set for the element represented by DocPath. It can be any of the following:
- A literal value. For example, 10 or "John".
- Any X DevAPI Expression, wrapped in the expr() function (or mysql.expr() for MySQL Shell and some Connectors), so that it is not taken as a literal value. Here are some examples, which do not exhaust the possibilities of using mysql.expr(Expression) for ExprOrLiteral:
  - Another DocPath selecting a value from the document that is being modified (for example, set("favorNums[0]", mysqlx.expr("favorNums[1]")), or set("name", mysqlx.expr("$.'last name'")).
  - A functional expression that involves one or more Expressions (for example, set("favorNums[0]", mysqlx.expr("abs(favorNums[1])")).
  - One or more Expressions connected by operators (for example, set("favorNums[0]", mysqlx.expr("favorNums[1]+favorNums[2]+favorNums[3]+3")) , or set("SameValueOrNot", mysqlx.expr("favorNums[1] = favorNums[2]")).
  - A JSON document (for example, set("Greeting", mysqlx.expr("{'season':'winter', 'phrase': 'Happy Holiday'}"))
Note

set("$", mysqlx.expr("json_document") replaces all documents matched by modify() with the supplied json_document, except for the original _id field, which is inalterable once set at document creation.
unset("DocPath[, DocPath] ..."): Delete one or more fields or array elements represented by a list of one or more DocPath (for example, unset("name"), unset("name.'last name'", name.'first name'"), or unset("favorNums[0]")).

An error is returned if no DocPath is supplied, or if DocPath is $ (use remove() instead if you want to delete a whole document).
Warning

Notice that when multiple array elements are unset or deleted, they are being removed one after another, and the same array index in a statement might, therefore, refer to different elements for each unset action. Take that into consideration when removing array elements. For example, for the document:
```
mysql-js> myColl.find("name = 'Ann'");
{
    "_id": "00006239f74a0000000000000004",
    "name": "Ann",
    "favorNums": [
        1,
        2,
        3,
        4,
        5
    ]
}
```
The following statement does not remove the first and second elements of the array as one might expect:
```
mysql-js> myColl.modify("name = 'Ann'").unset("favorNums[0]","favorNums[1]");
Query OK, 1 item affected (0.0038 sec)

mysql-js> myColl.find("name = 'Ann'").fields("favorNums");
{
    "favorNums": [
        2,
        4,
        5
    ]
}
1 document in set (0.0007 sec)
```
Instead, it removed the first and third elements of the array. To delete the first two elements, you can do the following:
```
mysql-js> myColl.modify("name = 'Ann'").unset("favorNums[0]","favorNums[0]");
Query OK, 1 item affected (0.0108 sec)

Rows matched: 1  Changed: 1  Warnings: 0
mysql-js > myColl.find("name = 'Ann'").fields("favorNums");
{
    "favorNums": [
        3,
        4,
        5
    ]
}
1 document in set (0.0005 sec)
```

patch(Document): Performs a merge patch for any documents matched by modify() and the JSON Document supplied as its parameter. The operation follows the RFC 7396 specification for JSON merge patch created by the Internet Engineering Task Force (IETF). The following table explains the action on a field, which depends on the field statuses in the two documents (notice that this is a recursive operation):

Table 4.1 JSON Patch Merge of Document Fields

Field Status in the Original Document	Field Status in Patch Document	Action to be Taken on the Field in the Original Document
Any value	Value is Null	Remove field
Value B	Value A (not Null)	If either Value A or B is a scalar, replace Value B with Value A If both Value A and B are JSON objects, they are merged using the same rules described in this table (that is, the merge is applied recursively for JSON documents).
Field does not exist	Value A (not Null)	Add field with Value A
Value C	Field does not exist	No change to field

Here is a simple example of a merge using patch():

mysql-js> myColl.find("name = 'John Doe'");
{
    "DOB": "1970-01-01",
    "_id": "0000626028c30000000000000002",
    "name": "John Doe",
    "Phone": 1234567,
    "Standing": "Good",
    "favorNums": {
       "a": 1,
       "b":2 
    }
}
1 document in set (0.0009 sec)
 
mysql-js> myColl.modify("name = 'John Doe'")
    .patch({ name: "Jane Doe", DOB: null, Phone: 9876543, favorNums: { a: 3, b:4 } });
Query OK, 1 item affected (0.0413 sec)

Rows matched: 1  Changed: 1  Warnings: 0
 
mysql-js> myColl.find("name = 'Jane Doe'");
{
    "_id": "0000626028c30000000000000002",
    "name": "Jane Doe",
    "Phone": 9876543,
    "Standing": "Good",
    "favorNums": {
        "a": 3,
        "b": 4
    }
}
1 document in set (0.0008 sec)

arrayInsert(DocPath, ExprOrLiteral): Insert an ExprOrLiteral (see explanations above) into an array at the location identified by DocPath, shifting any following values in the array to the right. For example: arrayInsert("favorNums[1]", 7), arrayInsert("favorNums[1]", {even: 2, odd: 3, irrational: 'pi'}). The following rules apply:
- If DocPath does not identify an array element, an error is returned.
- If DocPath identifies an array position past the end of an array, the value is inserted at the end of the array.

arrayAppend(DocPath, ExprOrLiteral): Append a value represented by ExprOrLiteral to the end of an array identified by DocPath. For example, arrayAppend("favorNums", 555).

Notice that if DocPath points to a scalar or a document value, that value is autowrapped within an array and the value represented by ExprOrLiteral is added to that array. For example:

mysql-js> myColl.find("name='Jane Doe'");
{
    "_id": "000062b0faf90000000000000001",
    "name": "Jane Doe",
    "favorNum": 2
}
1 document in set (0.0011 sec)

mysql-js> myColl.modify("name='Jane Doe'").arrayAppend("favorNum",3);
Query OK, 1 item affected (0.0094 sec)

Rows matched: 1  Changed: 1  Warnings: 0
mysql-js> myColl.find("name='Jane Doe'");
{
    "_id": "000062b0faf90000000000000001",
    "name": "Jane Doe",
    "favorNum": [
        2,
        3
    ]
}
1 document in set (0.0006 sec)

The following methods can be chained to the modification methods described above to configure the modification:

sort(sortCriteriaList): Sort the order in which documents are to be modified according to sortCriteriaList, which is either a comma-separated list or an array of sortCriteria. Each sortCriteria consists of a component name and a search order (asc for ascending, or desc for descending). For example:
- sort('name asc', 'age desc')
- sort(['name asc', 'age desc'])
The method is used in combination with the limit() method to determine which of the documents matched by modify(SearchConditionStr) are to be modified.
limit(int): Limits the number of documents to be modified to int. When chained after sort(), only the first int of documents in the sorted list are modified.

This is an example of using sort().limit() to limit modifications to the documents:

mysql-js> myColl.find("name like '%Doe'");
{
    "_id": "000062b0faf90000000000000001",
    "name": "Jane Doe",
    "favorNum": [
        2,
        3
    ]
}
{
    "_id": "000062b372f80000000000000001",
    "name": "Bob Doe",
    "favorNum": [
        1,
        2
    ]
}
{
    "_id": "000062b372f80000000000000002",
    "name": "Mark Doe",
    "favorNum": [
        7,
        8
    ]
}
{
    "_id": "000062b372f80000000000000003",
    "name": "John Doe",
    "favorNum": [
        0,
        4
    ]
}
mysql-js> myColl.modify("name like '%Doe'").unset("favorNum").sort("name asc").limit(2);
Query OK, 2 items affected (0.0082 sec)

Rows matched: 2  Changed: 2  Warnings: 0
mysql-js> myColl.find("name like '%Doe'").sort('name asc');
{
    "_id": "000062b372f80000000000000001",
    "name": "Bob Doe"
}
{
    "_id": "000062b0faf90000000000000001",
    "name": "Jane Doe"
}
{
    "_id": "000062b372f80000000000000003",
    "name": "John Doe",
    "favorNum": [
        0,
        4
    ]
}
{
    "_id": "000062b372f80000000000000002",
    "name": "Mark Doe",
    "favorNum": [
        7,
        8
    ]
}
4 documents in set (0.0068 sec)

Parameter binding using bind() is also supported. The execute() function triggers the actual execution of the modify() operation. The following example illustrates the use of modify():

MySQL Shell JavaScript Code

// Use the collection 'my_collection'
var myColl = db.getCollection('my_collection');

// Add a new document to the collection 
myColl.add({ name:"John Doe", DOB:"1970-01-01", Phone:1234567, Standing: "Good" }).execute();

// Patch the added document, adding, removing, and changing some fields 
myColl.modify("name = 'John Doe'").patch({ name: "Jane Doe", DOB: null, Phone: 9876543, favorNums: [1,2,3,4,5] }).execute();

//Modify fields with different methods
myColl.modify("name like :param").set("Standing", "Bad").bind("param", "J%Doe").execute();
myColl.modify("name like :param").unset("Phone").bind("param", "J%Doe").execute();
myColl.modify("name like :param").arrayInsert("favorNums[1]", 7).bind("param", "J%Doe").execute();
myColl.modify("name like :param").arrayAppend("favorNums", 99).bind("param", "J%Doe").execute();
myColl.modify("name like :param").unset("favorNums[2]").bind("param", "J%Doe").execute();

var doc = myColl.find('name like :param').limit(1).bind('param', 'J%Doe').execute();

print(doc.fetchOne());
/* The output looks like: 
{
    "Standing": "Bad", 
    "_id": "0000626718c10000000000000002", 
    "favorNums": [
        1, 
        7, 
        3, 
        4, 
        5, 
        99
    ], 
    "name": "Jane Doe"
} */

MySQL Shell Python Code

# Use the collection 'my_collection'
myColl = db.get_collection('my_collection')
# Add a new document to the collection 
myColl.add({ "name":"John Doe", "DOB":"1970-01-01", "Phone":1234567, "Standing": "Good" }).execute()

# Patch the added document, adding, removing, and changing some fields 
myColl.modify("name = 'John Doe'").patch({ "name": "Jane Doe", "DOB": None, "Phone": 9876543, "favorNums": [1,2,3,4,5] }).execute()

# Modify fields with different methods
myColl.modify("name like :param").set("Standing", "Bad").bind("param", "J%Doe").execute()
myColl.modify("name like :param").unset("Phone").bind("param", "J%Doe").execute()
myColl.modify("name like :param").array_insert("favorNums[1]", 7).bind("param", "J%Doe").execute()
myColl.modify("name like :param").array_append("favorNums", 99).bind("param", "J%Doe").execute()
myColl.modify("name like :param").unset("favorNums[2]").bind("param", "J%Doe").execute()

doc = myColl.find('name like :param').limit(1).bind('param', 'J%Doe').execute()

print(doc.fetch_one())

# The output looks like:
# {"Standing": "Bad", "_id": "0000626718c10000000000000005", "favorNums": [1, 7, 3, 4, 5, 99], "name": "Jane Doe"}

Node.js JavaScript Code

const mysqlx = require('@mysql/xdevapi');

mysqlx.getSession('mysqlx://localhost:33060')
    .then(session=> {
        const collection = session.getSchema('mySchema').getCollection('myCollection');
        // Add a new document to the collection 
        return collection.add({ name:"John Doe", DOB:"1970-01-01", Phone:1234567, Standing: "Good" })
          .execute()
          // Patch the added document, adding, removing, and changing some fields 
          .then(()=> {
            return collection.modify("name = 'John Doe'")
              .patch({ name: "Jane Doe", DOB: null, Phone: 9876543, favorNums: [1,2,3,4,5] })
              .execute();
          })
          // Modify fields with different methods
          .then(()=> {
            return collection.modify("name like :param")
              .set("Standing", "Bad")
              .bind("param", "J%Doe")
              .execute();
          })
          .then(()=> {
            return collection.modify("name like :param")
              .unset("Phone").bind("param", "J%Doe")
              .bind("param", "J%Doe")
              .execute();
          })
          .then(()=> {
            return collection.modify("name like :param")
              .arrayInsert("favorNums[1]", 7)
              .bind("param", "J%Doe")
              .execute();
          })
          .then(()=> {
            return collection.modify("name like :param")
              .arrayAppend("favorNums", 99)
              .bind("param", "J%Doe")
              .execute();
          })
          .then(()=> {
            return collection.modify("name like :param")
              .unset("favorNums[2]")
              .bind("param", "J%Doe")
              .execute();
          })
          .then(()=> {
            return collection.find('name like :param')
              .limit(1).bind('param', 'J%Doe')
              .execute();
          })
          .then(res => {
            console.log(res.fetchOne()); 
                     /* The output looks like: 
                       {
                        _id: '00006272fb890000000000000004',
                        name: 'Jane Doe',
                        Standing: 'Bad',
                        favorNums: [ 1, 7, 3, 4, 5, 99 ]
                       } */
           });
    });

C# Code

// Use the collection "my_collection"
var myColl = myDb.GetCollection("my_collection");

// Insert a document
myColl.Add(new { name = "John Doe", DOB = "1970-01-01", Phone = 1234567, Standing = "Good" }).Execute();

//Patch the added document, adding, removing, and changing some fields 
myColl.Modify("name = 'John Doe'")
  .Patch(new { name = "Jane Doe", DOB = (string)null, Phone = 9876543, favorNums = new[] { 1, 2, 3, 4, 5 } })
  .Execute();

//Modify fields with different methods
myColl.Modify("name like :param").Set("Standing", "Bad").Bind("param", "J%Doe").Execute();
myColl.Modify("name like :param").Unset("Phone").Bind("param", "J%Doe").Execute();
myColl.Modify("name like :param").ArrayInsert("favorNums[1]", 7).Bind("param", "J%Doe").Execute();
myColl.Modify("name like :param").ArrayAppend("favorNums", 99).Bind("param", "J%Doe").Execute();
myColl.Modify("name like :param").Unset("favorNums[2]").Bind("param", "J%Doe").Execute();

var docs = myColl.Find("name like :param").Limit(1).Bind("param", "J%Doe").Execute();
Console.WriteLine(docs.FetchOne());

/* The output looks like: 
   {
                  "_id": "00006274151a0000000000000004",
                  "name": "Jane Doe",
                  "Standing": "Bad",
                  "favorNums": [
                    1,
                    7,
                    3,
                    4,
                    5,
                    99
                  ]
                }
*/

Python Code

# Use the collection 'my_collection'
my_coll = my_schema.get_collection('my_collection')

# Add a new document to the collection 
my_coll.add({ "name":"John Doe", "DOB":"1970-01-01", "Phone":1234567, "Standing": "Good" }).execute()

# Patch the added document, adding, removing, and changing some fields 
my_coll.modify("name = 'John Doe'").patch({"name": "Jane Doe", "DOB": None, "Phone": 9876543, "favorNums": [1,2,3,4,5] }).execute()

# Modify fields with different methods
my_coll.modify("name like :param").set("Standing", "Bad").bind("param", "J%Doe").execute()
my_coll.modify("name like :param").unset("Phone").bind("param", "J%Doe").execute()
my_coll.modify("name like :param").array_insert("favorNums[1]", 7).bind("param", "J%Doe").execute()
my_coll.modify("name like :param").array_append("favorNums", 99).bind("param", "J%Doe").execute()
my_coll.modify("name like :param").unset("favorNums[2]").bind("param", "J%Doe").execute()

docs = my_coll.find('name like :param').limit(1).bind('param', 'J%Doe').execute()

print(docs.fetch_one())

# The output looks like:
#{"_id": "0000627ac9ac0000000000000007", "name": "Jane Doe", "Standing": "Bad", "favorNums": [1, 7, 3, 4, 5, 99]}

Java Code

// Use the collection 'my_collection'
Collection myColl = db.getCollection("my_collection");



// Add a new document to the collection 
myColl.add("{ \"name\":\"John Doe\", \"DOB\" : \"1970-01-01\", \"Phone\" : 1234567, \"Standing\" : \"Good\" }").execute();
   
// Patch the added document, adding, removing, and changing some fields 
myColl.modify("name = 'John Doe'").patch("{ \"name\": \"Jane Doe\", \"DOB\" : null, \"Phone\" : 9876543, \"favorNums\" : [1,2,3,4,5] }").execute();

//Modify fields with different methods
myColl.modify("name like :param").set("Standing", "Bad").bind("param", "J%Doe").execute();
myColl.modify("name like :param").unset("Phone").bind("param", "J%Doe").execute();
myColl.modify("name like :param").arrayInsert("favorNums[1]", 7).bind("param", "J%Doe").execute();
myColl.modify("name like :param").arrayAppend("favorNums", 99).bind("param", "J%Doe").execute();
myColl.modify("name like :param").unset("favorNums[2]").bind("param", "J%Doe").execute();

DocResult docs = myColl.find("name like : param").limit(1).bind("param", "J%Doe").execute();

while (docs.hasNext()) {
    DbDoc myDoc = docs.next();
    System.out.println(myDoc);
} 
    
//The output looks like:
//{"Standing":"Bad","_id":"0000627eadcb0000000000000001","favorNums":[1,7,3,4,5,99],"name":"Jane Doe"}

C++ Code

Collection myColl = db.getCollection("my_collection");
          
// Add a new document to the collection 
myColl.add(R"({ "name":"John Doe", "DOB" : "1970-01-01", "Phone" : 1234567, "Standing" : "Good" })").execute();
   
// Patch the added document, adding, removing, and changing some fields 
myColl
  .modify("name = 'John Doe'")
  .patch(R"({
   "name": "Jane Doe", "DOB" : null, "Phone" : 9876543, "favorNums" : [1,2,3,4,5] 
   })")
  .execute();

//Modify fields with different methods
myColl.modify("name like :param").set("Standing", "Bad").bind("param", "J%Doe").execute();
myColl.modify("name like :param").unset("Phone").bind("param", "J%Doe").execute();
myColl.modify("name like :param").arrayInsert("favorNums[1]", 7).bind("param", "J%Doe").execute();
myColl.modify("name like :param").arrayAppend("favorNums", 99).bind("param", "J%Doe").execute();
myColl.modify("name like :param").unset("favorNums[2]").bind("param", "J%Doe").execute();

DocResult docs = myColl.find("name like :param").limit(1).bind("param", "J%Doe").execute();

DbDoc myDoc;
while ((myDoc = docs.fetchOne()))
    {
        cout << myDoc << endl;
    }
    
//The output looks like: 
// {"_id": "0000627ea0a80000000000000006", "name": "Jane Doe", "Standing": "Bad", "favorNums": [1, 7, 3, 4, 5, 99]}

See also CollectionModifyFunction for the syntax of add() in EBNF.

PREV HOME UP NEXT