In all the previous blog posts we covered a lot of examples of how to perform actions in a MySQL server set up as a document store. In all the code examples we used the XSession to communicate to the MySQL server.

The XSession is a logical session that abstracts our connection. With it, we can be connected to one or more servers and also give us the required support to work with collections and relational tables. But there is something that the XSession does not provide, full SQL language support.

When you need to execute a query that is not supported by an XSession, use a NodeSession instead. The NodeSession object can be useful, for example, when you want to connect to a specific MySQL server and do operations specifically on this server, or when you want to execute a specific function or create a stored procedure.

The following example demonstrates how to use a NodeSession object.

var mysqlx = require('mysqlx');
mysqlx.getNodeSession({
  host: 'host',
  port: '33060',
  dbUser: 'root',
  dbPassword: 'my pass'
}).then(function (nodesession) {
  nodesession.executeSql("USE mySchema")
  .execute()
  .then(result => {
    nodesession.executeSql("SELECT * FROM myColl LIMIT 1")
    .execute(function (result){
      console.log(result[0]);
      nodesession.close();
    });
  }).catch(function (err) {
    console.log(err.message);
    console.log(err.stack);
  });
});

In the previous example, we use the method getNodeSession to get the NodeSession object (nodesession). Then we call the nodesession objects’s executeSql method to set the schema that we are going to use. To execute the query, we need to call the execute method. When the query that sets the current database completes, it returns a Result object (result) that contains information about the execution of the query. In this instance, the result object does not contain any useful information other than reporting the success or failure of the operation. Then, we call the executeSql method again, setting a query that may return one record. Once the query is executed, a result object is received, but in this case, it contains a row returned from the query represented as an array object. Each item in the array represents a column of a row. We access the array object by indexes to get the information we require, in the code we get the value of the column 0 (index 0) to write it in the console.

Of course, the previous example can be accomplished without using a NodeSession object. For that reason, in the next example we are going to create a more complex query that cannot be handled by an XSession object.

Suppose that we have a collection where we store the reviews for a movie, and the review contains a rating. We want to get the 10 best rating movies based on the average of the rating that the movie has. Currently, we do not have support to calculate the average using an XSession object, and so for this case, we need a NodeSession.

The next example demonstrates how to accomplish what we want.

var mysqlx = require('mysqlx');
mysqlx.getNodeSession({
  host: 'host',
  port: '33060',
  dbUser: 'root',
  dbPassword: 'my pass'
}).then(function (nodesession) {
  var query = 'select movie_name from' +
  ' (select  JSON_EXTRACT(doc, "$.movie_name")'+
  ' as movie_name, round(avg(JSON_EXTRACT(doc,' +
  ' "$.rate")), 2) as average from mySchema.reviews' +
  ' group by movie_name) as bestrated' +
  ' order by average desc limit 10;';

  const execSql = nodesession.executeSql(query);
  execSql.execute(function (result) {
    console.log(result[0]);
  })
  .catch(function (err) {
    console.log(err.message);
    console.log(err.stack);
  });
  nodesession.close();
}).catch(function (err) {
console.log(err.message);
console.log(err.stack);
});

In the previous example we get the NodeSession object (nodesession). Then we call the nodesession object’s executeSql method to define the query we want to execute. As you can see in this example, we don’t set the schema we want to use, but specify it in the query (mySchema.reviews). Once the query has executed, we log the results returned to the console and at the end the node session is closed.

In the query you can notice that we are using a function called “JSON_EXTRACT”, which is one of the many functions introduced in MySQL Server 5.7 that enable you to create and query JSON documents. For more information about these functions, see the MySQL Reference Guide articles JSON Functions and Manipulation JSON Data.

See you in the next blog post.