In the previous blog post we explained how to create schemas and collections. In this one we are going to explain how to work with collections: adding, updating and deleting documents.
The following code demonstrates how to add a single document to an existing collection:
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 newDoc = { name: 'Test Name', description: 'Test Description' };
coll.add(newDoc).execute().then(function (added) {
console.log('Document(s) added: '
+ added.getAffectedItemsCount());
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 objects that represent the schema (schema) and the collection (coll) that we want to work with. An object using JSON (newDoc) is created and is passed as a parameter to the coll object’s add method. Calling the execute method the document is added to the collection. Once the execute method has finished, we receive an object (added) that contains information about the document added. To verify if the document was added, we can call the added object’s getAffectedItemsCount method, which will return how many documents were added.
But, what if you want to add multiple documents?
You can do it with almost no changes in your code. The following code adds two documents at the same time:
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 newDoc = { name: 'Test Name', description: 'Test Description' };
var newDoc2 = { name: 'Test Name 2', description: 'Test Description 2' };
coll.add(newDoc , newDoc2).execute().then(function (added) {
console.log('Document(s) added: '
+ added.getAffectedItemsCount());
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 identical to the first example. We just add an extra line to declare the new document (newDoc2), and we add the new document as a parameter in the coll object’s add method. At the end we call added object’s getAffectedItemsCount method to verify we added the two documents.
Now we know how to add multiples documents to a collection using multiple variables and passing them as parameters, but we can also do the same using an Array object. In the following code example we create an array object to use it to add new documents to a collection.
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 newDocs = [{ name: 'Test Name', description: 'Test Description' },
{ name: 'Test Name 2', description: 'Test Description 2' }];
coll.add(newDocs).execute().then(function (added) {
console.log('Document(s) added: '
+ added.getAffectedItemsCount());
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 is almost identical to the first example; the difference is that we pass an array object as a parameter instead of a JSON object. The rest of the code is the same. This could be useful if you receive an array of objects from the client or if you load the data from a JSON file, you just pass the whole array to upload it to the collection.
Updating a field in a document is also very easy to do. The following code is an example of 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 = "$._id == 'f0a743ac-a052-d615-1f8c-ef65ebc4'";
coll.modify(query)
.set('$.name', 'New Name')
.set('$.description', 'New Description')
.execute()
.then(function (updated) {
console.log('Document(s) updated: '
+ updated.getAffectedItemsCount());
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 objects that represent the schema (schema) and the collection (coll) we want to work. Then we declare the query variable which contains the where clause for our update. 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 to update and the second one the new value to be set. As we did in our previous examples we call the execute method to perform the action requested. When the execute method finishes we receive an object (updated) with information about the update. To know how many documents were updated we call the updated object’s getAffectedItemsCount method.
Now that we know how to add and update documents in a collection, we are going to explain how to remove them. The following code demonstrates 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 = "$._id == 'f0a743ac-a052-d615-1f8c-ef65ebc4'";
coll.remove(query).execute().then(function (deleted) {
console.log('Document(s) deleted: '
+ deleted.getAffectedItemsCount());
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 defines the objects that represent the schema (schema) and the collection (coll) where we want to work. Then, we define the query variable again to contain the where clause for our operation, the remove in this case. To remove a document we call the coll object’s remove method followed by the execute method. Once the execute method is completed, we receive an object (deleted) with the information about the operation that has finished. By calling the deleted object’s getAffectedItemsCount method, we know how many documents were removed from the collection.
Now we are going to see how to get documents from a collection. In the following example, we are retrieving the document that match the _id that we want:
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 = "$._id == 'f0a743ac-a052-d615-1f8c-ef65ebc4'";
coll.find(query).execute(function (doc) {
console.log(doc);
})
.catch(function (err) {
console.log(err.message);
console.log(err.stack);
});
session.close();
}).catch(function (err) {
console.log(err.message);
console.log(err.stack);
});
The previous code defines the objects that represent the schema (schema) and the collection (coll) we want to work with. Then the query variable is defined and the where clause is set to it. Then we call the coll object’s execute method to perform the query. When the execute method completes, we receive the document that match our search criteria and is send to the console to view it.
But, what if we want all the records from a collection? Well that is simple; we just need to remove the search criteria from the find method. The updated code would look like the following:
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');
coll.find().execute(function (doc) {
console.log(doc);
})
.catch(function (err) {
console.log(err.message);
console.log(err.stack);
});
session.close();
}).catch(function (err) {
console.log(err.message);
console.log(err.stack);
});
Now we know how to search a specific document and get all the documents from a collection. What if we want to get just some number of documents that match query criteria? The next example shows the code 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 like '%Test%'";
coll.find(query).limit(3).execute(function (doc) {
console.log(doc);
})
.catch(function (err) {
console.log(err.message);
console.log(err.stack);
});
session.close();
}).catch(function (err) {
console.log(err.message);
console.log(err.stack);
});
The previous code looks very similar to the example that returns one document with a specific _id, the difference here is that our query is performing a like and we are adding a call to the coll object’s limit method. Note that the query statement is case sensitive; this means that if we have documents that have ‘test’ in the field ‘name’, those documents will not be returned because we are searching for ‘Test’ names.
See you in the next blog post.