In the previous blog post we explained how work with the collection CRUD operations. In this blog post we are going to explain other functions that are related to document management.

We already know how to create collections, as well as how to add, delete, update and retrieve documents from them. But, how can we add a new field to a document or documents that are in a collection?

The following code demonstrates how to do it:

var mysqlx = require('mysqlx');
mysqlx.getSession({
  host: 'host',
  port: '33060',
  dbUser: 'root',
  dbPassword: 'my pass'
}).then(function (session) {
  var schema = session.getSchema('mySchema');
  var coll = schema.getCollection('myColl');
  var query = "$.name == 'NewField'";
  var newDoc = { name: 'NewField', description: 'a new field', 
                 extra: ['hello', 'world'] };

  coll.add(newDoc).execute().then(function (added) {
    coll.modify(query)
        .set('$.newField1', 'new field 1')
        .execute().then(function (updated) {
          console.log('Document(s) updated: '
                      + updated.getAffectedItemsCount());
          coll.find(query).execute(function (doc) {
            console.log(doc);
            session.close();
          });
    })
    .catch(function (err) {
      console.log(err.message);
      console.log(err.stack);
    });
  });
}).catch(function (err) {
  console.log(err.message);
  console.log(err.stack);
});

In the previous code, first we get the schema and the collection objects (schema and coll). Then we define the query variable that contains the where clause, and newDoc with the document used in the example. Next, we call the coll object’s modify method that receives the query variable as a parameter. Chained to the modify method is the set method, which receives a pair of objects; the first one is the field name and the second one is the value for the field. If you have already read the previous blog post, you may notice that the code is the same as the one used to update the value of a field in a document, except for the line that adds the new document we want to work with. The set method is used to add new fields or to update their value. Once the execute method is finished, the number of documents updated are written to the console as well as the updated document, and finally the connection is closed.

The following code demonstrates how to add multiple fields to a document:

var mysqlx = require('mysqlx');
mysqlx.getSession({
  host: 'host',
  port: '33060',
  dbUser: 'root',
  dbPassword: 'my pass'
}).then(function (session) {
  var schema = session.getSchema('mySchema');
  var coll = schema.getCollection('myColl');
  var query = "$.name == 'NewField'";
  var newDoc = { name: 'NewField', description: 'a new field', 
                 extra: ['hello', 'world'] };

  coll.add(newDoc).execute().then(function (added) {
    coll.modify(query)
        .set('$.newField1', 'new field 1')
        .set('$.newField2', 'new field 2')
        .execute().then(function (updated) {
          console.log('Document(s) updated: '
                      + updated.getAffectedItemsCount());
            coll.find(query).execute(function (doc) {
              console.log(doc);
              session.close();
            });
    })
    .catch(function (err) {
      console.log(err.message);
      console.log(err.stack);
    });
  });
}).catch(function (err) {
  console.log(err.message);
  console.log(err.stack);
});

As you can see in the previous code, we just chained the set method two times to add multiple fields to a document. You can chain the set method as many times as needed. If the query passed to the modify method finds multiple documents, all the documents found will be updated adding the new fields sent; so be careful with it.

What if we want to remove a field? We just need to use the unset method to accomplish this. In the next example we demonstrate how to do it:

var mysqlx = require('mysqlx');
mysqlx.getSession({
  host: 'host',
  port: '33060',
  dbUser: 'root',
  dbPassword: 'my pass'
}).then(function (session) {
  var schema = session.getSchema('mySchema');
  var coll = schema.getCollection('myColl');
  var query = "$.name == 'NewField'";
  var newDoc = { name: 'NewField', description: 'a new field', 
                 extra: ['hello', 'world'], field1: 'one', field2: 'two'};

  coll.add(newDoc).execute().then(function (added) {
    coll.modify(query)
        .unset('$.field1')
        .unset('$.field2')
        .execute().then(function (updated) {
          console.log('Document(s) updated: '
                      + updated.getAffectedItemsCount());
          coll.find(query).execute(function (doc) {
            console.log(doc);
            session.close();
          });
    })
    .catch(function (err) {
      console.log(err.message);
      console.log(err.stack);
    });       
  });
}).catch(function (err) {
  console.log(err.message);
  console.log(err.stack);
});

The previous code looks almost identical to the code used to add fields to a document; the difference is that we use the unset method instead of the set method. The rest of the code is the same.

In the previous examples we used an array as the value for one of the fields of the documents we added. Connector/Node.js also has methods to handle the items inside the array.

If we want to add items to an array we have several options. One such option is demonstrated in the following example:

