Organizations who want to take advantage of the many benefits of NoSQL document databases, often find that they have two challenges:
- How to convert their RDBMS schemas to leverage the schema-less document model.
- Learn a new API/Query to access the data.
Some also find the confusion with the NoSQL name. The abbreviation stands for ‘Not only SQL’, but it can also be misinterpreted it as ‘No to SQL’, thus accepting that in order to use a NoSQL database, organizations will not only have to convert their relational data model into the document model, but also get training on whichever APIs for the NoSQL database that they will select.
In reality, the NoSQL database industry has never abandoned the most popular data access for databases. Many NoSQL vendors are still using a variation of SQL. Cosmos DB, Cassandra CQL, Elasticsearch SQL, Cockroach Labs. Even with Mongodb query language, you will find that it is based on the select-join-project construct, which is the foundation of relational algebra that is used in SQL.
One database company in this NoSQL space, that has addressed this issue full on, is Couchbase with its N1QL query language. While Couchbase stores the data in the native JSON format, the data model that it supports can either be relational or hierarchical structure, which is often used in the document based model for its schema flexibility and extensibility. This is possible because Couchbase provides a SQL like query language – N1QL, which extends the SQL language to allow users to manipulate the hierarchical nature of the document model. All this is built on top of Couchbase high-performance data service with its key-value APIs.
But today’s NoSQL database market, the options are numerous, and as the results can pose many challenges for organizations who are looking to ensure that their database technology investments can take advantage of all the many benefits that are currently available with NoSQL technology.
- The support of structured and unstructured data
- Horizontal scalability
- Easy to manage schema evolution
- Perhaps the most important of all, is the option for vendor choice, over and above the current RDBMS vendors who have dominated the database market for the last three decades.
To help customers with the decision, Altoros – a company that focuses on helping companies to shift from their legacy IT system to the future, have published a report to compare the query language in today’s most popular databases. It has selected to focus on MySQL/SQL, Couchbase N1QL, and MongoDB query languages. Each query language was assessed for their implementations to meet the different query scenarios using the following criteria.
- Simplicity
- Readability
- Expressiveness
- Flexibility
- Skills availability
- Line of codes
- The number of application to server trips
You can get more detail of this report from the Altoros website , and it is also available here at https://resources.couchbase.com/c/altoros-database-query-report.
All the NoSQL language examples of queries and database dumps, for which can help to deploy and run all the scenarios from this report can be found in this GitHub repository
The Altoros report methodology
The goal of the report is to compare the query languages from the perspective of the traditional RDBMS applications. For that it has selected:
An Activity Management application mode, that is often found in the many of the CRM systems that manage Sales, Services and Marketing activities. The report setup includes both the relational model and the document model that are used by Couchbase and MongoDB
It also uses a set of query scenarios that most users of these systems would recognize.
| Scenario | Description |
| 1. Customer Meeting Report | To prepare for customer meetings that I will be attending next week, I want to get a list of all the customers to attend the meetings and their contacts. |
| 2. Regional Sales Territory Report | I am a Regional Sales Manager for the C-Suite Sellers territory. I want to get all accounts assigned to this territory and the account team members. |
| 3. Top 10 Industries from Customers | Determine the top 10 industries from our customers based on the 2018 sales activities. |
| 4. Sales organizations | I want to find out how much time we spent talking to the accounts assigned to my territory for Q3FY19. |
| 5. A sales activity report | How the number of sales- related tasks have changed a month over a month during the year 2018. |
| 6. A Sales team Skill Set | An analysis on the sales team skill sets/roles in the current sales organization |
| 7. Customer interactions report | A query to review all the presentations that we have conducted with the customers in CY19Q4 with detail metrics on time spent for each customer and the effectiveness of the meeting. |
| 8. Analyze the sentiment of hotel reviews | Calling Google Natural Language API to sort all reviews based on the sentiment score |
| 9. A text search report to identify customer meetings | Identify the customer accounts and their related contacts, where a particular topic has been discussed. The search criteria may include the following information partially or in full: a meeting title, a meeting date range, customer contact details, sales team member details (participants), and a customer name. |
For each scenario, the report provides the corresponding solutions written in SQL, N1QL and MongoDB query language.
- The report provides a rating for each query language based on the following criteria.
- Simplicity
- Readability
- Expressiveness
- Flexibility
- Skills availability
- The report also tabulates two additional metrics for the number of lines of code, and the number of client-server trips required by each query language in each solutions of the nine scenarios.
The assessment criteria results
The table below is a summary of all the ratings for all of the query scenarios. Please refer to the report for the individual assessment for each of the query scenarios.
Using MySQL-SQL as the reference point, the report assesses Couchbase N1QL and MongoDB Query Language based on a number of criteria.
Notes:
- Altoros who has worked with MongoDB, Cassandra, RedisLab has found that N1QL is very similar to SQL, and has consistently given it a more favorable rating than that of the MongoDB query language.
- The sample code for scenario #3 shows that the three query languages are relatively similar for the simple query scenarios, and have similar assessment criteria ratings.
The number of lines of code
The chart shows the number of lines of code for each query. While this metric can be subjected to misrepresentation as all query languages have their own recommended formatting, it can provide a simple guide as to the complexity involved.
Notes:
- The N1QL query language has about the same number of lines of code as SQL.
- MongoDB query language consistently has more lines of code.
- For scenario #7, the Altoros team had to write 347 lines for MongoDB query language, compared to 21 lines of N1QL. This outlier reflects the MongoDB query language limitations to compute complex aggregations and common table expressions (CTE) that SQL, and now N1QL, have always been the key strengths of relational database technology in the past several decades.
The number of client server trips
The chart shows the number of trips that the application has to submit to the database server.
Notes:
- For most of the scenarios, SQL/N1QL only requires a single query submit to the server, whereas the MongoDB query requires multiple trips. This is due to the expressiveness of SQL/N1QL, where the application developers simply need to declare the desired output, and it is up to the server to process and returns the results.
- The lack of the support of complex aggregation requires MongoDB to perform its calculation in multiple phases. This is similar to the standard SQL sub-query approach. The difference here is that the sub-query result sets need to be maintained in the client applications, which are then subsequently passed on to another query.
The report key findings
English