var mysqlx = require('mysqlx');
mysqlx.getSession({
  host: 'host',
  port: '33060',
  dbUser: 'root',
  dbPassword: 'my pass'
}).then(function (session) {
  var schema = session.getSchema('mySchema');
  var coll = schema.getCollection('myColl');
  var query = "$.name == 'NewField'";
  var newDoc = { name: 'NewField', description: 'a new field', 
                 extra: ['hello', 'world'] };

  coll.add(newDoc).execute().then(function (added) {
    coll.modify(query)
        .arrayAppend('$.extra', 'this is a new item')
        .execute().then(function (updated) {
          console.log('Document(s) updated: '
                      + updated.getAffectedItemsCount());
          coll.find(query).execute(function (doc) {
            console.log(doc);
            session.close();
          });
    })
    .catch(function (err) {
      console.log(err.message);
      console.log(err.stack);
    }); 
  });
}).catch(function (err) {
  console.log(err.message);
  console.log(err.stack);
});

The code looks quite similar to the code of the previous examples; there is just a new method: appendArray. The method receives two parameters; the first one is the field that has an array as its value. The second parameter is the value for the item to be added, which will be added at the end of the array.

As well as for the set method, if you send a query to the modify method that returns multiple documents; all the documents will be updated as well.

Another way of adding items to an array field is by using the arrayInsert method:

var mysqlx = require('mysqlx');
mysqlx.getSession({
  host: 'host',
  port: '33060',
  dbUser: 'root',
  dbPassword: 'my pass'
}).then(function (session) {
  var schema = session.getSchema('mySchema');
  var coll = schema.getCollection('myColl');
  var query = "$.name == 'NewField'";
  var newDoc = { name: 'NewField', description: 'a new field', 
                 extra: ['hello', 'world'] };

  coll.add(newDoc).execute().then(function (added) {
    coll.modify(query)
        .arrayInsert('$.extra[1]', 'inserting a new item')
        .execute().then(function (updated) {
          console.log('Document(s) updated: '
                      + updated.getAffectedItemsCount());
          coll.find(query).execute(function (doc) {
            console.log(doc);
            session.close();
          });
    }).catch(function (err) {
      console.log(err.message);
      console.log(err.stack);
    });
  });
}).catch(function (err) {
  console.log(err.message);
  console.log(err.stack);
});

As you can see the previous code is almost equal to the code where we used the appendArray method. The only difference is that we now use the arrayInsert method instead. The arrayInsert method receives two parameters; the first one is the name of the field including the index of the array where you want to insert the new item, if the index is out of bounds it will be inserted at the end of the array. The second parameter is the value of the new item.

If you remember, in previous examples we used the set method to update field values and to add new fields to a document.

Well we can also use set to add new items to an array. The following code demonstrates how to do it.

var mysqlx = require('mysqlx');
mysqlx.getSession({
  host: 'host',
  port: '33060',
  dbUser: 'root',
  dbPassword: 'my pass'
}).then(function (session) {
  var schema = session.getSchema('mySchema');
  var coll = schema.getCollection('myColl');
  var query = "$.name == 'NewField'";
  var newDoc = { name: 'NewField', description: 'a new field', 
                 extra: ['hello', 'world'] };

  coll.add(newDoc).execute().then(function (added) {
    coll.modify(query)
        .set('$.extra[5]', 'new item array')
        .execute().then(function (updated) {
          console.log('Document(s) updated: '
                      + updated.getAffectedItemsCount());
           coll.find(query).execute(function (doc) {
             console.log(doc);
             session.close();
           });
     })
      .catch(function (err) {
        console.log(err.message);
        console.log(err.stack);
     });
  });
}).catch(function (err) {
  console.log(err.message);
  console.log(err.stack);
});

The code above is identical to previous examples which used the set method. In this example when calling the set method we use an index that is out of bounds, by doing that the method understands that the item does not exist in the array; so it will be added to the end of the array. But if the index passed to the method is the index of an existing item, the method will update it instead of adding a new item.

At this point we know how to add and update items in an array, but also we can remove items. The following code is demonstrates this:

var mysqlx = require('mysqlx');
mysqlx.getSession({
  host: 'host',
  port: '33060',
  dbUser: 'root',
  dbPassword: 'my pass'
}).then(function (session) {
  var schema = session.getSchema('mySchema');
  var coll = schema.getCollection('myColl');
  var query = "$.name == 'NewField'";
  var newDoc = { name: 'NewField', description: 'a new field', 
                 extra: ['hello', 'world'] };

  coll.add(newDoc).execute().then(function (added) {
    coll.modify(query)
        .unset('$.extra[1]')
        .execute().then(function (updated) {
          console.log('Document(s) updated: '
                      + updated.getAffectedItemsCount());
          coll.find(query).execute(function (doc) {
            console.log(doc);
            session.close();
          });
    })
    .catch(function (err) {
      console.log(err.message);
      console.log(err.stack);
    });
  });
}).catch(function (err) {
  console.log(err.message);
  console.log(err.stack);
});

In this example we pass the name of the field and the index position of the item we want to remove as parameters to the unset method. If we pass an index that is out of bounds of the array, no item will be removed from the array and we will not receive an exception, so be careful with that.

See you in the next blog post.